473,326 Members | 2,126 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,326 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 9814
<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...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.