Export Data to Access

  • Thread starter Thread starter Geoff Murley
  • Start date Start date
G

Geoff Murley

I have an Access table containing the following fields:
Username, StartTime, EndTime. At the end of every working
Day I want to export this data to an existing Excel
Workbook. The Workbook has named sheets corresponding to
each Username. (Database called Times.mdb, Spreadsheet
called UserTimes.xls)

Everytime I perform an export I want to create a new row
on the matching sheet for each User as follows:

Column A: CurrentDate
Column B: StartTime
Column C: EndTime, obviously matching the Username in the
Table to the Name on the Sheet tab.

So as the days go on I am building up rows of start and
end times for each User. After the export, if it is
successful, the Database Table is cleared ready for the
following day's Data Entry to the Database via a Web Front
End.

Could anyone help me with the VBA Code to perform this
export?

Thanks
 
"Geoff Murley" said:
I have an Access table containing the following fields:
Username, StartTime, EndTime. At the end of every working
Day I want to export this data to an existing Excel
Workbook. The Workbook has named sheets corresponding to
each Username. (Database called Times.mdb, Spreadsheet
called UserTimes.xls)

Everytime I perform an export I want to create a new row
on the matching sheet for each User as follows:

Column A: CurrentDate
Column B: StartTime
Column C: EndTime, obviously matching the Username in the
Table to the Name on the Sheet tab.

So as the days go on I am building up rows of start and
end times for each User. After the export, if it is
successful, the Database Table is cleared ready for the
following day's Data Entry to the Database via a Web Front
End.

Could anyone help me with the VBA Code to perform this
export?

Thanks
Click to expand...

Geoff

Here is some VBA code to do what you wanted. Note that you will need to add
some error handling etc to cope with new names. The code requires a reference
to the relevant Excel object library to be set


Public Sub sAddToExcel()
On Error GoTo E_Handle
Dim objXL As New Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim strExcelFile As String
Dim db As DAO.Database
Dim rsUser As DAO.Recordset
Dim intRow As Integer
strExcelFile = "D:\Databases\UserTimes.xls"
Set objXLBook = objXL.Workbooks.Open(strExcelFile)
Set db = DBEngine(0)(0)
Set rsUser = db.OpenRecordset("tblUser")
If Not (rsUser.BOF And rsUser.EOF) Then
Do
Set objXLSheet = objXLBook.Worksheets(rsUser!UserName)
With objXLSheet
objXL.ActiveSheet.UsedRange ' Reset the last cell
intRow = .cells.SpecialCells(xlCellTypeLastCell).Row + 1
.cells(intRow, 1) = Date
.cells(intRow, 2) = rsUser!StartTime
.cells(intRow, 3) = rsUser!EndTime
End With
rsUser.MoveNext
Loop Until rsUser.EOF
End If
objXLBook.Save
sExit:
On Error Resume Next
Set objXLSheet = Nothing
objXLBook.Close
Set objXLBook = Nothing
objXL.Quit
Set objXL = Nothing
rsUser.Close
Set rsUser = Nothing
Set db = Nothing
Exit Sub
E_Handle:
MsgBox Err.Description & vbCrLf & "sAddToExcel", vbOKOnly + vbCritical,
"Error: " & Err.Number
Resume sExit
End Sub
 
Back
Top