Solved

MS VB Run-time error '1004

Posted on 2013-10-27
9
1,927 Views
Last Modified: 2013-11-07
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.

Thanks.
Bob Collison
[Edited by SouthMod]
Run-time-error--1004.txt
0
Comment
Question by:Bob_Collison
  • 3
  • 3
  • 3
9 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 400 total points
ID: 39604661
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.
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 100 total points
ID: 39604795
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.
0
 

Author Comment

by:Bob_Collison
ID: 39607384
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.
Run-time-error--1004---All-Code.txt
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 39607419
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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:Bob_Collison
ID: 39607467
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.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 39607479
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.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39607504
Bob,
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.
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 100 total points
ID: 39607527
Word MVP Dave Rado has a nice discussion on early and late binding, and shows the statements you would use to instantiate each. http://word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm

Excel MVP Chip Pearson has a nice discussion on setting references.
http://www.cpearson.com/Excel/MissingReferences.aspx


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.

Brad
0
 

Author Comment

by:Bob_Collison
ID: 39607562
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.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now