Solved

Late Binging MS Excel To MS Access Import

Posted on 2013-11-06
9
314 Views
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? .

Thanks.
Bob Collison
[Edited by SouthMod]
All-Code.txt
0
Comment
Question by:Bob_Collison
  • 3
  • 3
  • 3
9 Comments
 
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?
0
 
LVL 10

Assisted Solution

by:broro183
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 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
 

Author Comment

by:Bob_Collison
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.
0
 
LVL 84

Accepted Solution

by:
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Closing Comment

by:Bob_Collison
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
0
 
LVL 10

Expert Comment

by:broro183
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


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

Author Comment

by:Bob_Collison
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.
0
 
LVL 10

Expert Comment

by:broro183
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 ;-)

Rob
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 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

14 Experts available now in Live!

Get 1:1 Help Now