Automating Excel report Using Data from MS Access

Discussion in 'Microsoft Access VBA Modules' started by Lotus, May 27, 2010.

  1. Lotus

    Lotus Guest

    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
     
    Lotus, May 27, 2010
    #1
    1. Advertisements

  2. Lotus

    microsoft Guest

    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.

    "Lotus" <> wrote in message
    news:...
    >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
    >
     
    microsoft, May 28, 2010
    #2
    1. Advertisements

  3. Lotus

    Lotus Guest

    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?


    "microsoft" wrote:

    > 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.
    >
    > "Lotus" <> wrote in message
    > news:...
    > >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
    > >

    >
    >
    > .
    >
     
    Lotus, May 28, 2010
    #3
  4. Lotus

    Lotus Guest

    Moved the question to the Excel programming forum.

    "Lotus" wrote:

    > 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?
    >
    >
    > "microsoft" wrote:
    >
    > > 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.
    > >
    > > "Lotus" <> wrote in message
    > > news:...
    > > >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
    > > >

    > >
    > >
    > > .
    > >
     
    Lotus, May 28, 2010
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Repost - Automating Excel and Outlook from Access

    Guest, Nov 5, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    11
    Views:
    321
    Alex Dybenko
    Jan 11, 2005
  2. Adrian

    Automating Word and email merge using Access

    Adrian, Feb 21, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    222
    Adrian
    Feb 21, 2006
  3. Guest

    Error automating mail merge using access query

    Guest, Nov 28, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    203
    Guest
    Nov 28, 2006
  4. Guest

    Automating using Excel...Freeze panes error

    Guest, Apr 25, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    408
    Guest
    Apr 26, 2007
  5. Guest

    data type conversion when automating excel from access

    Guest, Jul 31, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    5
    Views:
    236
    Ed Adamthwaite
    Aug 1, 2007
Loading...

Share This Page