How to save a record in access to a new row in Excel

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

Guest

This code works, however, after the Then (after the Excel sheet has been
created) at the SaveAs line it writes over the first Excel sheet created in
the beginning if clause. What I want it to do is add the next record to the
next row in the same sheet not write a new sheet for every record.
Please help. Thanks,
janis


Private Sub Form_AfterUpdate()


Const SFOL = "C:\Test"
Const SFIL = "Employees.xls"

Dim fso As Object
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



If Dir(SFOL, vbDirectory) = "" Then
MkDir SFOL
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
Set wbk = appExcel.Workbooks.Add
Set wks = appExcel.Worksheets(1)
wks.Name = "Emp"
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.SaveAs ("c:\Test\Employees")

Set dbs = Nothing
Set fso = Nothing

Else

Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("c:\Test\Employees.xls")

Set wks = appExcel.Worksheets("Emp")

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.SaveAs ("c:\Test\Employees.xls")
Set dbs = Nothing
End If
End Sub
 
Back
Top