ChDir Question

[Guest]

In the following code snipit, I am trying to determine why the excel workbook
is not saved to the directory listed in the ChDir statement. A copy of the
excel workbook is saved in My Documents instead of the folders listed below.
The module is in Access 2003:

Select Case strLocation
Case "Austin"
ChDir ("C:\Documents and Settings\etrigo\My Documents\File
SAN Reports\Austin\Formatted Reports")
Case "SSVL"
ChDir ("C:\Documents and Settings\etrigo\My Documents\File
SAN Reports\Sunnyvale\Formatted Reports")
Case Else
ChDir ("C:\Documents and Settings\etrigo\My Documents\File
SAN Reports\Format Errors")
End Select

objExcel.ActiveWorkbook.SaveCopyAs "Formatted_" &
objExcel.ActiveWorkbook.Name
objExcel.ActiveWorkbook.Close False

Thanks for the help!

 

[Guest]

Roach,
I believe if you just use the path/filename in the save as rather than the
Change Directory it should save where you want just find. Is there any reason
you can not do that? As in:

Dim pstrPath as String

Select Case strLocation
Case "Austin"
pstrPath = "C:\Documents and Settings\etrigo\My
Documents\File
SAN Reports\Austin\Formatted Reports"
Case "SSVL"
pstrPath = "C:\Documents and Settings\etrigo\My
Documents\File
SAN Reports\Sunnyvale\Formatted Reports"
Case Else
pstrPath = "C:\Documents and Settings\etrigo\My
Documents\File
SAN Reports\Format Errors"
End Select

objExcel.ActiveWorkbook.SaveCopyAs pstrPath & "Formatted_" &
objExcel.ActiveWorkbook.Name
objExcel.ActiveWorkbook.Close False


Take Care & God Bless ~ SPARKER ~

 

[Guest]

Sparker,

Thanks, it worked like a charm.

Roach,
I believe if you just use the path/filename in the save as rather than the
Change Directory it should save where you want just find. Is there any reason
you can not do that? As in:

Dim pstrPath as String

Select Case strLocation
Case "Austin"
pstrPath = "C:\Documents and Settings\etrigo\My
Documents\File
SAN Reports\Austin\Formatted Reports"
Case "SSVL"
pstrPath = "C:\Documents and Settings\etrigo\My
Documents\File
SAN Reports\Sunnyvale\Formatted Reports"
Case Else
pstrPath = "C:\Documents and Settings\etrigo\My
Documents\File
SAN Reports\Format Errors"
End Select

objExcel.ActiveWorkbook.SaveCopyAs pstrPath & "Formatted_" &
objExcel.ActiveWorkbook.Name
objExcel.ActiveWorkbook.Close False


Take Care & God Bless ~ SPARKER ~