Formatting Access Export to Excel - VBA

[derekben]

Hi,

I am looking to export a table to excel from access. I would like to add a column with a header of RefNumber and it will add one every row until the last row of data, starting at 1 (1,2,3,4 ect.) I have successfully added the title and have been able to find how to calculate getting the last row but I can not get it to go down the column and add one. This is the code I have so far. ANY HELP WOULD BE AMAZING! THANK YOU.

NOTE: oWB.Sheets(1) is referring to the export excel document being created by this button.

oWB.Sheets(1).Range("G1").Value = "RefNumber"


Dim x As Integer
Dim total As Integer
total = 1
x = oWB.Sheets(1).UsedRange.Rows.Count


oWB.Sheets(1).Range("G2").Value = total


For i = 1 To x
oWB.Sheets(1).Range("G3").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = total
oWB.Sheets(1).Range("G3").Value = total


total = total + 1
Next

[June7]

You keep selecting G3 within the loop so not moving anywhere.

Faster code if you don't 'jump around' the sheet.

Try:

For i = 1 To x
oWB.Sheets(1).Range("G" & i + 3) = oWB.Sheets(1).Range("G2") + i
Next

[derekben]

Thanks!!! That saved me a lot of time. Have a great day!!