Automation of Excel Report using Access Data
in [Programming]
|
From: Lotus on 28 May 2010 10:25 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
From: ryguy7272 on 28 May 2010 10:40 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Lotus" wrote: > 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 > >
From: Lotus on 28 May 2010 10:50 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. "ryguy7272" wrote: > 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--- > > > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''. > > > "Lotus" wrote: > > > 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 > > > >
From: Lotus on 28 May 2010 10:54 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. "Lotus" wrote: > 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. > > > "ryguy7272" wrote: > > > 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--- > > > > > > -- > > Ryan--- > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > "Lotus" wrote: > > > > > 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 > > > > > >
From: ryguy7272 on 28 May 2010 10:59 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 -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Lotus" wrote: > 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. > > > "ryguy7272" wrote: > > > 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--- > > > > > > -- > > Ryan--- > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > "Lotus" wrote: > > > > > 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 > > > > > >
|
Next
|
Last
Pages: 1 2 Prev: Error 1004 - Application or Object Defined Error Next: Worksheet_Change? |