TranferSpreadsheet question 1

[robojeff]

I am attempting ot use the TranferSpreadsheet method of transfering a table to an excel spreadsheet but I am a little bit confused...

I would like to place the contents of my table into a seperate worksheet.

I would like to name the worksheet by the data within the Assembly field of my table (which is the same for all records in that table)

My Table consists of 4 fields:
==============================
Assembly - text
Item - text
Description - text
Quantity - number
===============================

I am using the following command:
--------------------------------------------
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ strTemp, "C:\FolderName\" & strFileName & ".xls"
--------------------------------------------
Where strTemp = the name of my table.

How do I specify a new worksheet and the name of that worksheet to match the value of Assembly in my table?

Thanks

 

[SkipVought]

Hi,

Just curious...

Do you have a separate table in your Access database, for each assembly?

I do something with similar results in Excel, using MS Query, only I'm tapping a table that has ALL my assemblies, subassemblies and details in it. I use a dropdown box to select the PARENT part number and that triggers a parameter query that returns the CHILD part numbers for that PARENT...click and you have a new part list.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robojeff]

Actually, I am pulling a large list of certain upper level assembly part numbers off of the Planning BOMs of our ERP system.

Then I am attempting to pull all of the BOMs of these items for use into seperate worksheets in an excel report but I am having a problem getting the BOMS over to Excel.

I create a table witha query containing all of the upper level assemblies and I have another query which pulls the BOM from each of the upper level assemblies.

the second query creates a table with the BOM which I want to export to excel.

 

[SkipVought]

Why not query the BOMs from your ERP, directly in Excel? If you can query in Access, you can also in Excel, and skip the unnecessary step.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robojeff]

Our IT guy has the means to do excel dujmps to excel but I do not want to bother the IT guy every time that someone wants an updated copy of this info as he is too busy to be doing this sort of thing...

I am attempting to make this easy for anyone to have access to when they need the info...

Any ideas how to use the Transferspreadsheet method?

 

[SkipVought]

What kind of database is the BOM in, in your ERP system?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robojeff]

The ERP system is a BPCS ODBC type and my query & table are created in Access 2003

 

[SkipVought]

You can also do the query to BPCS directly from Excel, using the same ODBC driver and an almost identical query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robojeff]

SkipVought wrote:

You can also do the query to BPCS directly from Excel, using the same ODBC driver and an almost identical query.

Thanks SkipVought but I am really only interested in exporting an access 2003 table into specific worksheet within an excel spreadsheet...

 

[SkipVought]

Sorry, I thought you might be interested in saving an unecessary step in the process.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

something like this...

Assy = "A12345-001"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourAccessTable", "C:\Assemplies.xls", True, Assy & "$"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robojeff]

Thanks Skip!

That was the info that I was looking for and it works great...