G
Guest
My problem is that I have a payroll application which works pretty well. I
want to expand it to simplify another task. This task is to export to the
results of a query in payroll to an excel file on my A-drive that is created
each month after the completion of payroll. I have the query written and as
a starting place I have code from an internet friend who helped me another
database (Shoe). The following is what I have :
Running the below code I get the error "Compile error user defined type not
defined" This is one of many possible problems I may get once this error is
addressed as I cut out the parts in the Shoe db that did not seem to be
needed in the Payroll db but of course I'm probably missing important
components. The code works in my Shoe db and I looked to make sure I have the
same references checked in this db.
Private Sub Command7283_Click()
On Error GoTo Err_Command7283_Click
Const cstrTemplate As String = "C:\OT\Access\RJDTemplate.xls"
Dim cstrFileDest As String
Dim db As Database
Dim rs As Recordset
Dim sSql As String
Dim X As Excel.Application
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
Dim stDocName As String
' This is a report that also prints to tell factories what inmate need to
have final reviews turned in.
stDocName = "IEP Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Hourglass True
cstrFileDest = "A:\RJD" & Format(Now(), "yyyymmdd") & ".xls"
On Error Resume Next
Kill cstrFileDest
On Error GoTo 0
FileCopy cstrTemplate, cstrFileDest
Set X = New Excel.Application
Set wb = X.Workbooks.Open(cstrFileDest)
Set ws = wb.Worksheets("Sheet1")
Set db = CurrentDb()
'This is the query that has the records I want to export to the excel file
Set rs = db.OpenRecordset("SELECT Employees.Inst, Factorys.FactoryName AS
Enterprise, ([FirstFour] & [LastTwo]) AS [CDC#], Employees.LastName,
Employees.FirstName, Employees.[Date Hired] AS [Date Assigned],
Employees.[Unassignment Date] AS [Date Unassigned], Employees.List AS Parole,
Employees.Transfer FROM Employees INNER JOIN Factorys ON Employees.[Cost
Center] = Factorys.CostCenter WHERE (((Employees.List) = Yes)) Or
(((Employees.Transfer) = Yes)) ORDER BY ([FirstFour] & [LastTwo]); ,
dbOpenSnapshot")
Do
wb.Save
X.Quit
Set X = Nothing
DoCmd.Hourglass False
MsgBox "Finished"
Exit_Command7283_Click:
Exit Sub
Err_Command7283_Click:
MsgBox Err.Description
Resume Exit_Command7283_Click
End Sub
What do you think?
want to expand it to simplify another task. This task is to export to the
results of a query in payroll to an excel file on my A-drive that is created
each month after the completion of payroll. I have the query written and as
a starting place I have code from an internet friend who helped me another
database (Shoe). The following is what I have :
Running the below code I get the error "Compile error user defined type not
defined" This is one of many possible problems I may get once this error is
addressed as I cut out the parts in the Shoe db that did not seem to be
needed in the Payroll db but of course I'm probably missing important
components. The code works in my Shoe db and I looked to make sure I have the
same references checked in this db.
Private Sub Command7283_Click()
On Error GoTo Err_Command7283_Click
Const cstrTemplate As String = "C:\OT\Access\RJDTemplate.xls"
Dim cstrFileDest As String
Dim db As Database
Dim rs As Recordset
Dim sSql As String
Dim X As Excel.Application
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
Dim stDocName As String
' This is a report that also prints to tell factories what inmate need to
have final reviews turned in.
stDocName = "IEP Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Hourglass True
cstrFileDest = "A:\RJD" & Format(Now(), "yyyymmdd") & ".xls"
On Error Resume Next
Kill cstrFileDest
On Error GoTo 0
FileCopy cstrTemplate, cstrFileDest
Set X = New Excel.Application
Set wb = X.Workbooks.Open(cstrFileDest)
Set ws = wb.Worksheets("Sheet1")
Set db = CurrentDb()
'This is the query that has the records I want to export to the excel file
Set rs = db.OpenRecordset("SELECT Employees.Inst, Factorys.FactoryName AS
Enterprise, ([FirstFour] & [LastTwo]) AS [CDC#], Employees.LastName,
Employees.FirstName, Employees.[Date Hired] AS [Date Assigned],
Employees.[Unassignment Date] AS [Date Unassigned], Employees.List AS Parole,
Employees.Transfer FROM Employees INNER JOIN Factorys ON Employees.[Cost
Center] = Factorys.CostCenter WHERE (((Employees.List) = Yes)) Or
(((Employees.Transfer) = Yes)) ORDER BY ([FirstFour] & [LastTwo]); ,
dbOpenSnapshot")
Do
wb.Save
X.Quit
Set X = Nothing
DoCmd.Hourglass False
MsgBox "Finished"
Exit_Command7283_Click:
Exit Sub
Err_Command7283_Click:
MsgBox Err.Description
Resume Exit_Command7283_Click
End Sub
What do you think?