Automating Excel report Using Data from MS Access

  • Thread starter Thread starter Lotus
  • Start date Start date
L

Lotus

I have data in a query that I have exporting using Transfer text to a csv file.
I set up an excel template for the report and recorded a macro in excel that
imports the data from the csv file and links the data to the report.

The problem is running the Excel macro as part of the code for the button
click in my Acces form. I am getting a runtime error, saying that the macro
cannot be found. Everything is spelled correctly.

Can anyone provide any insight?

Thanks

Here is the code:


Dim xlsApp As Excel.Application
Dim xlswkb As Excel.Workbook
Set xlsApp = CreateObject("Excel.Application")

xlsApp.Application.Visible = True

Set xlswkb = GetObject("W:\Weekly Reports\Report Templates\Report_Macros.xls")

With xlsApp.Application
.Workbooks.Open "W:\Weekly Reports\Report Templates\Report_Macros.xls"
'this is the point were the code gives the run time error, stating that the
file cannot be found
.Run "W:\Weekly Reports\Report
Templates\Report_Macros.xls!UpdateFieldSalary_Data"
.ActiveWorkbook.Sheets("template").Range("B5").Value = ReportTitle
.ActiveWorkbook.SaveAs Filename:= _
"W:\Weekly Reports\Final Reports\" & Format(Me.txtWeekEnding,
"yyyymmdd") & "FieldSal.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End With
xlsApp.Application.ActiveWorkbook.Close

xlsApp.Application.Run "Macros Worksheet.xls!UpdateMaintOT_Data"

With xlsApp.Application
.ActiveWorkbook.Sheets("Report").Range("A2").Value = ReportTitle
.ActiveWorkbook.SaveAs Filename:= _
"W:\Weekly Reports\Final Reports\" & Format(Me.txtWeekEnding,
"yyyymmdd") & "MaintOverPer.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End With

xlsApp.Application.ActiveWorkbook.Close
xlsApp.Application.Workbooks.Close
xlswkb.Close
xlsApp.Application.Quit

Set xlsApp = Nothing
Set xlswkb = Nothing

DoCmd.SetWarnings True

End Sub
 
This is actually an Excel question and should be posted in an Excel
newsgroup. Although you are working within Access, its still Excel that's
doing the work.
 
The reason I posted it here because the error is happening in the Access
programming. The macro works in Excel, but when I try to have it run from
Access code it stops cold. Or am I missing something?
 
Back
Top