runtime error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How it works is, the user updates only one record in the access db at a
time. I want that one record to be added to the Excel sheet and then saved.

There is a runtime error and it stops in the Else clause on the line:
Set wbk = appExcel.Workbooks.Open("Employees.xls")
It can't find the newly created Employees sheet. I think because the
default save in Access wants it to save it to MyDocuments. I took out the
default save location but it doesn't seem to know where to save it to. That
is why I hard coded it as SaveAs. I've tried it as only a Save and then it
definitely saves it to the wrong location. I have to save it to the test
file.
many thanks,

Private Sub Form_AfterUpdate()





Dim fso As Object
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim EndRow As Long

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

'Check if directory exists if not create it
If Dir("c:\Test", vbDirectory) = "" Then
MkDir "c:\Test"
End If

'Check if Excel object is created if not create it
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists("c:\Test\Employees.xls") Then
Set appExcel = New Excel.Application
appExcel.Application.Visible = True
appExcel.DisplayAlerts = False
Set wbk = appExcel.Workbooks.Add
Set wks = appExcel.Worksheets(1)
wks.Name = "Emp"
wks.Activate



Cells(1, 1).Value = Me.Form.ID
Cells(1, 2).Value = Me.Form.FirstName
Cells(1, 3).Value = Me.Form.Salary
wbk.SaveAs ("c:\test\Employees.xls")
wbk.Close
appExcel.Quit
Set dbs = Nothing
Set fso = Nothing

Else

Set appExcel = Excel.Application
appExcel.Visible = True
appExcel.DisplayAlerts = False
Set wbk = appExcel.Workbooks.Open("Employees.xls")

Set wks = appExcel.Worksheets("Emp")

wks.Activate

EndRow = Cells(Rows.Count, 1).End(xlUp)
Debug.Print EndRow
Cells(EndRow + 1, 1).Value = Me.Form.ID
Cells(EndRow + 1, 2).Value = Me.Form.FirstName
Cells(EndRow + 1, 3).Value = Me.Form.Salary
Debug.Print EndRow
wbk.SaveAs ("c:\Test\Employees.xls")
wbk.Close
appExcel.Quit
appExcel.Quit


End If

appExcel.DisplayAlerts = True
End Sub
 
Hullo Janis,
the statement raise and error 'cause you have to specify the path of the
file before the file name. Try in this way and you'll surely get rid of the
error

Set wbk = appExcel.Workbooks.Open("c:\test\Employees.xls")

In this way the path and the file name are hardcoded but you can also pass
them as variable.

Cheers Paolo
 
Back
Top