MS VB Run-time error '1004

Hi Folks,
One of the EE Experts originally provided this solution which has worked extremely well
 on Windows 7 64 Bit machine with MS Excel 2003 and MS Access 2003 installed and
 importing from a MS Excel 2003 File.
 No when I try to run it on Windows 7 64 Bit machine with MS Excel 2010 and
 MS Access 2003 importing a MS Excel 2003 File it fails with the error:
 Run-time error '1004 on the following line of code.
 Set MSExcelWorkBook = MSExcelApplication.Workbooks.Open(PARM_GROUP_IMPORT_PATH_FILENAME, , True) 'MS Excel File Path / Filename.

Is this caused by:
- Missing MS Access Reference?
- Need to use MS Excel 2010 File?
- Something else?

Could you please take a look at this and advise?  A copy of the code is attached.

Bob Collison
[Edited by SouthMod]
Bob CollisonSystem ArchitectAsked:
Who is Participating?
KimputerConnect With a Mentor Commented:
Both PARM_GROUP_IMPORT_PATH_FILENAMEMS, ExcelWorkBook and MSExcelApplication come out of nowhere, which is kind of strange. Are you sure there's no code somewhere else? It's not complete this way.
byundtConnect With a Mentor Commented:
In the VBA Editor, look in Tools...References menu item. Are there any items marked "Missing" at the top of the list? If so, you need to set a reference to them (make sure the item exists and the box is checked).

For example, if your code originally ran in Access 2003, you probably had a reference checked for "Microsoft Excel 11.0 Object Library". You would need to change that reference to "Microsoft Excel 14.0 Object Library" when you switch to a computer running Access 2010 & Excel 2010.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,

First I checked the References and there are no 'Missing' messages.  Also there are no references to  "Microsoft Excel 14.0 Object Library" or "Access 2010 & Excel 2010" when I scroll through the whole list.  I presume there is no harm in adding them but I'm not sure how when they don't display.

Secondly, sorry that I didn't include all of the code.  I was trying to eliminate any 'noise code'.  I am attaching what should be all of the pertinent code although the whole Event Code is hundreds of lines long.

Thanks.  Bob C.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Just another step to try:

Set MSExcelWorkBook = MSExcelApplication.Workbooks.Open(PARM_GROUP_IMPORT_PATH_FILENAME, , True) 'MS Excel File Path / Filename.

Change the PARM_GROUP_IMPORT_PATH_FILENAME to a real file like "c:\temp\test.xlsx", the file that you wanted to use, and see if it works or not.
If it works, we have to check the code that fills in that variable. If it doesn't work, I'm stumped.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Kimputer,

I just tried it a number of times using the Path / Filename variable and it worked every time!

I'm wondering if you have latched onto something.  I use an Event to browse to the Path / Filename.  When doing this I return to the form and then click on the button to run the Load Event.  I think if I click on another field after returning from browsing for the Path / Filename and before clicking the button to run the Load Event it seems to work.

Therefore it seems like maybe the Path / Filename variable is not actually being initialized properly without accessing clicking on another field in between.  I'm going to do some more checking.

PS I would like to know how to implement the 'References' suggested unless it could cause problems.

Thanks.  Bob C.
Missing references usually give you a "compile error - can't find project or library" error. So I don't think you have to worry about that. Just step into the VBA code, and go through every line by Step Into, while watching the variables that should be filled with the eventual file name.
There are two ways that Access VBA can use the Excel object model: early-binding and late-binding. Both approaches establish a connection between your Access VBA code and the objects, methods and properties that are part of the Excel object model.

Early-binding makes the connection using the Tools...References menu item by checking the box for "Excel xx.0 Object Library". Early-binding is faster and gives you the advantage of IntelliSense when you are writing your code. But it will also result in a run-time error message if a user with a different version of Excel tries to use your code.

Late-binding makes the connection using VBA code statements at run-time. Your code is using late-binding, which is slower and lacks IntelliSense--but avoids the run-time error message if a user has a different version of Excel.

If you are writing the code, it makes sense to use early-binding to take advantage of IntelliSense. But before you release the code, it also makes sense to switch it over to late-binding.
byundtConnect With a Mentor Commented:
Word MVP Dave Rado has a nice discussion on early and late binding, and shows the statements you would use to instantiate each.

Excel MVP Chip Pearson has a nice discussion on setting references.

After you have read and understood this Comment and the preceding one, be advised that none of that discussion affects the problem you are having with your code. Good to know, yes, but not germane to the problem at hand.

Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,
Thanks for the documents on References and Early / Late Binding.  I am using Late Binding exactly because my Application runs on machines where I don't control the Environment outside of my MS Access Application which I deliver via InstallShield complete with MS Access Runtime.

At this point I'm going to close this ticket as it appears I only get the error (and then infrequently) the first time I run the Event.  i.e.  It is so intermittent I can't debug it.

Thanks for all your help.
Bob Collison.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.