how to save an Excel file to a directory?

[Guest]

On the following line I am trying to save the created Excel sheet to a certain
directory:
wbk.Save (STR_DIRECTORY_PATH)

I get an error on this line. I also tried chDir to see if that would work.
I created the directory but Excel wants to save it to the default. I need it
saved in this directory because I have a form I want updated to be saved in
that directory after a record is updated. I want it to check if the file is
created and open it otherwise I want it to create it and save it in that
directory.

tia,






Private Sub Form_AfterUpdate()


'*******************************************************************
'Purpose: To update an Excel spreadsheet with each subsequent record
'*******************************************************************



Const STR_DIRECTORY_PATH = "C:\Test\"
'Const STR_Filename = "emp.xls"

Dim lngLastError As Long
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim i As Integer
Dim EndRow As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset


'Check if directory exists if not create it

If Dir(STR_DIRECTORY_PATH) = "" Then
MkDir STR_DIRECTORY_PATH

Else

End If


Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("emp.xls")


Set wks = appExcel.Worksheets("Employees")
wks.Activate

EndRow = Range("A65536").End(xlUp).Select

Range("a1").Offset(0, EndRow + 1).Value = Me.Form.ID
Range("B1").Offset(0, EndRow + 1).Value = Me.Form.FirstName
Range("C1").Offset(0, EndRow + 1).Value = Me.Form.Salary

wbk.Save (STR_DIRECTORY_PATH)
Set dbs = Nothing
End Sub

 

[Norman Yuan]

Firstly, unless the worksheet is newly create and not save, you need to use
SaveAs() to save the opened worksheet to a different location:

wbk.SaveAs File_Path_Name

Secendly, the error is caused by the bracket. You do not use bracket around
the argument, unless you use Call, like this:

Call wbk.SaveAs(File_Path_Name)