5

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?

whitebloodcell
  • 308
  • 1
  • 4
  • 10
  • 1
    Tried [CurDir](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/curdir-function)? – LS_ᴅᴇᴠ Oct 17 '17 at 08:28
  • CurDir might be changed while file is open and before code runs? Assuming the File is opened from a local drive use the workbook.open event to write the curdir to a global variable – Harassed Dad Oct 17 '17 at 11:58
  • Sorry for the delay in getting back to this - have been away. Tried CurDir and that works well for my usage, as I set the output filepath immediately upon execution. If you post it as an answer I will mark it as correct. – whitebloodcell Oct 27 '17 at 07:22
  • I spoke too soon, this does change depending on which computer it is run. – whitebloodcell Oct 31 '17 at 11:00
  • This problem has been solved [here](https://stackoverflow.com/a/73577057/12287457). – GWD Sep 05 '22 at 23:18

2 Answers2

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!

hod
  • 750
  • 7
  • 21
  • 1
    Is there a way to get the directory the workbook is saved in from this though? The results ideally need to be saved in OneDrive so that they sync back to my development computer. – whitebloodcell Oct 31 '17 at 11:01
  • @whitebloodcell I think I'm missing something. In your original question you're saying that the `Application.ActiveWorkbook.Path` returns path to OneDrive, and in your follow up question you're saying that "results ideally need to be saved in OneDrive"... – hod Oct 31 '17 at 12:12
  • 1
    Sorry, by "returns path to OneDrive" I mean it returns the remote OneDrive address, rather than the local folder it is saved on my computer. When I refer to OneDrive in "results ideally need to be saved in OneDrive", I mean the local OneDrive folder on my computer (C:\Users\tomcl\OneDrive\Documents\Trading). – whitebloodcell Nov 01 '17 at 14:14
  • 1
    I would try this: `OnePath = Environ("UserProfile") & "\OneDrive\Documents\Trading"`. This Environ function will return the local user name. So in your case if you will `Debug.Print OnePath` your result will be `C:\Users\tomcl\OneDrive\Documents\Trading` but on other machine it's going to be `C:\Users\OtherUserName\OneDrive\Documents\Trading` – hod Nov 01 '17 at 16:00
-1

I solved this using FileSystemObject.

Dim fso as FileSystemObject, localPath as String
localPath = fso.GetParentFolderName(fso.GetAbsolutePathName(Application.ActiveWorkbook.Name))
learner4life
  • 1
  • 2