transfering data to spreadsheets for reporting.

[Michelle]

I have pasted the following code into a module as advised earlier but I can
not seem to get the application to work properly. Obviously, I am doing
something wrong.
I need to have the times logged on and times logged off in an access table
exported to an excel spreadsheet for easier calculation and reporting but
have 2 problems.
1. cant get the logon and logoff form to dump the times entered. Code is
following - where have I gone wrong?
Private Sub cmdLogOff_Click()
Dim strSQL As String
strSQL = "Insert into tblLogTimes (TimeLogOn, TimeLogOff, EmployeeName,
Date) Values (#" & dteLogon & "#, #" & Now & "#);"
CurrentDb.Execute strSQL, dbFailOnError
cmdLogon.Visible = True
[CmdButton.Menu].SetFocus
cmdLogOff.Visible = False

End Sub

Private Sub cmdLogon_Click()
dteLogon = Now
cmdLoff.Visible = True
[CmdButton.Menu].SetFocus
cmdLogon.Visible = False


2. the code for the transfer to speadsheet in my module doesnt seem to want
to work. Code following:
Option Compare Database
Public Function TransferSpreadsheet()
Sub SendToExcel()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tblLogTimes",
"C:\Documents and Settings\Office\My Documents\Employeetimesheets.xls", True
Application.FollowHyperlink "C:\Documents and Settings\Office\My
Documents\Employeetimesheets.xls"

End Sub
End Function


This is beginning to become annoying as it is the last thing I need to do
prior to testing the database.
Any help appreciated!!

 

[Ken Snell [MVP]]

Your "syntax" for how you've written the function with a sub inside it is
wrong. That is not the way to write the code.

I would not name the function TransferSpreadsheet as that is a method of
DoCmd... I like to avoid any confusion to ACCESS.

Try this (be sure that the regular module in which this sub is located is
named something other than TransferTheSpreadsheet):

Public Sub TransferTheSpreadsheet()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tblLogTimes",
"C:\Documents and Settings\Office\My Documents\Employeetimesheets.xls", True
Application.FollowHyperlink "C:\Documents and Settings\Office\My
Documents\Employeetimesheets.xls"
End Sub

 

[Michelle]

Your "syntax" for how you've written the function with a sub inside it is
wrong. That is not the way to write the code.

I would not name the function TransferSpreadsheet as that is a method of
DoCmd... I like to avoid any confusion to ACCESS.

Try this (be sure that the regular module in which this sub is located is
named something other than TransferTheSpreadsheet):

Public Sub TransferTheSpreadsheet()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tblLogTimes",
"C:\Documents and Settings\Office\My Documents\Employeetimesheets.xls", True
Application.FollowHyperlink "C:\Documents and Settings\Office\My
Documents\Employeetimesheets.xls"
End Sub


--

Ken Snell
<MS ACCESS MVP>

I have pasted the following code into a module as advised earlier but I can
not seem to get the application to work properly. Obviously, I am doing
something wrong.
I need to have the times logged on and times logged off in an access table
exported to an excel spreadsheet for easier calculation and reporting but
have 2 problems.
1. cant get the logon and logoff form to dump the times entered. Code is
following - where have I gone wrong?
Private Sub cmdLogOff_Click()
Dim strSQL As String
strSQL = "Insert into tblLogTimes (TimeLogOn, TimeLogOff, EmployeeName,
Date) Values (#" & dteLogon & "#, #" & Now & "#);"
CurrentDb.Execute strSQL, dbFailOnError
cmdLogon.Visible = True
[CmdButton.Menu].SetFocus
cmdLogOff.Visible = False

End Sub

Private Sub cmdLogon_Click()
dteLogon = Now
cmdLoff.Visible = True
[CmdButton.Menu].SetFocus
cmdLogon.Visible = False


2. the code for the transfer to speadsheet in my module doesnt seem to
want
to work. Code following:
Option Compare Database
Public Function TransferSpreadsheet()
Sub SendToExcel()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"tblLogTimes",
"C:\Documents and Settings\Office\My Documents\Employeetimesheets.xls",
True
Application.FollowHyperlink "C:\Documents and Settings\Office\My
Documents\Employeetimesheets.xls"

End Sub
End Function


This is beginning to become annoying as it is the last thing I need to do
prior to testing the database.
Any help appreciated!!

Thankyou Ken, will try that