L
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
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