How To: Change the directory of an Excel Application:

  • Thread starter Thread starter Kevin McCartney
  • Start date Start date
K

Kevin McCartney

Hi, when I create an Excel file from Access and pump data
to it, I'd like to change the directory path so that when
a user selects to save the workbook it looks in the
specified folder. Within Excel I can use ChDir = "..." but
I need to set it from Access so something like
xlApp.VBA.ChDir = "..."

Best regards
KM
 
If you are using Office 2000 or later, one way to be able to save the
workbook to a specified folder location is to use the SaveCopyAs method on
the workbook.

Example:

xlApp.Workbooks(<WorkbookNameInStringFormat>).SaveCopyAs
<SpecifiedPathAndFileName>

xlApp.Workbooks("Book1.xls").SaveCopyAs "C:\Documents and Settings\uname\My
Documents\Book1.xls"

Note: The SaveCopyAs will not cause a message box to pop up, even if
writing over a current file, like SaveAs method will cause in Excel.
SaveCopyAs also will not cause the formulaes in any open workbook to be
changed to adjust to the file path and name the workbook was just saved to
unlike what Save and SaveAs methods would do, so SaveCopyAs is a good way to
backup files without this concern, which is how I have used this method.
This method has in the past proven to be worthwhile.

The "uname" is the user name of the user that is logged in. That particular
path is only good for Windows 2000 and later. Windows 3.11, 95, and 98 does
not have a user specific folder, and Windows NT 4.0 uses a slightly
different file path name for the user specified "My Documents" folder.
 
Back
Top