VBA: How to get the local filepath of a workbook that uses the new autosave feature (and thus is technically opened from OneDrive)?

Question

I have a workbook that does some analysis and outputs its results to a textfile in the same directory as the workbook is saved. I need to run this code on multiple computers so the local directory changes. Previously I just used

Application.ActiveWorkbook.Path

but since the autosave feature was enabled (which is useful, so I want to continue using) this returns the remote filepath in OneDrive.

How can I get the local filepath?

Answer 1

I think that what you're looking for is an Environfunction: https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/environ-function

You could set up your "Path" like that:

MyPath = Environ("LocalAppData")

Which would return:

C:\Users\username\AppData\Local

or

MyPath = Environ("Public")

Which would return:

C:\Users\Public

There are more options to choose from (AppData, AllUsersProfile, etc...)

Hope this helps!