J
Janis
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
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