0

I am trying to use ChDir in Excel VBA to change the default directory to the one the current workbook is located, so that I can have the macro open an open file dialog box with that same folder as the default.

I can't get it to work properly, my code below gives a runtime error saying the path not found.

Dim myPath As String
myPath = ActiveWorkbook.Path
ChDrive ""
ChDir myPath  'this line gives the error
OpenFiles = Application.GetOpenFilename(Title:="select file(s) to import", MultiSelect:=True)

I'm working in a Onedrive folder, which seems to be the problem (it works ok if I move the workbook to my desktop.) How can I get around this problem? I want to be able to use this macro regardless of where the workbook is located.

Jade
  • 1
  • What is the value of myPath in the moment of the error? Is it a right path? Chdir will give error in case it cannot build the path... – Jortx Apr 05 '22 at 13:29
  • https://social.msdn.microsoft.com/Forums/office/en-US/1331519b-1dd1-4aa0-8f4f-0453e1647f57/how-to-get-physical-path-instead-of-url-onedrive?forum=officegeneral – Storax Apr 05 '22 at 13:32
  • This has answers [here](https://stackoverflow.com/a/68963896/3221380), I link to the one that I found the most useful – Sgdva Apr 05 '22 at 13:32
  • Does this answer your question? [Excel's fullname property with OneDrive](https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive) – Sgdva Apr 05 '22 at 13:33
  • THanks Sgdva, It looks like your first link (to the local address path) should work. I just need to learn how to use a function (I'm brand new to VBA and am using these puzzles to learn it)--when I tried copying the code into my module and then calling it in my code with the ChDir it gives me an "arguement not optional" error. So I assume I'm not using a function correctly. I'll keep working on that first, and then circle back to this. – Jade Apr 09 '22 at 19:36

0 Answers0