Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS VB Run-time error '1004

Posted on 2013-10-27
9
Medium Priority
?
2,117 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 36

Accepted Solution

by:
Kimputer earned 1600 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 81

Assisted Solution

by:byundt
byundt earned 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 36

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
 

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 36

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 81

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 81

Assisted Solution

by:byundt
byundt earned 400 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

610 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