Transfer Table to Existing Excel Worksheet

[hmiller]

Hey there folks:

I have been trying to get this work for about a week now. I'm new to
VBA...

I am trying to transfer a populated table in Access to an existing, but
blank, Excel worksheet. I have seen methods for transfering cell by
cell, and understand how to get that to work. However, my recordset has
3600 cells of data. Cell by cell is just not an option. I am looking
for a way to specify the starting cell (most upper left cell) in Excel,
and then just drop the Access table into the worksheet.

The table is 36 columns wide and 100 rows tall.

Both the Access table and the existing Excel worksheet have the exact
same formatting. It's just a matter of effieciently transfering this
large amount of data.

Thanks for your help...

[Kc-Mass]

This will transfer the entire table to the Excel File you name. You don't
need to create
the recipient file. This will create it for you. It will also set the first
row as column names
which it will take from the table fieldnames.

In a VBA Module within the database that has the table of interest enter

DoCmd.TransferS preadsheet acExport, 8, "TableDataComin gFrom",
"PathAndNameFor ExcelFile" -1
acExport means it is outbound
8 means that it is Access 97 or later
-1 means use fieldNames as column titles

Example
DoCmd.TransferS preadsheet acExport, 8, "tblClients ",
"c:\ExcelWbs\Cl ientWB.xls", -1
<hm*****@hartfo rd.eduwrote in message
news:11******** **************@ k70g2000cwa.goo glegroups.com.. .

Hey there folks:

I have been trying to get this work for about a week now. I'm new to
VBA...

I am trying to transfer a populated table in Access to an existing, but
blank, Excel worksheet. I have seen methods for transfering cell by
cell, and understand how to get that to work. However, my recordset has
3600 cells of data. Cell by cell is just not an option. I am looking
for a way to specify the starting cell (most upper left cell) in Excel,
and then just drop the Access table into the worksheet.

The table is 36 columns wide and 100 rows tall.

Both the Access table and the existing Excel worksheet have the exact
same formatting. It's just a matter of effieciently transfering this
large amount of data.

Thanks for your help...

[Kc-Mass]

Just to be clear the DoCmd lines below have been broken by the message
software.
The line immediately under each DoCmd line has to be part of the DoCmd line
"Kc-Mass" <co********@com cast.netwrote in message
news:ac******** *************** *******@comcast .com...

This will transfer the entire table to the Excel File you name. You don't
need to create
the recipient file. This will create it for you. It will also set the
first row as column names
which it will take from the table fieldnames.

In a VBA Module within the database that has the table of interest enter

DoCmd.TransferS preadsheet acExport, 8, "TableDataComin gFrom",
"PathAndNameFor ExcelFile" -1
acExport means it is outbound
8 means that it is Access 97 or later
-1 means use fieldNames as column titles

Example
DoCmd.TransferS preadsheet acExport, 8, "tblClients ",
"c:\ExcelWbs\Cl ientWB.xls", -1
<hm*****@hartfo rd.eduwrote in message
news:11******** **************@ k70g2000cwa.goo glegroups.com.. .

>Hey there folks:

I have been trying to get this work for about a week now. I'm new to
VBA...

I am trying to transfer a populated table in Access to an existing, but
blank, Excel worksheet. I have seen methods for transfering cell by
cell, and understand how to get that to work. However, my recordset has
3600 cells of data. Cell by cell is just not an option. I am looking
for a way to specify the starting cell (most upper left cell) in Excel,
and then just drop the Access table into the worksheet.

The table is 36 columns wide and 100 rows tall.

Both the Access table and the existing Excel worksheet have the exact
same formatting. It's just a matter of effieciently transfering this
large amount of data.

Thanks for your help...

[hmiller]

I'm sorry I should have been more clear.

I can't create a new worksheet in the existing Excel workbook.

I need to export the Access table to a specific area in the existing
Excel worksheet so that the rest of the workbook can make INDIRECT
references to the specific cells.

I inherited the Excel workbook and the orginal creator took the time to
create all of the INDIRECT references. I don't want to have to go
through the entire workbook and redirect all of the INDIRECT references
to the new worksheets.

If I can drop the Access table directly into the existing Excel
worksheet then I don't need to recode/formulate the Excel workbook.

I was thinking an alternative to my problem might be to reference the
newly imported worksheets, from the DoCmd, as an array. But I don't
know how to refference an entire worksheet in Excel without setting a
range.

I want my final program to be hands off as much as possible. Just open
Access, see a MsgBox pop-up "All Set", and that's it.

[Kc-Mass]

Maybe your coming at this from the wrong side. Excel has a built in query
system (MS_Query). It is not part of the default install but is on the cd.

Go to Data:GetExterna l:New Database Query

It will walk you through building the query against an Access DB. Save the
query.
Next time you want the data go run the saved query. It will load data to
the sheet and position of your choosing.

Bury it in a macro if you like.

<hm*****@hartfo rd.eduwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .

I'm sorry I should have been more clear.

I can't create a new worksheet in the existing Excel workbook.

I need to export the Access table to a specific area in the existing
Excel worksheet so that the rest of the workbook can make INDIRECT
references to the specific cells.

I inherited the Excel workbook and the orginal creator took the time to
create all of the INDIRECT references. I don't want to have to go
through the entire workbook and redirect all of the INDIRECT references
to the new worksheets.

If I can drop the Access table directly into the existing Excel
worksheet then I don't need to recode/formulate the Excel workbook.

I was thinking an alternative to my problem might be to reference the
newly imported worksheets, from the DoCmd, as an array. But I don't
know how to refference an entire worksheet in Excel without setting a
range.

I want my final program to be hands off as much as possible. Just open
Access, see a MsgBox pop-up "All Set", and that's it.

[Bob Quintal]

"hm*****@hartfo rd.edu" <hm*****@hartfo rd.eduwrote in
news:11******** **************@ m73g2000cwd.goo glegroups.com:

I'm sorry I should have been more clear.

I can't create a new worksheet in the existing Excel workbook.

I doubt that.

>
I need to export the Access table to a specific area in the
existing Excel worksheet so that the rest of the workbook can
make INDIRECT references to the specific cells.

Maybe you could transfer the data to the new worksheet, then run a
macro to cut the data from the new sheet and paste it into the
final destination, then delete the sheet added above.

All is easy enough to do in code.

>

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com