Excel VBA to refer to files stored on OneDrive instead of local drive?

[PatDools]

Hello,

I have the following code that was originally written when I was storing my Excel files on my local machine.  I moved all my files recently to OneDrive to take advantage of the automatic backup to my OneDrive cloud storage in case anything happened to my local hard drive.  Unfortunately, my code no longer works and I'm wondering how to refer to the OneDrive location in order for my code to execute properly.  Here is my current code:

Sub GermanDwnldIndFiles()
Dim Location As String
Dim sFolderPick As String
Dim sFolder As String
Dim sFile As String
Dim wbT As Workbook
Dim wsT As Worksheet
Dim wbS As Workbook
Dim wsS As Worksheet
Dim r As Long
With Application.FileDialog(4) ' msoFileDialogFolderPicker
If .Show Then
sFolder = .SelectedItems(1)
sFolderPick = .SelectedItems(1)
Else
Beep
Exit Sub
End If
End With
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Right(sFolder, 1) <> "\" Then
sFolder = sFolder & "\"
End If
sFile = Dir(sFolder & "*.xls*")
Set wbT = Workbooks.Open(sFolder & sFile)
sFile = Dir
Do While sFile <> ""
Set wbS = Workbooks.Open(sFolder & sFile)
For Each wsS In wbS.Worksheets
Set wsT = wbT.Worksheets(wsS.Name)
r = wsT.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wsS.UsedRange.Offset(1).Copy Destination:=wsT.Range("A" & r)
Application.CutCopyMode = False
Next wsS
wbS.Close SaveChanges:=False
sFile = Dir
Loop
Location = "C:\SaveAMillion_Materials\German_Hlthcare_Materials\INEK_Smpl_DataSets\INEK_YrlyTotals"
ActiveWorkbook.SaveAs Filename:=Location & "\" & "INEK_" & Right(sFolderPick, 4) & ".xlsx", FileFormat:=xlOpenXMLWorkbook
'wbT.SaveAs Filename:=sFolder & "New Workbook.xlsx", FileFormat:=xlOpenXMLWorkbook
' Optional: close the new workbook
wbT.Close SaveChanges:=False
ExitHandler:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

---------------

Do I need to alter the msoFileDialogFolderPicker in my code to find my chosen folder properly on my OneDrive?  Assuming that I can reference my files on my OneDrive, how do I update my 'Location' variable to write my output file to the OneDrive location:

Location = "C:\SaveAMillion_Materials\German_Hlthcare_Materials\INEK_Smpl_DataSets\INEK_YrlyTotals"

I'm hoping I can avoid having to move my files back to my local machine order for this code to continue working.

Thank you!

[#2]

@PatDools

When you move your files to OneDrive, the file paths you need to reference in your VBA code will change. Instead of using local paths like "C:...", you'll need to use the URL or path that points to the OneDrive location.

Here is how you can modify your code to work with files stored on OneDrive:

Update the Folder Picker: You do not need to modify the msoFileDialogFolderPicker part of your code because it's used to let the user select a folder interactively. It will work as long as the user selects the appropriate folder on their OneDrive.

Update the Location Variable: The Location variable should point to the location on OneDrive where you want to save your output file. To do this, you can construct a path using the OneDrive URL and the desired folder structure. For example:

vba

Location = "C:\Users\<YourUsername>\OneDrive\<FolderPath>\"

Replace <YourUsername> with your OneDrive username and <FolderPath> with the path to the folder where you want to save the output file. Note that this URL should be the local path to your OneDrive folder on your machine.

Update the SaveAs Method: Update the SaveAs method to save the file in the correct location:

vba

ActiveWorkbook.SaveAs Filename:=Location & "INEK_" & Right(sFolderPick, 4) & ".xlsx", FileFormat:=xlOpenXMLWorkbook

This will save the output file in the specified OneDrive location.

Remember that OneDrive URLs can sometimes be different depending on your setup (personal, business, etc.). You might need to adjust the path based on your specific OneDrive setup.

Additionally, make sure you have OneDrive sync enabled on your computer, so the files are available offline and can be accessed and manipulated using VBA in the same way as files on your local drive.

Keep in mind that working with cloud-based storage like OneDrive might introduce slight delays due to syncing and network operations, so be patient when working with large files.

My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.