Error 1004 ""Method 'Range' of object '_Global' Failed

[Andrew R]

Hi

I am being plagued by a problem which I cannot resolve, so I turn to
the Repository Of All Knowledge.....


I have a procedure in a VB application (not within Excel, but an
application I've created myself). It contains the following code:


Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rng As Excel.Range
Dim x As Integer


Set xl = New Excel.Application
xl.Visible = True


Set wb = xl.Workbooks.Add


Set ws = wb.Worksheets(1)
Set rng = ws.Range("A1")
For x = wb.Worksheets.Count To 2 Step -1
wb.Worksheets(x).Delete
Next x


For Each fld In rsData.Fields
rng.Value = fld.Name
Set rng = rng.Offset(, 1)
Next fld
'-----------
Set rng = Range("A1", Range("A1").End(xlToRight))
'-----------
rng.Font.Bold = True
rng.Interior.Color = RGB(255, 255, 180)
Set rng = ws.Range("A2")
rng.CopyFromRecordset rsData
Set rng = Range("A1").CurrentRegion
rng.Columns.AutoFit
rng.Rows.RowHeight = 14
wb.SaveAs "Exported data " & Format(Now, "dd-mm-yy at hh-mm-ss")
MsgBox "File saved as " & wb.FullName, vbInformation + vbSystemModal,
"File saved successfully"
Set xl = Nothing
Set wb = Nothing
Set ws = Nothing
Set rng = Nothing


The line highlighted above, beginning "Set rng=Range("A1", Range..... "

causes a runtime error 1004 with the message "Method 'Range' of object
'_Global' failed. "


BUT... it works the first time I run the procedure without fail. It's
the second time I run the procedure which generates the error. The
error occurs ALMOST always, on the second (and subsequent) runnings of
the procedure, although testing again just now it ran fine every time,
and it ALWAYS runs fine the first time....


Any thoughts? I'd be grateful for any help


Regards
Andrew

 

[NickHK]

Andrew,
Using Automation you need to make sure all you objects go through the
application object, amy be indirectly.
Here you have an unqualified range object
Set rng = Range("A1", Range("A1").End(xlToRight))
I suppose you mean:
with ws
Set rng = .Range("A1", .Range("A1").End(xlToRight))
end with

NickHK

 

[Andrew R]

But I thought the idea of a Global object was that it can be used
without qualifying it first...

Or am I mistaken?

Andrew

 

[Tom Ogilvy]

In automation, in the background, the code counts references established to
Excel. Excel can not be released until all the references are released. by
not qualifying these objects, you create unreleasable references and the
Excel application will remain in memory.

If you go to the task manager, you should find the Excel still in memory.
This is apparently causing the conflict on the second running.