Late Binging MS Excel To MS Access Import

Posted on 2013-11-06
Last Modified: 2013-11-09
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.
 Now sometimes when I try to run it on Windows 7 64 Bit machine with MS Excel 2010 installed and MS Access 2003 importing a MS Excel 2003 File it hangs if I provide the Path / Filename as a Parameter Field.  It works if I replace the Parameter Field hard coded.  See attached code or I display the Parameter Field with a MsgBox immediately before the following code line.  It's like the Response Time for the Message gives it time to get ready to execute.
Set MSExcelWorkBook = MSExcelApplication.Workbooks.Open(PARM_GROUP_IMPORT_PATH_FILENAME, , True) 'MS Excel File Path / Filename.

It appears that there is not enough time for the value in the Parameter Field to take effect before the code above is executed.
Could you please take a look at this and advise? .

Bob Collison
[Edited by SouthMod]
Question by:Bob_Collison
  • 3
  • 3
  • 3
LVL 84
ID: 39629804
Can you show us the value of "PARM_GROUP_IMPORT_PATH_FILENAME"?

Also, what is that value? Is it a variable declared in your code? Or a TempVar?
LVL 10

Assisted Solution

broro183 earned 200 total points
ID: 39629842
hi Bob,

What type of variable is PARM_GROUP_IMPORT_PATH_FILENAME?

Do you get the same value for PARM_GROUP_IMPORT_PATH_FILENAME if you use "debug.print PARM_GROUP_IMPORT_PATH_FILENAME" instead of a message box?

I'm not sure if it will work, but does it help if you include a "DoEvents" statement after populating the subform & before using the PARM_GROUP_IMPORT_PATH_FILENAME variable in the statement?

As an alternative to the subform, would it be possible to use "MSExcelApplication.GetOpenFilename..." to identify the desired path/file?
GetOpenFilename is a method of the excel Application object & is detailed in the excel vba help files.


Author Comment

ID: 39632294
Hi Experts,
The  PARM_GROUP_IMPORT_PATH_FILENAME is an Unbound field on a Form.  Its contents varies but an example is "M:\Co-op\01-MDB-Design\SAS\Import\Group Forms\Pass\P00010 21st Hamilton Pathfinders Group Registration Data Form.xls"

I am not familiar with the "MSExcelApplication.GetOpenFilename" Method or the "DoEvents' Statement although I'm open to trying anything.

Thanks.  Bob C.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 39634623
If PARM_GROUP_IMPORT_PATH_FILENAME is a value on a form, then why not just refer to that directly instead of jumping through hoops:

Set MSExcelWorkBook = MSExcelApplication.Workbooks.Open(Forms("YourForm").ControlThatHasTheValue, , True) 'MS Excel File Path / Filename.

Author Closing Comment

ID: 39634837
Hi Experts.
I have tried and tested 100% successfully both the suggestion to use 'DoEvents' and to directly reference the Path / Filename.  Both worked without any issues at all.  The only reason I split the points the way I did is because the 'DoEvent' solution appears to have slightly greater risk of problem that the direct reference.  Since the 'DoEvents' basically ensures that setting up the Path / Filename completes before continuing execution I assume that I was on the right track about my PC being too fast.  I encountered this problem when I upgraded the Ram from 8 Gig to 32 Gig on a 3.4 Gig memory 64 Bit machine.  Also I have tried this with .xls (Excel 2003) and .xlsm (Excel 2010) files and both work perfectly.
Thanks for all your assistance.
Bob Collison
LVL 10

Expert Comment

ID: 39635362
hi Bob,

Thanks for the points, I'm pleased we could help. LSM's got a lot more MS Access experience than me & my DoEvents suggestion was just a shot in the dark. I've used it in my work in the past to overcome issues which I didn't understand. It can help, but it can also slow code down if used repeatedly eg within loops.

My understanding is that the code is processed "in series", so I don't understand how your machine could be too fast...

LSM, could it have something to do with the value being from a "subform"?
For example, could the subform need to be re-queried/refreshed before an attempt is made to use the value of the field?
Or is it possible that some action needs to be completed as part of the field's Exit Event?
Or is "too fast" a more probable candidate?

[PARM_GROUP_IMPORT_PATH_FILENAME] = Forms![F-40-850 - Event Group Import Data 5 Form]![F-40-851 - Event Group Import Data 5 [b]SubForm[/b]].Form![WRK_IMPORT_PATH_FILENAME]

Open in new window

LVL 84
ID: 39635404
DoEvents just allows other processes to complete before moving forward in the code. It does not insure those process complete, however. It should be used sparingly, and should never be depended on for situations like this. It's typically used in long running code blocks where the user would need to interact with their machine otherwise (for example, in a long running loop where the user might click away from the Access app to check email, for example).

Access will always save the data in a Subform when the user moves out of it, so I don't think the concept of the subform data not saving is relevant here.

I assume that I was on the right track about my PC being too fast.
My machine is running an ASUS G motherboard with an Intel I-7 Extreme processor, with 16 gb of very fast ram, and smoking fast hard drives. I've never ran code that was "too slow" for this machine. I suppose if you were running a loop where you're writing to a remote database, and then reading back from that database, that you could get ahead of yourself ... but not with stuff like this. Access may have been caching a value (which is far, far more likely), but I seriously doubt your machine is outrunning the code.

Author Comment

ID: 39635625
Hi Experts,
Thanks for the update comments.

My development machine is an ASUS i7-3770 CPU @ 3.40Ghz with 32 GB Ram.  The drives are all 7200 RPM Western Digital Industrial level.

Although there is a lot of code in this Event and it does have loops, they are relatively simple and involve less than 30 records at a time.  This application typically is run on User hardware that I don't have any control over other than what I provide in the install via  InstallShield that consists of the Application and MS Access 2003 Runtime.   I am aware that it runs successfully with the following configurations.:
- XP 2003, Office 2003, Access Runtime 2003.
- Vista, Office 2003,  Access Runtime 2003.
- Windows 7, Office 2003, Access Runtime 2003.
- Windows 7, Office 2010, Access Runtime 2003.

By the way I am a System Architect (retiring in 3 months) with Canada's largest bank.
Thanks again for all your advice / comments.
Bob C.
LVL 10

Expert Comment

ID: 39636015
hi guys,

Thanks LSM, your statment "It should be used sparingly, and should never be depended on for situations like this." is what I was thinking but lacked the experience to state directly.

Bob, enjoy your last three months :-)
If you need something to keep your mind active after retirement, you could become an active E-E member in the area/topic of your expertise ;-)


Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

749 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