Default File Location (VBA/XL97+)

Topic

New Reply

Help!
Users run a small macro that requires they specifiy some data to look for in a remote file. The program will give them the File | Open dialog, they choose a file from the list and then several forms ask for the type of data they need to filter for in the original file.

This filtered data is copied and placed into a template-generated workbook where they can further filter if they need to. They then can save the new file in a desired location. Because of the file’s content, the file will normally be saved into a location that is different to their individual default file locations.

I have declared a global string variable for ‘DefFileLoc’ and used the following to determine their default location (as per the Tools | Options | General | Default file location: command)

Global DefFileLoc As String ‘ in Declarations section

DefFileLoc = Application.DefaultFilePath ‘ to get the current default file location

This is OK when I have stepped through the code and noted that the path is indeed e.g ‘C:My Documents’

I did write the following code to change the default back, but it has no effect as it only remembers the last location the user used.

‘ Change back to the users original DefaultFilePath
With Application
.DefaultFilePath = DefFileLoc
End With

When the user elects to put their files away in ‘H:Quality Data’ for example, the default file location is changed to the new location.

I am sure that it can be done. but I seem to have not found the answer. Any suggestions on how I should be using the object variable to remember the default file location so that it can be re-used when the user has closed the files that go to the alternate location and return the default file location back to how it was when the user started MS Excel would be appreciated.

Leigh

Reply | Quote

Replies

The deafault file path is not really relevant, because it does not always have to be the active filepath. Instead of changing the default filepathy, I think yyou just need to use ChDir to set the current directory. If you want to restore the Default Filepath as the current directory, just use :

ChDir Application.DefaultFilePath

Andrew

Reply | Quote

Thanks Andrew.

I am not sure how to use this so that the initial default file location is ‘captured’ , the user plays to their heart’s content saving files in any other location and then the macro is to return the default file location to how it was before the macro ran.

Would you expand on your advice a little please?

TIA, Leigh

Reply | Quote

Leigh,

CurDir returns the last directory a file was saved to, so you could do something like this :

strCurrentDir = CurDir

do whatever file saving you have to do and then

ChDir strCurrentDir.

Would that help ?

Andrew C

Reply | Quote

Thanks, Andrew

I already had used your suggestion to define the path to their location of the server-based data files. (It uses their workstation name to derive a known location for where they can ‘see’ their data files). See the public function code and the snippet from the main macro below for that.

Public Function GetComputerNames() As String
Dim lpBuffer As String * 20
Dim Length As Long ‘MID(text,start_num,num_chars)
Length = GetComputerName(lpBuffer, Len(lpBuffer))

‘Save Location & Department names for later use
LocationName = Mid(lpBuffer, 2, 3)
DepartmentName = Mid(lpBuffer, 5, 2)
End Function

GetComputerNames
‘Open relevant CSV file
Dim FilePath As String
FilePath = LocationName & DepartmentName & “QUA”
‘Specify the correct file server name before running macro
ChDir “ServerName” & FilePath
Application.DefaultFilePath = “ServerName” & FilePath
Application.Dialogs(xlDialogOpen).Show “ServerName” & FilePath & “*.csv”
fNamePath = Application.DefaultFilePath & “”

I wanted to persist with ‘Application.DefaultFilePath’ in some form so that I did not have to worry about what ‘ChDir’ and ‘CurDir’ were doing, except where I could use them to my satisfaction.

I am no closer to resolving this, any further suggestions?

Reply | Quote

I have solved the problem…

I had been referencing a new default path in the code that I posted to the forum. It wasn’t until I saw it there that I realised that the path was being set.

‘Application.DefaultFilePath = “ServerName” & FilePath’

Thanks for your assistance.

Reply | Quote

If you want to change the path temporarily, try this:

ChangeFileOpenDirectory “Servernameshare”
Application.Dialogs(xlDialogOpen).Show

Reply | Quote