Home > Forum Home > Presenting and Reporting Data > VBA Save to SkyDrive Share

VBA Save to SkyDrive

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


Forum TopicPost Reply Login

Vba Save To Skydrive

Rate this:
(4/5 from 2 votes)
HappyIn 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.
 Excel Business Forums Administrator
 Posted by on
 
Replies - Displaying 1 to 1 of 1Order Replies By: Most recent | Chronological | Highest Rated
Confused
Rate this:
(3/5 from 1 vote)
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.

Thanks 
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for VBA Save to SkyDrive in the