XL Automation Started Crashing

[lameid]

This is simply weird...

Code I have used dozens of time to manipulate XL files from Access crashes Access.

The only thing that seems to have changed is the installation of KB981716. Sadly this update was on 7/14/10 and my auto restore points have rolled this update off the list and it is not an update that can be uninstalled to properly test short of finding my intallation media which I may do tonight.

This is a fully patched install of Office 2003 where the issue occurs and while some of the XL automation runs, other parts do not. To be on the safe side I have decompiled and recompiled the Access database.


The code snippet that blows up...

'A lot of code removed for brevity

Dim XLAPP As Excel.Application

Set XLAPP = CreateObject("Excel.Application")
With XLAPP
            .Workbooks.Open FileName:= _
                strFilePath
            If strNewPath <> "" Then
                .ActiveWorkbook.SaveAs FileName:=strNewPath _
                    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
                    ReadOnlyRecommended:=False, CreateBackup:=False


'Bunch of code that manipulates file makes a selection...

'The below crashes
.Selection.Sort Key1:=.Cells(1, RS!Detail_Column), Order1:=xlAscending, Header:=IIf(RS!Has_Header_Row, xlYes, xlNo), _
                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal

    'More manipulation and saving file etc.

     End With

Interestingly I have manipulated the failing line once to include the XLAPP object before the .cells and had it pass that line but this does not seem repeatable and I do not know which line it failed on the one time it did pass this.

Any thoughts would be appreciated.

While i do not think this could possibly be relevant, I have also moved my data backend to SQL Express 2008 R2 and the code does use a DAO recordset since I remember successfuly running the code.

 

[combo]

You could make XLAPP visible (XLAPP.Visible=True), so you will see if the workbook is opened and what is going on there.
Before sorting, test (Msgbox) values of RS!Detail_Column and RS!Has_Header_Row.
Check references (Excel, DAO) and their order. To avoid mixing recordsets, declare RS as DAO.Recordset.


combo

 

[lameid]

Combo thanks but I already have done all that except that I checked the values of the Recordset fields while debugging...

I even went so far as to make Excel visible while my code was in break mode on the line of impending doom. I opened the Excel immediate window, pasted in the failing code, substituted the Recordset fields for the literal values from access and prepended the .Object references with application. That executes... Step through the next line in Access and another crash. Too bad it is a hard crash of the app and not an error in the code <sigh>.

Code-wise the only thought I have is to target a sheet rather than the application object which is an obnoxious adjustment to the code and may not even work... On the flip side I am thinking hard about doing the uninstall and reinstall of office to snuff out the suspect patch. Right now I have bigger fish to fry before I can go down either time consuming path.

 

[combo]

What is selected? (XLAPP.Selection.Address). Is the sheet protected?

I would declare two extra ranges, one for range to sort and the second for Key1, check their addresses and try to sort in the next line. All with excel visible from the beginning, code executed in access line by line and testing type and value of used variables.

You could also test sorting with automation with simple ready to use table in excel and hard-coded ranges.



combo