Arrays from Access to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2003 on WindowsXP SP2.
I have retrieved data using the getrows method in access, and want to put
this data into a temperary excel workbook/spreadsheet object to display
and/or print. I thought that it would be as simple as setting a range equal
to the array, the opposite of setting an arrayequal to a range. Only problem
with this, is that Microsoft in all their wisdom(a bit of sarcasm :p)
reversed the index of the arrays from access to excel. In access, the field
index is first but in excel the row index is first. This make my data table
hard to read. Is there a simple way (without having to step through the array
and reverse the indexes) to do this?
Thanks in advance
 
I am not sure why you want to use an array to move the data to excel. Are
you using automation? If not, what method are you using to load the data into
Excel?
If you are using automation, then you may want to use the Excel method
CopyFromRecordset. If you are wanting a specific number of rows, then you
could use an SQL call to get the number of records you want into a recordset,
then use the CopyFromRecordset to pull it into Excel:
SELECT TOP nn FROM... (Where nn is the number of records you want)
 
I guess because I wasn't aware of the copyfromrecordset method. I had already
reworked it to flip the indexes, but of course your way is much more
efficient so I took out all the other lines, and added the copyfromrecordset
method. Works great!
Thank you!
 
Happy to help!
You should see some of the hoops I jumped through before I stumbled across
copyfromrecordset.
 
Back
Top