?
Solved

Opening Excel from Access causes Access to crash

Posted on 2009-12-23
15
Medium Priority
?
347 Views
Last Modified: 2013-11-27
I have an Access 2007 database I use to create and format Excel reports for a large team. It has been working just fine up to a couple days ago. Then it started giving me a "Access has encountered an error and needs to close" popup.  Using some MsgBoxes I was able to determine that the error occurs in a subroutine that opens an existing Excel file, deletes some extraneous columns of code (no headers and sometimes garbage characters live there), saves and closes the file.
If I comment out the lines

Set xlApp = New Excel.Application and     Set xlApp = Nothing

then it works OK.  Note that I have to comment out the xlApp lines in a couple other, but not all, places it exists in the code. But of course, if I do that, I do not get the tweeks I need.

Any ideas what could be causing this to decide to fail now?   I have added the actual code below for one of the subs that is causing the problem.  Another opens an existing Excel file and formats specific cells, fills, bold, that kind of thing.

Sub TweakMetricsReport()

Dim xlApp As Excel.Application
Dim filePath As String

filePath = CurrentProject.path & "\Linked\metrics_report.xls"
    
    On Error Resume Next
    Set xlApp = New Excel.Application
    xlApp.Visible = False
    xlApp.Workbooks.Open FileName:=filePath, ReadOnly:=False
    
    xlApp.sheets("data").Select
    xlApp.Columns("BY:CZ").Select
    xlApp.Selection.Delete Shift:=xlToLeft
    xlApp.ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    
    xlApp.ActiveWorkbook.Save
    xlApp.ActiveWorkbook.Close
    xlApp.Quit
    
    Set xlApp = Nothing

End Sub

Open in new window

0
Comment
Question by:JazCat
15 Comments
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 26113207
did u check if the file is exists or not used by another process?
0
 

Author Comment

by:JazCat
ID: 26113405
The file definitely exists and is not being used by another process. This occurs on both files I am downloading to use within the database as well as files I create from Access and then open, with Excel from my database, to do formatting.
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26113638
This sounds to me like it could be a problem with references and/or the version of Excel itself.  Here are a few questions to help pinpoint the problem.

Does this happen on every machine, just a few, or just yours?

Do some users have Office 2K3, or do all users have Office 2K7?  If all users have Office 2K7, do they all have the same updates (service packs, hotfixes, etc)?  I have found Access 2007 to be VERY picky when it comes to updates between users.

What do you have in your project references for Excel, and have you considered using late binding instead?

On occasion, I have also found that sometimes a problem exists within Excel itself and it needs to be repaired.   In this instance, trying to open Excel from Access will immediately fail.  However, if you open the Excel application directly, after it repairs itself everything works fine again.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:JazCat
ID: 26113805
Torrwin,

I am the only one who runs this program and I have office 2007 and all the updates.  

I am trying the Repair to 2007 Office to see if that helps. I am not sure what you mean about late binding, can you be more specific?
0
 
LVL 9

Expert Comment

by:Stephan_Schrandt
ID: 26113855
Did you try it with  xlApp.Visible = True? Does it crash as well?
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26114163
Hmm, if you are the only person that uses the application then the binding will probably not matter.  Basically, it allows your VBA to use which version of Office to use at run time, instead of selecting it in your references at design time.

Here is a web page that explains Early vs. Late binding:  http://word.mvps.org/fAQs/InterDev/EarlyvsLateBinding.htm  


Since the problem is occuring when you attempt to use the Excel application, it is probably not something to do with your VBA code.  It is more likely a problem with either Excel itself, or the reference to Excel.  I also find it interesting that if you comment out "Set xlApp = New Excel.Application", it starts working.

Two more questions for you:
1)  What references do you have selected for your project?
2)  In your Windows Task Manager, do you have lots of instances of Excel open?
0
 

Author Comment

by:JazCat
ID: 26114275
I tried the repair for Office, didn't help.  

For you other questions, 2 is easier, no instances of Excel open when I open the database. Answer to 1 is longer.  MS is Microsoft, saved me some keystrokes.

Visual Basic for Applications
MS Access 12.0 Object Library
OLE Automation
MS ActiveX Data Objects 2.1 Library
MS Scripting Runtime
MS WinHTTP Services, version 5.1
MS Outlook 12.0 Object Library
MS Excel 12.0 Object Library
MS Windows Common Controls 6.0 (SP6)
JET Expression Service Type Library
MS Jet and Replication Objects 2.6 Library
Common Language Runtime Execution Engine 2.0 Library
Export 1.0 Type Library
MS Office 12 Authorization Control 1.0 Type Library
MS Office 12.0 Access database engine Object Library
MS Visual Basic for Applications Extensibility 5.3
ftpseui 1.0 Type Library
0
 

Author Comment

by:JazCat
ID: 26114533
And, my apologies for not saying this before, thank you for the link to the binding data, Ex-Ex is how I increase my knowledge of Access.
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26114606
Unless debugging can narrow down to a line of code other than the two you mentioned, the only other options I can think of are to be more explicit in your Excel code, or decompile the database (that's really a last resort if all else fails).  

Very rarely do I use the application object itself, instead I use the workbook and worksheet objects like so:
Sub TweakMetricsReport() 
 
Dim xlApp As Excel.Application 
Dim xlBook as Excel.Workbook
Dim xlSheet as Excel.Worksheet
Dim filePath As String 
 
filePath = CurrentProject.path & "\Linked\metrics_report.xls" 
     
    On Error Resume Next 
    Set xlApp = New Excel.Application 
    xlApp.Visible = False 
    
    Set xlBook = xlApp.Workbooks.Open(filePath) 
    
    Set xlSheet = xlBook.Worksheets("Data")
    xlSheet.Activate
    
    xlSheet.Columns("BY:CZ").Select 
    xlSheet.Selection.Delete Shift:=xlToLeft 
    xlApp.ActiveWindow.ScrollWorkbookTabs Position:=xlFirst 
     
    xlBook.Save 
    xlBook.Close 
    xlApp.Quit 
     
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing 
 

Open in new window

0
 

Author Comment

by:JazCat
ID: 26114834
I am trying your suggestion now. One question, if I am tweaking multiple worksheets in the same workbook do I just activate the different sheets, make the modifications and then do the xlBook.Save after all the changes are made?
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26115141
Yes, that is correct.  If you try to do most anything to a worksheet that hasn't been "activated", it will throw an error.

You probably already know this, but if you comment out the "On Error Resume Next" line the VBA should break wherever the error occurs while you're testing.
0
 

Author Comment

by:JazCat
ID: 26115182
Unfortunately the changes did not resolve the problem.  I tried again, commented out all the different steps except for that one, drilled down to Set xlApp = New Excel.Application and got the error again.
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26115333
Have you tried decompiling?
0
 

Accepted Solution

by:
JazCat earned 0 total points
ID: 26117642
I have no idea why this worked but I did two things and now the database is working normally. I changed the order of the references, moved the Excel Object library up further to the top of the list and added the MS Word Object library. No other changes and now it's working. It's too late for me to remove one to see which one allowed it to work.

Thanks Torrwin for your help. I am still clueless as to why these two changes allowed the database to start working again, especially since I had not changed anything before it stopped working.

Happy Holidays to All
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26119180
Great to hear!  Merry Christmas!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

839 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