Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Changing Default file locations (Excel 2k)

    Hi,

    Is there any way that when you start Excel by clicking on a file in Explorer, it might be possible for the default directory in Excel to be changed to that of the file which has initiated Excel? Currently this would only be changed by saving the said file.

    Any thoughts from Legare or any of our other gurus?

    Thanks in Advance

    Peter Moran
    Two heads are always better than one!

  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: Changing Default file locations (Excel 2k)

    Try setting the default path to a blank, and that should work.

    Tools, Options, General and remove any entry for Default File location.

    I'm not Legare (or any other guru) but hope that will work for you <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Changing Default file locations (Excel 2k)

    Hi Andrew,

    Thanks for your thoughts.

    This was my thinking - sometimes I don't have Excel running, and I may do some hunting for a spreadsheet via Explorer and then load it from there. When I do that the directory the file is not the current directory in Excel, as it is whenever I load a file from any directory via File Open in Excel.

    However I have found subsequently that if I do a File Save As, Excel finds the directory and makes it the current directory, even though I don't actually save the file.

    My thoughts were along the lines of having a simple macro which would identify that the current directory is aligned to the file however it was opened.

    Regards,

    Peter Moran

  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: Changing Default file locations (Excel 2k)

    Peter,

    If you place the following code in the Thisworkbook object of a workbook, it will do as you want for that workbook.

    Private Sub WorkbookOpen()
    ChDrive Left(Me.path, 1)
    ChDir Me.path & ""
    End Sub


    However that may not be much use as it means you have to incorporate the code into all existing workbooks.

    If you add a simple class module with similar code to your Personal.xls it should work in most cases.

    Create a Class Module in Personal.xls and insert the following procedures :

    Option Explicit

    Public WithEvents xlApp As Application

    Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
    ChDrive Left(Wb.path, 1)
    ChDir Wb.Path & ""
    End Sub

    Private Sub xlApp_WorkbookActivate(ByVal Wb As Workbook)
    ChDrive Left(Wb.path, 1)
    ChDir Wb.Path & ""
    End Sub

    You can omit xlApp_WorkbookActivate if you do not want Excel to change the current path each time you activate a workbook.

    Then add the following to the Thisworkbook object of Personal.xls :

    Dim AppCls As New xlAppClass

    Private Sub Workbook_Open()
    Set AppCls.xlApp = Application
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set AppCls.xlApp = Nothing
    End Sub

    Hope that helps.

    Andrew C

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Changing Default file locations (Excel 2k)

    Andrew,

    Thanks for your further input.

    Looks very interesting - I will try them and see which appears most appropriate.

    As you surmised I don't think the first option in each workbook is the most satisfactory. I'm not sure whether I want to change the current drive every time a workbook is activated, but will see how it works out.

    Thanks again for your input.

    Regards,

    Peter Moran

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Default file locations (Excel 2k)

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> You don

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

    Re: Changing Default file locations (Excel 2k)

    Peter,

    Some modifications would be needed to cater for launching a file from an UNC path (Network) or indeed when excel starts with a blank work book.

    the following should leave the deafault file location intact in those circumstances. VBA is not very good at dealing with UNC path but should be ok with network paths that are mapped to a drive letter.

    Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
    If Left(Wb.path, 1) = "" Or Wb.path = "" Then
    ChDrive Left(Application.DefaultFilePath, 1)
    ChDir Application.DefaultFilePath
    Else
    ChDrive Left(Wb.path, 1)
    ChDir Wb.path
    End If
    End Sub

    Andrew C

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Default file locations (Excel 2k)

    Why not use Auto_Open in Personal.xls so the code need not be put in every workbook?

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

    Re: Changing Default file locations (Excel 2k)

    The code in Personal.xls.Auto_Open runs once when Personal.xls opens, which would be before the the target workbook is opened. However as I suggested in <post#=241810>post 241810</post#> , this code should go in a class module in Personal.xls, with the initialization being handled by Personal.xls WorkBook_Open event. The code should then be fired any time any workbook is opened.

    Andrew C

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Default file locations (Excel 2k)

    Kerect.

    I should not attempt to post anything while in the midst of doing taxes.

Posting Permissions

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