Sorting Excel Spreadsheet

[Guest]

I have a procedure which opens an Excel template, adds data, and optionally
saves the Excel file. I can sort the sheet from the Access code, but it only
works the first time Excel is opened in the current Access session. If I
close Access and Excel, then reopen Access and run the procedure again, it
sorts. But if I attempt to run it again, it does not.

Global objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Application.Workbooks.Add Template:=TemplateFile

'Sheet contains a named range Detail
with objxl
....code....
.Goto Reference:="Detail"
.Range("Detail").Select
Selection.Sort Key1:=Range("N5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Error message is Runtime 1004
Method 'Range' of object '_Global' failed

After spreadsheet is created, procedure leaves it open but sets excel object
to nothing (so user can use the spreadsheet). I tried without setting the
object to nothing but same thing happens.

Thanks

 

[Stefan Hoffmann]

hi,

Global objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Application.Workbooks.Add Template:=TemplateFile

'Sheet contains a named range Detail
with objxl

After spreadsheet is created, procedure leaves it open but sets excel object
to nothing (so user can use the spreadsheet). I tried without setting the
object to nothing but same thing happens.

Don't declare it global, it should be a private member variable of the
procedure. Also don't use With objxl.

Set objxl and all other referenced objects to Nothing.


mfG
--> stefan <--