A
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
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