I am getting the subject line error, but only ocassionally when I run my code
-- and I can't figure out a pattern to when it bombs and when it doesn't.

My code is in workbook 1, I open a user-specified workbook 2, which is the
active workbook when it reaches this sub. I am trying to replace the data in
the user-specified range in workbook 2 with new data from a recordset and
save to the same range name.

savetorange is a public string variable containing the user-specified range
name.

Can anyone see what is wrong with the code below? Thanks.

Public Sub manageoutput_exists()

Dim rngResultSet As Range
Dim ww As Variant

' get upper-left most cell of the current range in workbook 2
ww = Range(savetorange).Cells(1, 1).Address

'delete the range
Range(savetorange).Delete

'add field headers from the results of the sql query

For j = 0 To recset.Fields.Count - 1
-next line bombs
Range(ww).Offset(0, j).Value = recset(j).Name
Next j

'copy the recordset data below the headers
ww = Range(ww).Offset(1, 0).Address
Range(ww).CopyFromRecordset recset

Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion
rngResultSet.Name = savetorange
ActiveWorkbook.Save


End Sub

When it bombs out, enter Debug and see what 'j' evaluates to and check to see
what each side of the equation evaluates to - either Offset(0,j) is illegal,
or something is really odd with the recset.Fields.Count value. Since the
second option should, in theory, never be invalid or illegal, I suspect that
you're offsetting into an invalid column. Look at ww at that point, look at
value of j and make sure that the resultant column is legitimate.

"in-over-his-head-bill" wrote:

I am getting the subject line error, but only ocassionally when I run my code
-- and I can't figure out a pattern to when it bombs and when it doesn't.

My code is in workbook 1, I open a user-specified workbook 2, which is the
active workbook when it reaches this sub. I am trying to replace the data in
the user-specified range in workbook 2 with new data from a recordset and
save to the same range name.

savetorange is a public string variable containing the user-specified range
name.

Can anyone see what is wrong with the code below? Thanks.

Public Sub manageoutput_exists()

Dim rngResultSet As Range
Dim ww As Variant

' get upper-left most cell of the current range in workbook 2
ww = Range(savetorange).Cells(1, 1).Address

'delete the range
Range(savetorange).Delete

'add field headers from the results of the sql query

For j = 0 To recset.Fields.Count - 1
-next line bombs
Range(ww).Offset(0, j).Value = recset(j).Name
Next j

'copy the recordset data below the headers
ww = Range(ww).Offset(1, 0).Address
Range(ww).CopyFromRecordset recset

Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion
rngResultSet.Name = savetorange
ActiveWorkbook.Save


End Sub

Thanks for the help; still don't see the answer.
in the debugger
j is 0
recset.fields.count=4
ww = $a$1
and recset(j).Name='effmonth'

when I position float the cursor over the whole line In the debugger I get
range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed

Is range($a$1).Offset(0,0) an ok statement?


"JLatham" wrote:

When it bombs out, enter Debug and see what 'j' evaluates to and check to see
what each side of the equation evaluates to - either Offset(0,j) is illegal,
or something is really odd with the recset.Fields.Count value. Since the
second option should, in theory, never be invalid or illegal, I suspect that
you're offsetting into an invalid column. Look at ww at that point, look at
value of j and make sure that the resultant column is legitimate.

"in-over-his-head-bill" wrote:

I am getting the subject line error, but only ocassionally when I run my code
-- and I can't figure out a pattern to when it bombs and when it doesn't.

My code is in workbook 1, I open a user-specified workbook 2, which is the
active workbook when it reaches this sub. I am trying to replace the data in
the user-specified range in workbook 2 with new data from a recordset and
save to the same range name.

savetorange is a public string variable containing the user-specified range
name.

Can anyone see what is wrong with the code below? Thanks.

Public Sub manageoutput_exists()

Dim rngResultSet As Range
Dim ww As Variant

' get upper-left most cell of the current range in workbook 2
ww = Range(savetorange).Cells(1, 1).Address

'delete the range
Range(savetorange).Delete

'add field headers from the results of the sql query

For j = 0 To recset.Fields.Count - 1
-next line bombs
Range(ww).Offset(0, j).Value = recset(j).Name
Next j

'copy the recordset data below the headers
ww = Range(ww).Offset(1, 0).Address
Range(ww).CopyFromRecordset recset

Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion
rngResultSet.Name = savetorange
ActiveWorkbook.Save


End Sub

ww = "$A$1"
j = 0
? Range(ww).Offset(0, j).Address
$A$1


--
Regards,
Tom Ogilvy


"in-over-his-head-bill" wrote:

Thanks for the help; still don't see the answer.
in the debugger
j is 0
recset.fields.count=4
ww = $a$1
and recset(j).Name='effmonth'

when I position float the cursor over the whole line In the debugger I get
range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed

Is range($a$1).Offset(0,0) an ok statement?


"JLatham" wrote:

When it bombs out, enter Debug and see what 'j' evaluates to and check to see
what each side of the equation evaluates to - either Offset(0,j) is illegal,
or something is really odd with the recset.Fields.Count value. Since the
second option should, in theory, never be invalid or illegal, I suspect that
you're offsetting into an invalid column. Look at ww at that point, look at
value of j and make sure that the resultant column is legitimate.

"in-over-his-head-bill" wrote:

I am getting the subject line error, but only ocassionally when I run my code
-- and I can't figure out a pattern to when it bombs and when it doesn't.

My code is in workbook 1, I open a user-specified workbook 2, which is the
active workbook when it reaches this sub. I am trying to replace the data in
the user-specified range in workbook 2 with new data from a recordset and
save to the same range name.

savetorange is a public string variable containing the user-specified range
name.

Can anyone see what is wrong with the code below? Thanks.

Public Sub manageoutput_exists()

Dim rngResultSet As Range
Dim ww As Variant

' get upper-left most cell of the current range in workbook 2
ww = Range(savetorange).Cells(1, 1).Address

'delete the range
Range(savetorange).Delete

'add field headers from the results of the sql query

For j = 0 To recset.Fields.Count - 1
-next line bombs
Range(ww).Offset(0, j).Value = recset(j).Name
Next j

'copy the recordset data below the headers
ww = Range(ww).Offset(1, 0).Address
Range(ww).CopyFromRecordset recset

Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion
rngResultSet.Name = savetorange
ActiveWorkbook.Save


End Sub

I figured out a fix late last night without understanding why I needed the
fix. By hardcoding some range values instead of assigning the recordset to
the range, I discovered that even though WorkBook 2 was the active workbook;
it was trying to place the newly created range on Workbook 1 (I am still not
sure why it errored out as opposed to just putting the range on WB 1). I
replaced all my Range... statements with
Worksheet(wz).Range ...
where wz=Range(the user specified range name in Workbook 2).Worksheet.Name
and it works.

Thanks to all for all their help.

"Tom Ogilvy" wrote:

ww = "$A$1"
j = 0
? Range(ww).Offset(0, j).Address
$A$1


--
Regards,
Tom Ogilvy


"in-over-his-head-bill" wrote:

Thanks for the help; still don't see the answer.
in the debugger
j is 0
recset.fields.count=4
ww = $a$1
and recset(j).Name='effmonth'

when I position float the cursor over the whole line In the debugger I get
range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed

Is range($a$1).Offset(0,0) an ok statement?


"JLatham" wrote:

When it bombs out, enter Debug and see what 'j' evaluates to and check to see
what each side of the equation evaluates to - either Offset(0,j) is illegal,
or something is really odd with the recset.Fields.Count value. Since the
second option should, in theory, never be invalid or illegal, I suspect that
you're offsetting into an invalid column. Look at ww at that point, look at
value of j and make sure that the resultant column is legitimate.

"in-over-his-head-bill" wrote:

I am getting the subject line error, but only ocassionally when I run my code
-- and I can't figure out a pattern to when it bombs and when it doesn't.

My code is in workbook 1, I open a user-specified workbook 2, which is the
active workbook when it reaches this sub. I am trying to replace the data in
the user-specified range in workbook 2 with new data from a recordset and
save to the same range name.

savetorange is a public string variable containing the user-specified range
name.

Can anyone see what is wrong with the code below? Thanks.

Public Sub manageoutput_exists()

Dim rngResultSet As Range
Dim ww As Variant

' get upper-left most cell of the current range in workbook 2
ww = Range(savetorange).Cells(1, 1).Address

'delete the range
Range(savetorange).Delete

'add field headers from the results of the sql query

For j = 0 To recset.Fields.Count - 1
-next line bombs
Range(ww).Offset(0, j).Value = recset(j).Name
Next j

'copy the recordset data below the headers
ww = Range(ww).Offset(1, 0).Address
Range(ww).CopyFromRecordset recset

Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion
rngResultSet.Name = savetorange
ActiveWorkbook.Save


End Sub

Obviously you have your code in the Thisworkbook module or a sheet module.
However, code like this:

Public Sub manageoutput_exists()


shouldn't be in one of those modules, but in a general module. It certainly
occured to me that the problem could be caused by that, but when I checked
your declaration, it wasn't an event (the only thing which should be in these
modules), so I discounted that as the problem. (too hasty on my part)

The cause is that an unqualified range reference located in a sheet module
refers to that sheet. You have stumbled into the solution of qualifying it.
In a general module, and unqualified range reference refers to the active
sheet.

--
Regards,
Tom Ogilvy


"in-over-his-head-bill" wrote:

I figured out a fix late last night without understanding why I needed the
fix. By hardcoding some range values instead of assigning the recordset to
the range, I discovered that even though WorkBook 2 was the active workbook;
it was trying to place the newly created range on Workbook 1 (I am still not
sure why it errored out as opposed to just putting the range on WB 1). I
replaced all my Range... statements with
Worksheet(wz).Range ...
where wz=Range(the user specified range name in Workbook 2).Worksheet.Name
and it works.

Thanks to all for all their help.

"Tom Ogilvy" wrote:

ww = "$A$1"
j = 0
? Range(ww).Offset(0, j).Address
$A$1


--
Regards,
Tom Ogilvy


"in-over-his-head-bill" wrote:

Thanks for the help; still don't see the answer.
in the debugger
j is 0
recset.fields.count=4
ww = $a$1
and recset(j).Name='effmonth'

when I position float the cursor over the whole line In the debugger I get
range(ww).Offset(0,j).Value=<Method 'range' of object '_global' failed

Is range($a$1).Offset(0,0) an ok statement?


"JLatham" wrote:

When it bombs out, enter Debug and see what 'j' evaluates to and check to see
what each side of the equation evaluates to - either Offset(0,j) is illegal,
or something is really odd with the recset.Fields.Count value. Since the
second option should, in theory, never be invalid or illegal, I suspect that
you're offsetting into an invalid column. Look at ww at that point, look at
value of j and make sure that the resultant column is legitimate.

"in-over-his-head-bill" wrote:

I am getting the subject line error, but only ocassionally when I run my code
-- and I can't figure out a pattern to when it bombs and when it doesn't.

My code is in workbook 1, I open a user-specified workbook 2, which is the
active workbook when it reaches this sub. I am trying to replace the data in
the user-specified range in workbook 2 with new data from a recordset and
save to the same range name.

savetorange is a public string variable containing the user-specified range
name.

Can anyone see what is wrong with the code below? Thanks.

Public Sub manageoutput_exists()

Dim rngResultSet As Range
Dim ww As Variant

' get upper-left most cell of the current range in workbook 2
ww = Range(savetorange).Cells(1, 1).Address

'delete the range
Range(savetorange).Delete

'add field headers from the results of the sql query

For j = 0 To recset.Fields.Count - 1
-next line bombs
Range(ww).Offset(0, j).Value = recset(j).Name
Next j

'copy the recordset data below the headers
ww = Range(ww).Offset(1, 0).Address
Range(ww).CopyFromRecordset recset

Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion
rngResultSet.Name = savetorange
ActiveWorkbook.Save


End Sub

I don't see anything wrong with your code. Run it again and choose debug
when it bombs, then check the value of recset(j).name by typing this into
the immediate window:
?recset(j).Name
That should at least make sure that you are trying to write an actual value
to the range.

HTH

Die_Another_Day
"in-over-his-head-bill" wrote
in message ...
I am getting the subject line error, but only ocassionally when I run my
code
-- and I can't figure out a pattern to when it bombs and when it doesn't.

My code is in workbook 1, I open a user-specified workbook 2, which is the
active workbook when it reaches this sub. I am trying to replace the data
in
the user-specified range in workbook 2 with new data from a recordset and
save to the same range name.

savetorange is a public string variable containing the user-specified
range
name.

Can anyone see what is wrong with the code below? Thanks.

Public Sub manageoutput_exists()

Dim rngResultSet As Range
Dim ww As Variant

' get upper-left most cell of the current range in workbook 2
ww = Range(savetorange).Cells(1, 1).Address

'delete the range
Range(savetorange).Delete

'add field headers from the results of the sql query

For j = 0 To recset.Fields.Count - 1
-next line bombs
Range(ww).Offset(0, j).Value = recset(j).Name
Next j

'copy the recordset data below the headers
ww = Range(ww).Offset(1, 0).Address
Range(ww).CopyFromRecordset recset

Set rngResultSet = Range(ww).End(xlDown).End(xlToRight).CurrentRegion
rngResultSet.Name = savetorange
ActiveWorkbook.Save


End Sub