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:
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:
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.
Hi, this looks really usefull and I'm looking at doing somthing similar. I was just wondering if you could by any chance update this method to work with OneDrive (as it is now called), I can't figure out with DOM explorer where you got the address from. If you also knew a way to open files directly from OneDrive that would be greatly appreciated.