VBA Save to SkyDrive

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

Find Excel Solutions


Search Forums:
Recent Activity:
Importing Data From Pdf Files
Need Help With Formulas And Fixing This Spreadsheet
Wanting To Calculate All Hours Worked On A Project With Timesheets
Simple Formulae For Creating Complex Interactive Dashboards In Excel
What Is Microsoft Project Gantt Chart Used For?
Check Pdf Files For Text From Excel File.




Follow Business Spreadsheets On:
 
Forum TopicReply Login

Vba Save To Skydrive

Rate this:
(3/5 from 1 vote)
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
 
There are currently no replies to the "VBA Save to SkyDrive" topic of the Excel Help Forum for Presenting and Reporting Data.

Reply

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 |