Filling an excel worksheet from a recordset (1 Viewer)

M

MarionD

Registered User.

Local time

Today, 22:32

Joined

Oct 10, 2000

Messages

421

• Sep 15, 2016

• #1

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

Wino Moderator

Staff member

Local time

Today, 14:32

Joined

Aug 30, 2003

Messages

36,125

• Sep 15, 2016

• #2

For the recordset, use

y-1

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

 

M

MarionD

Registered User.

Local time

Today, 22:32

Joined

Oct 10, 2000

Messages

421

• Sep 15, 2016

• #3

Perfect! Thanks a ton!

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 14:32

Joined

Aug 30, 2003

Messages

36,125

• Sep 15, 2016

• #4

Happy to help!

 

M

MarionD

Registered User.

Local time

Today, 22:32

Joined

Oct 10, 2000

Messages

421

• Sep 15, 2016

• #5

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

Wino Moderator

Staff member

Local time

Today, 14:32

Joined

Aug 30, 2003

Messages

36,125

• Sep 15, 2016

• #6

There's no attached message.

 

M

MarionD

Registered User.

Local time

Today, 22:32

Joined

Oct 10, 2000

Messages

421

• Sep 15, 2016

• #7

sorry forgot to upload

 

Attachments

• 
  Unbenannt1.JPG

  22.6 KB · Views: 67

pbaldy

Wino Moderator

Staff member

Local time

Today, 14:32

Joined

Aug 30, 2003

Messages

36,125

• Sep 15, 2016

• #8

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.

 

M

MarionD

Registered User.

Local time

Today, 22:32

Joined

Oct 10, 2000

Messages

421

• Sep 15, 2016

• #9

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.

 

R

rpeare

Registered User.

Local time

Today, 14:32

Joined

Sep 15, 2016

Messages

18

• Sep 15, 2016

• #10

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.

 

M

MarionD

Registered User.

Local time

Today, 22:32

Joined

Oct 10, 2000

Messages

421

• Sep 15, 2016

• #11

Thanks again!

 

G

Galaxiom

Super Moderator

Staff member

Local time

Tomorrow, 07:32

Joined

Jan 20, 2009

Messages

12,852

• Sep 15, 2016

• #12

MarionD said:

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

Click to expand...

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

 

M

MarionD

Registered User.

Local time

Today, 22:32

Joined

Oct 10, 2000

Messages

421

• Sep 16, 2016

• #13

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 ?

 

G

Galaxiom

Super Moderator

Staff member

Local time

Tomorrow, 07:32

Joined

Jan 20, 2009

Messages

12,852

• Sep 16, 2016

• #14

You will find easily with Google.

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

 

M

MarionD

Registered User.

Local time

Today, 22:32

Joined

Oct 10, 2000

Messages

421

• Sep 16, 2016

• #15

Thanks again for the tip!
You live and learn!

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 14:32

Joined

Aug 30, 2003

Messages

36,125

• Sep 16, 2016

• #16

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: