Late Binging MS Excel To MS Access Import

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? .

Thanks.
Bob Collison
[Edited by SouthMod]
All-Code.txt
Bob CollisonSystem ArchitectAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
 
broro183Connect With a Mentor Commented:
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.

Rob
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Bob CollisonSystem ArchitectAuthor Commented:
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.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
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
0
 
broro183Commented:
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


Rob
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
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.
0
 
broro183Commented:
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 ;-)

Rob
0
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.