XL Automation Started Crashing Access

[lameid]

Since this is as much Access as Excel I am linking my generic VBA thread here in hopes of gaining more attention

thread707-1615566

 

[Remou]

You do not show any tidy-up. Close everything and set everything to nothing

http://lessthandot.com

 

[lameid]

The tidy-up is just not part of my code excerpt... It still should run once successfully if I didn't.

 

[RoyVidar]

My experience is that being explicit is the best way of doing automation. This would mean declarate and instantiate objects at least down to sheet level, if sheet level is needed.

And never ever rely on any of the .Activethingies, which is impicit referencing, and might not refer to what you hoped it referred to.

[tt]Set XLAPP = CreateObject("Excel.Application")
'With XLAPP
dim wr as object 'excel.workbook
set wr = xlapp.Workbooks.Open FileName:= _
strFilePath
If strNewPath <> "" Then
wr.SaveAs FileName:=strNewPath _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


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

' Now - I don't remember which is the parent object of
' the .selection object and the .cells object
' but do declare and instantiate objects, and refer
' through the parent object in stead of the implicit
' referencing you're doing

' you'll most likely also find extra instances of Excel
' in memory after such errors

' now - the below is just OTOH, as I'm not in a position
' to test at the moment, you need to check/find what
' is the parent object of the .selection object and
' the .cells object are, and use them

xlapp.Selection.Sort Key1:=wr.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[/tt]

Roy-Vidar

 

[lameid]

Thanks Roy, that was my code hunch but it is good to see some experince behind the thought before taking the time... Hopefully I will get to it today. Unfortunately this is not the only anomalous thing I am looking at <sigh>.