Excel Help for Vba Save To Skydrive

In Excel 2010 we can share a workbook directly as a web application on SkyDrive. This supports a wide range of functionality including standard pivot tables, charts and slicers. The published workbook does not support certain features such as links to external sources or VBA macros. Therefore to save the current workbook to SkyDrive using VBA, we can make a copy and remove the unsupported features. By specifying some SkyDrive information, we can then automate the publishing process directly from the macro enabled workbook.

An example of the information required is like:
Excel Spreadsheet:

	A	B	C
1
2	SkyDrive ID:	12346789abcefghij
3	SkyDrive Folder:	Documents
4

Where SkyDrive ID is the account number that can be found in the URL when logged in through Windows Live, and SkyDrive folder is the folder in SkyDrive to publish to.

The VBA code required with detailed comments is:

VBA Code:

Sub Publish_to_SkyDrive()
Application.ScreenUpdating = False
Dim sht As Variant
Dim fname, cname, dname, tsht As String
Dim ws As Worksheet
Dim i As Long
Dim skyid, skyfd As String
'get current workbook name
cname = ActiveWorkbook.Name
'build a published file name
fname = "Report_" & Month(Now()) & "_" & Day(Now()) & "_" & Year(Now()) & ".xlsx"
'get skyDrive information
skyid = Range("B2").Text
skyfd = Range("B3").Text
'create new workbook
Workbooks.Add
'get new workbook name
dname = ActiveWorkbook.Name
'go back to master workbook
Windows(cname).Activate
'iteration for sheet placement
i = 0
'loop worksheets to copy
'here we can limit the sheets such as
'by referencing a range with a list of
'sheet names
For Each ws In Worksheets
    i = i + 1
    'copy sheet
    Sheets(ws.Name).Copy After:=Workbooks(dname).Sheets(i)
    Windows(cname).Activate
Next
'go to new workbook
Windows(dname).Activate
'delete the default sheets
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
'break external links
Call BreakLinks
'save to SkyDrive
ActiveWorkbook.SaveAs Filename:="https://d.docs.live.net/" & skyid & "/^." & skyfd & "/" & fname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'go back to master sheet
Windows(cname).Activate
Application.ScreenUpdating = True
End Sub

Sub BreakLinks()
'sub routine to remove links to external sources
    Dim vl As Variant
    Dim il As Long
    vl = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    For il = LBound(vl) To UBound(vl)
        ActiveWorkbook.BreakLink Name:=vl(il), Type:=xlLinkTypeExcelLinks
    Next il
End Sub

Sharing and permissions can then be made directly on SkyDrive.

VBA Save to SkyDrive

Excel Help for Vba Save To Skydrive in Presenting and Reporting Data

Vba Save To Skydrive