VBA loop to write data to an Excel spreasheet on different lines 1

[eddy556]

Hi, what I am trying to code is a loop in VBA which will go through a recordset and write each record onto a different line within excel. For example the first record will be written into cells B9, C9, D9, E9, then the next record goes to B10, C10, D10, E10 etc. However I get error 424 - object required....not sure how to fix this

Heres the code:

Set oApp = CreateObject(Class:="Excel.Application")

'Open the spreadsheet Cost_Updater.xls
oApp.Workbooks.Open "C:\Documents and Settings\Admin\Desktop\BSADCAssignmen2_2006_7\samedayInvoice.xls"

'Make sure the Spreadsheet can be seen
oApp.Visible = True


recdeliveries.MoveFirst


Set oApp = CreateObject(Class:="Excel.Application")

'Open the spreadsheet Cost_Updater.xls
oApp.Workbooks.Open "C:\Documents and Settings\Admin\Desktop\BSADCAssignmen2_2006_7\samedayInvoice.xls"


rows = 9

For c1 = 1 To noofrows
If recdeliveries("CustomerCode") = CustomerCode Then


'Make sure the Spreadsheet can be seen
oApp.Visible = True


'Move initial cost to the procedure
oApp.Sheets("Sheet1").[B & rows].Value = recdeliveries(0)
oApp.Sheets("Sheet1").[C & rows].Value = recdeliveries(1)
oApp.Sheets("Sheet1").[D & rows].Value = recdeliveries(2)
oApp.Sheets("Sheet1").[E & rows].Value = recdeliveries(3)
oApp.Sheets("Sheet1").[F & rows].Value = recdeliveries(4)
oApp.Sheets("Sheet1").[G & rows].Value = recdeliveries(5)


'Move updated Unit Cost back to the database
'UnitCost.Value = oApp.Sheets("Cost_Updater").[c4].Value

rows = rows + 1

Else

MsgBox "This customer has not made any deliveries this month"

End If
recdeliveries.MoveNext
Next

recdeliveries.Close
db.Close


Thanks for your help

 

[PHV]

Why instantiate oApp 2 times ?
Replace this:
oApp.Sheets("Sheet1").[B & rows].Value = recdeliveries(0)
with this:
oApp.Sheets("Sheet1").Range("B" & rows).Value = recdeliveries(0)
and similarly for other lines.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eddy556]

Hi, thanks! Works great!

Lol I was also wondering why it was opening Excel twice! Didn't realise I had dublicated the code

Thanks again