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


Late Binging MS Excel To MS Access Import

Posted on 2013-11-06
Medium Priority
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
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
LVL 85
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 800 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 workbooks.open 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.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 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 85
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

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