Filling an excel worksheet from a recordset

[MarionD]

Hi there all,

I am trying to fill an excel sheet from an access recordset, using this code

newdata.MoveFirst
With xlapp
Do Until newdata.EOF
x = x + 1
For y = 1 To 22 ' the recordset has 22 fields
.Worksheets(1).Cells(x, y) = newdata.Fields.Value
Next y

newdata.MoveNext
Loop
it works fine EXCEPT it ignores the first field in the recordset....field one should go to cell 1, field 2 to cell 2 etc. It seems to move all the cells back one so that field 2 in in cell 1. If I start with y=0 an error occurs.

Any ideas on how to solve this would be much appreciated!

 

[pbaldy]

For the recordset, use

y-1

The recordset fields collection is zero based, but the Excel column is not.

 

[MarionD]

Perfect! Thanks a ton!

 

[pbaldy]

Happy to help!

 

[MarionD]

another small Problem...
When I try to save the Excel sheet with xlApp.save..... I get the attached message-- If I click OK it saves perfectly....

 

[pbaldy]

There's no attached message.

 

[MarionD]

sorry forgot to upload

 

[pbaldy]

My German isn't very good...okay, it's non-existent. Is that about the file existing, and do you want to replace? If so, I'd just delete it first.

 

[MarionD]

Sorry- I'm so used to thinking in 2 languages... It saya a file with the name Resume.xlw is already there. Do I want to replace it. But my file is calles something completely different.. it also an xlsx... no idea what an .xlw is.

 

[rpeare]

You have to terminate your excel connection after the file is created otherwise you're going to encounter this problem repeatedly. If you ran this code multiple times then went to look at your task manager you'd see a bunch of instances of excel open. So improperly closing the excel file after it's creation may be giving you this result.

 

[MarionD]

Thanks again!

 

[Galaxiom]

I am trying to fill an excel sheet from an access recordset,

Any reason why you don't use the CopyFromRecordset Method of the Worksheet?

 

[MarionD]

Hi there! Only reason is I don't know how! Will Google it and see how it works. Thanks for the tip... this is the first time I've had to do this!
Or do you have an example ?

 

[Galaxiom]

You will find easily with Google.

One thing though. CopyToRecordset ignores any filters and always copies the whole recordset.

 

[MarionD]

Thanks again for the tip!
You live and learn!

 

[pbaldy]

Good thought Galaxiom, that's what I'd use. Sometimes I get too focused on fixing the code that's posted, rather than suggesting the better way. :banghead: