VBA Save to SkyDrive

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

Find Excel Solutions


Search Forums:
Recent Activity:
Web Query
Vba Save To Skydrive
Auto-timing Not Working Properly
"runtime Error 13" And "runtime Error -2147483640" Excel 2011 Mac
Portfolio Performance Monitoring And Valuation
Importing Data From Pdf Files




Follow Business Spreadsheets On:
 
Forum TopicReply 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

 

Find Excel templates
and add-ins in the
Excel Business Solutions Directory
   
  © 2014 Business Spreadsheets. All Rights Reserved. Legal |