"Dir" command problem

[crowdx42]

So I have been using the dir command to open all the files in a folder and move them into an import workbook. My problem is that the command tends to default to the excel default save folder whenever I make changes to the macros in the original excel workbook, i.e. the first workbook with the macros stored in it opens the second workbook which the files from the dir command are moved to.
Is there a way to avoid this? My current workaround is to 'save as" before I run the macro and this normally fixes it, the other solution is that if I go to the folder where the original file is saved and double click on it I do not have to use the "save as" workaround.
Is there any vba code which can fix this without the above workarounds?
TIA
Patrick

[Bryan Hessey]

So I have been using the dir command to open all the files in a folder and move them into an import workbook. My problem is that the command tends to default to the excel default save folder whenever I make changes to the macros in the original excel workbook, i.e. the first workbook with the macros stored in it opens the second workbook which the files from the dir command are moved to.
Is there a way to avoid this? My current workaround is to 'save as" before I run the macro and this normally fixes it, the other solution is that if I go to the folder where the original file is saved and double click on it I do not have to use the "save as" workaround.
Is there any vba code which can fix this without the above workarounds?
TIA
Patrick

Hi,

does http://www.cpearson.com/excel/BrowseFolder.htm answer your question?

---

[crowdx42]

Is there no simple way? Using shell commands may not work on machines with restricted access.
I was hoping more for some other way to define the excel defaul folder in a macro?
TIA

[mudraker]

Have you looked at the
ChDrive
ChDir
commands

[crowdx42]

What I am going to try is the code below. Hopefully it will work.
Patrick

Sub set_default_directory()
Application.DefaultFilePath = "F:\Daily Report"
End Sub

[crowdx42]

Nope the above code did not work
Any ideas??

[Bryan Hessey]

What I am going to try is the code below. Hopefully it will work.
Patrick

Sub set_default_directory()
Application.DefaultFilePath = "F:\Daily Report"
End Sub

Hi,

reading other posts, the line (quote) "DefaultFilePath only changes the folder which Excel will use the first time it is opened for a session. To change the current path for the next file open, you need to use ChDrive and ChDir" would suggest it wouldn't work (as you discovered).

from my own code,

Please Login or Register  to view this content.

presents the user with a default folder, and allows change, however you are after a way to automate that back to the correct (of your predetermined) choice folder?

If .LookIn were set to the FilePath of a known folder/file this would avoid you using the (current) default folder.

Question, do you know (within the code) the folder you want, or can you search for it?

Is this heading in the direction to solve your problem?

---