PC Review


Reply
Thread Tools Rate Thread

Automating Excel report Using Data from MS Access

 
 
Lotus
Guest
Posts: n/a
 
      27th May 2010
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

 
Reply With Quote
 
 
 
 
microsoft
Guest
Posts: n/a
 
      28th May 2010
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>



 
Reply With Quote
 
 
 
 
Lotus
Guest
Posts: n/a
 
      28th May 2010
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >

>
>
> .
>

 
Reply With Quote
 
Lotus
Guest
Posts: n/a
 
      28th May 2010
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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >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
> > >

> >
> >
> > .
> >

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
data type conversion when automating excel from access =?Utf-8?B?VGFueWE=?= Microsoft Access VBA Modules 5 1st Aug 2007 11:39 PM
Automating emails from Access using Outlook 97 and Access 97 =?Utf-8?B?VGltIExvbmc=?= Microsoft Outlook VBA Programming 5 16th Jun 2005 08:40 PM
Automating the XML Data Mapping in Excel 2003 using C# =?Utf-8?B?TWFoZXNo?= Microsoft Dot NET Framework 0 14th Mar 2005 07:47 PM
Automating the XML Data Mapping in Excel 2003 using C# =?Utf-8?B?TWFoZXNo?= Microsoft Excel Programming 0 14th Mar 2005 07:43 PM
Automating exporting data from Access to Excel Freddie Microsoft Access External Data 2 2nd Feb 2004 10:34 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:06 AM.