Automation of Excel Report using Access Data

[Lotus]

I have data in a query that I have exported from Access using Transfer text
to a csv file. I set up an excel spreadsheet 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 for the button click in Access:


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

 

[ryguy7272]

Why do you go from Access to csv to Excel? Why not Access straight to Excel?
You can control Excel from Access quite easily. Look at this code on this
link to my site:
http://www.consulting-group360.com/Code.aspx

Post back with specific questions.
Ryan---

 

[Lotus]

Thanks for the link (as I have a lot that I want to learn)!

The reason for the csv is because i have been asked to archive a flat file
of the data being used.

The specific issue is the runtime 1004 error that keeps Access from running
the macro in Excel. connecting to excel works, because the instance of excel
is visible, but the macro that I have in excel does not run and the effor
message states that "W:\Weekly Reports\Report Templates\Report_Macros.xls"
cannot be found.

I do not know what to do get the macro to run. Is my syntax wrong? The
Excel macro works with no problems when run from Excel.

 

[Lotus]

Thanks for the link (as I have a lot that I want to learn)!

The reason for the csv is because i have been asked to archive a flat file
of the data being used.

The specific issue is the runtime 1004 error that keeps Access from running
the macro in Excel. connecting to excel works, because the instance of excel
is visible, but the macro that I have in excel does not run and the error
message states that the macro "W:\Weekly Reports\Report
Templates\Report_Macros.xls!UpdateFieldSalary_Data"
cannot be found.

I do not know what to do get the macro to run. Is my syntax wrong? The
Excel macro works with no problems when run from Excel.

 

[ryguy7272]

I would say, paste all your Excel VBA into that Access VBA that I just gave
you, and put it right here:
' Your Excel code begins here…

Make sure you set a reference to Excel:
Tools > References > check off Microsoft Excel xx.x Object Library

 

[Lotus]

It works beautifully!!! Thank you!

I would say, paste all your Excel VBA into that Access VBA that I just gave
you, and put it right here:
' Your Excel code begins here…

Make sure you set a reference to Excel:
Tools > References > check off Microsoft Excel xx.x Object Library