Exporting to excel sheet with dynamic range

[frispee]

Hi,

I have adapted a piece of code which is used to copy a record set to an excel worksheet. What I want to do is copy multiple record sets one after the other and paste them side by side in the worksheet. This number of record sets is not fixed and is determined by a counter. Basically, this is the summary of what is happening:

Set rs to something
Set objXL = New Excel.Application
Set objWkb = .Workbooks.Open(conWKB_NAME)
Set objSht = objWkb.Worksheets(conSHT_NAME)
objSht.Range(E4).CopyFromRecordset rs

And this is my target:

Set objXL = New Excel.Application
Set objWkb = .Workbooks.Open(conWKB_NAME)
Set objSht = objWkb.Worksheets(conSHT_NAME)
Range = "E4"
For Counter = 1 to some value
Set rs to something with criteria = Counter
objSht.Range(Range).CopyFromRecordset rs
Change Range to new value
Next Counter

The way 'Range' changes is fixed. It moves by 5 cells to the right. So for the first iteration, if it is E4, in the next iteration, it should be J4 and so on.

Any advice/comments would be greatly appreciated. Thank you in advance

 

[boblarson]

Don't use the lettering and Range. Use Cells(c, r) instead where c is the column number and r is the row. Then you can increment your column counter by the number you need it to move.

 

[frispee]

Thanks a lot! That will work for me