How to change directory or path when saving Excel from Access 2003

[Dom606]

Hi guys,
I have code that exports data from Access to Excell and it works fine untile I try to save the resulting open spread sheet to a specific directory. No matter what I try it save to My Documents. I even tried the ChDir command and that did not work either. Can anyone suggest the correct way to change the path so that I can save the workbook? See the red line of code below. This is where I am getting hosed.

Private Sub subTransferDataToExcel()
    On Error Resume Next
    Dim sCriteria As String
    Dim db As Database
    Dim rst As Recordset
    Dim objApp As Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    Dim strTemplatePath As String
    Dim newFile As String
    Dim fDt As String
    Dim strSavePath As String  
.
.
.
.
    With objSheet
        .Select
        'Clears the current contents in the workbook range
        .Range("C16:D22").ClearContents
        'rst Copies the recordset into the worksheet
        .Range("C16").CopyFromRecordset rst
    End With
    rst.Close
    objApp.Visible = True
  
   'Saves filename with the date value in C22
    fDt = Range("C22").Value
   'Change the date format
    newFile = "time sheet" & Format$(fDt, "mmddyyyy")
   ' Where to save the new workbook
    [COLOR=red]strSavePath = "\\bigguy\users\Dom_F\Excel\TimeSheets"[/color]
   ' ChDir "\\bigguy\users\Dom_F\Excel\TimeSheets"
    ActiveWorkbook.SaveAs FileName:=newFile
    
    Set rst = Nothing
    Set db = Nothing
    Set objSheet = Nothing
    Set objBook = Nothing
    Set objApp = Nothing

End Sub

 

[PHV]

Your excel objects are never created ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dom606]

Hi PH,
I am not sure what you mean. The spreadsheet is saved in My Documents with the correct name and date. All I am doing is changing where it is saved.

 

[vbajock]

'Saves filename with the date value in C22
fDt = Range("C22").Value
'Change the date format
newFile = "time sheet" & Format$(fDt, "mmddyyyy")

' Where to save the new workbook
strSavePath = "\\bigguy\users\Dom_F\Excel\TimeSheets\"
' ChDir "\\bigguy\users\Dom_F\Excel\TimeSheets"

-> ActiveWorkbook.SaveAs FileName:=strSavePath+newFile

 

[Dom606]

Thank you VBAJock,
I had to make two minor changes but it works. I had to use & instead of + and I needed to add a \ at the end of strSavePath. Hey, but it works great.
Dom

'Saves filename with the date value in C22
    fDt = Range("C22").Value
   'Change the date format
    newFile = "time sheet" & Format$(fDt, "mmddyyyy")
   ' Where to save the new workbook
    strSavePath = "\\bigguy\users\Dom_F\Excel\TimeSheets[COLOR=red]\[/color]"
    ActiveWorkbook.SaveAs FileName:=strSavePath [COLOR=red]&[/color] newFile