Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default File Location (VBA/XL97+)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Default File Location (VBA/XL97+)

    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 :
    <pre>ChDir Application.DefaultFilePath</pre>


    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default File Location (VBA/XL97+)

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Default File Location (VBA/XL97+)

    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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default File Location (VBA/XL97+)

    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?

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default File Location (VBA/XL97+)

    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.

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Default File Location (VBA/XL97+)

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

    ChangeFileOpenDirectory "Servernameshare"
    Application.Dialogs(xlDialogOpen).Show

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •