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

 

[Nick Hodge]

Andrew

You appear to be re-using rng several times in quick succession, have you
tried setting it to nothing between uses and re-setting it to

ws.Range(.......

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Andrew R]

Hmmm. Okay, I'll give it a go.

Is this the way it should be or a glitch? The reason I ask, is that
surely this has an impact on loops such as

For Each fld In rsData.Fields
rng.Value = fld.Name
Set rng = rng.Offset(, 1)
Next fld

In such a loop, rng is being re-set in rapid succession, and I don't
see a way of setting it to nothing in between.

Thanks for your help.

 

[Nick Hodge]

Andrew

In that loop I would use a counter rather than resetting the rng each time
(Psuedo code)

rng.Offset(x,1).value=fld.name
x=x+1
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk