473,229 Members | 1,767 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,229 software developers and data experts.

Running Excel macro from access - code error

Hi,

I am trying to run an Excel macro from an Access module, however when I
run the code the macro runs but then I get an error in Access. The
error is: Run-time error "440", Automation error.

My code is:
Sub Run_Excel_Macro()

Dim xls, xlWB As Object
Dim strFile, strMacro As String
strFile = "DIP_CTR.xls"
strMacro = "DIP_CTR"
Set xls = CreateObject("Excel.Application")
Set xlWB = xls.workbooks.Open("G:\Hris\Rachel\DIP STUFF\" &
strFile)
xls.Visible = True

xls.Run strMacro ****this is were the code errors******
Set xls = Nothing
Set xlWB = Nothing

End Sub

The macro runs perfect but my code errors at "xls.run.strmacro even tho
the excel macro has run.

Any ideas would be greatly appreciated!

Flick. x

Nov 13 '05 #1
6 9805
<ge*********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi,

I am trying to run an Excel macro from an Access module, however when I
run the code the macro runs but then I get an error in Access. The
error is: Run-time error "440", Automation error.

My code is:
Sub Run_Excel_Macro()

Dim xls, xlWB As Object
Dim strFile, strMacro As String
I suppose you are not aware that writing
Dim strFile, strMacro As String
is the same as writing
Dim strFile as Variant, strMacro as String

If you want both vars be defined as string you must write
Dim strFile As String, strMacro As String

Bruno

strFile = "DIP_CTR.xls"
strMacro = "DIP_CTR"
Set xls = CreateObject("Excel.Application")
Set xlWB = xls.workbooks.Open("G:\Hris\Rachel\DIP STUFF\" &
strFile)
xls.Visible = True

xls.Run strMacro ****this is were the code errors******
Set xls = Nothing
Set xlWB = Nothing

End Sub

The macro runs perfect but my code errors at "xls.run.strmacro even tho
the excel macro has run.

Any ideas would be greatly appreciated!

Flick. x

Nov 13 '05 #2
I have run your code in Access changing only the strFile and strMacro and it
runs without error. This is with Office 2003. You may want to supply more
information (Access and Excel version, etc.).

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<ge*********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi,

I am trying to run an Excel macro from an Access module, however when I
run the code the macro runs but then I get an error in Access. The
error is: Run-time error "440", Automation error.

My code is:
Sub Run_Excel_Macro()

Dim xls, xlWB As Object
Dim strFile, strMacro As String
strFile = "DIP_CTR.xls"
strMacro = "DIP_CTR"
Set xls = CreateObject("Excel.Application")
Set xlWB = xls.workbooks.Open("G:\Hris\Rachel\DIP STUFF\" &
strFile)
xls.Visible = True

xls.Run strMacro ****this is were the code errors******
Set xls = Nothing
Set xlWB = Nothing

End Sub

The macro runs perfect but my code errors at "xls.run.strmacro even tho
the excel macro has run.

Any ideas would be greatly appreciated!

Flick. x
Nov 13 '05 #3
Excel 2000 and Access 2000.

Many Thanks

Nov 13 '05 #4
My macro will run in Excel (xls. macro works fine) however my code in
Access debugs with the same error msg "Run-time error "440", Automation
error." (as soon as macro has finished in xls)

Please help - I relly don't get why the macro runs fine from Access but
then errors in Access

Flick. x

Nov 13 '05 #5
Hi!

In addition to the string declarations, mentioned
by Bruno, the same is true for the object
declarations.

Dim xls, xlWB As Object

Declares xls as variant, and xlwb as object, try:

Dim xls as object, xlWB As Object

I can't find any direct errors, but perhaps the
Excel macro isn't finished at the time it hits
the lines releasing the object variables?
Try using a DoEvents:

xls.Run strMacro
DoEvents
Set xlWB = Nothing
Set xls = Nothing

And try reversing the order of releasing
(workbook first).

I don't know, but perhaps also experiment a little
with were you place the xls.visible line?

If neither of these works, I think I'd consider
doing the whole operation from Access. I e copy all
the code from the macro into Access, prefix all
objects, properties and methods with the appropriate
object variables, and see if that works better.

Roy-Vidar

geronimo...@hotmail.com wrote:
My macro will run in Excel (xls. macro works fine) however my code in
Access debugs with the same error msg "Run-time error "440", Automation error." (as soon as macro has finished in xls)

Please help - I relly don't get why the macro runs fine from Access but then errors in Access

Flick. x


Nov 13 '05 #6
Thank you.

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Carl | last post by:
Please can anyone tell me how I can create a macro to save the results of a query as an excel file? The query is called Student List and I would like to save it to "My Documents". We have had...
2
by: geronimo_me | last post by:
Hi, I have the following code in an access module: Sub Run_Excel_Macro() Dim xls, xlWB As Object Dim strFile, strMacro As String
3
by: bwhite | last post by:
I have a temp table with one row of data that I need to export into Excel. I created the export to create the xls file as follows ... Dim FileName FileName = !! DoCmd.SetWarnings False...
14
by: pmud | last post by:
Hi, I need to use an Excel Sheet in ASP.NET application so that the users can enter (copy, paste ) large number of rows in this Excel Sheet. Also, Whatever the USER ENETRS needs to go to the...
1
by: Dennis Benjamin | last post by:
Hi All Hope this isn't a FAQ - I've looked for several hours and can't find an answer. I've written my first VB.NET app, a ~20 line console application that opens up an Excel document, compares...
10
by: Steve | last post by:
I am trying to create a DLL in Visual Studio 2005-Visual Basic that contains custom functions. I believe I need to use COM interop to allow VBA code in Excel 2002 to access it. I've studied...
7
by: farhaaad | last post by:
Hi everybody, I just wanted to know if i can make a form in excel (the same as access forms), so when i enter data in excel form it goes to a table in access ? I mean when i enter a value in a...
16
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub...
2
by: Cele Balser | last post by:
Hello, This can't be that hard! Could someone give me the code to run a macro in my Personal.xls from my Access module? I have created a query in Access, exported it to a network drive, I can open...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.