Prev: Error 1004 - Application or Object Defined Error
Next: Worksheet_Change?
From: Lotus on 28 May 2010 11:20

It works beautifully!!! Thank you!

"ryguy7272" wrote:

> 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
> > > >
> > > >
First  |  Prev  | 
Pages: 1 2
Prev: Error 1004 - Application or Object Defined Error
Next: Worksheet_Change?