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

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.