Update Excel Web App from Local Excel VBA

Question

I have a pretty basic knowledge base of VBA. I've been using it the past year to write programs that copy the data from excel to websites.

Right now I have a local excel sheet full of data (which is updated daily), that I am trying to have automatically copied into the Excel Web App via VBA.

I'm hoping someone can tell me how to reference the cells in the Web App from my local VBA (assuming it's possible). I can't seem to find the answer anywhere else.

Any help would be greatly appreciated, thank you!

ProfoundlyOblivious ProfoundlyOblivious · Accepted Answer · 2019-07-07T03:45:38

The Excel Web App opens files from OneDrive, therefor, saving the file in the OneDrive folder on your local machine will do what you ask.

All three of the following examples presume the purpose is to save a macro-enabled workbook as a macro-enabled workbook of the same name in the default OneDrive folder location "C:\Users\user\OneDrive"

The first two examples save a copy of the active workbook to OneDrive; in contrast, the third example moves the file without creating a copy and cannot be used if the file is open.

Example 1: save a copy of the active workbook to OneDrive. The original workbook remains the active workbook. Subsequent changes remain with the original and are not applied to the copy in OneDrive.

Sub SaveCopyToOneDrive()
    Dim destinationFolder As String
    destinationFolder = Environ("USERPROFILE") & "\OneDrive\"
    ActiveWorkbook.SaveCopyAs destinationFolder & ActiveWorkbook.Name
End Sub

Example 2: save the active workbook in OneDrive. The file in OneDrive becomes the active workbook and the original workbook is closed without saving.

Sub SaveToOneDrive()
    Dim destinationFolder As String
    destinationFolder = Environ("USERPROFILE") & "\OneDrive\"
    ActiveWorkbook.SaveAs destinationFolder & ActiveWorkbook.Name, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Example 3: move the file from an explicitly defined location to OneDrive. The target file cannot be the VBA host for this macro.

Sub MoveToOneDrive()
    Dim shortFileName As String
    Dim fullFileName As String
    Dim destinationFolder As String
    fullFileName = "C:\MyDataFiles\File.xlsm"
    Name fullFileName As destinationFolder & shortFileName
    destinationFolder = Environ("USERPROFILE") & "\OneDrive\"
    shortFileName = Mid(fullFileName, InStrRev(fullFileName, Chr(92)) + 1, Len(fullFileName))
End Sub

Update Excel Web App from Local Excel VBA

1 Answers