Export to excel and "skip" a column?

[rglman]

Does anyone know if it is possible to export the results of an access query into an excel worksheet, but have the output data skip one of the columns on the excel sheet? Put another way:

Access table has columns "A", "B" & "C"
Excel worksheet has columns "A", "B", "C" & "D"

I want to export "A" to "A"
"B" to "B"
"C" to "D"

Skipping over Excel column "C" which contains a formula that cannot be erased or over-written, and the "owner" of the excel worksheet cannot (or more appropriately "is not willing to" ) rearrange the order of the columns.

Any ideas how this could be done?

Thanks in advance!

 

[rglman]

On occasions the solutions are so simple...

Oh Duhh! Sometimes the solutions are so simple, and we miss the simplest solutions while trying to find a more complex approach:

This can be done by simply creating two queries, one for column "A" & "B" and one for column "C". Then, using the DoCmd.TransferSpreadsheet function twice transfer the data from query 1 then transfer the data from query 2 and simply specify the appropriate cell ranges.

That said, if one of you knows a "more elegant" vba solution please let me know!

Thanks!

 

[DanG]

How about..
make a table with only 1 record (no value in it) and make a query with the new table (for "C") and the table with the other two columns (A and B) and join the tables.
Then you will have all three columns in 1 query and "C" will be empty and you will be able to place it in any position you like.

 

[rglman]

OLE Automation...hmmm

Can you refer me to a good reference source so I might get a crash course in OLE automation as I don't have any experience with it?

Thanks!

 

[rglman]

Thanks!

I'll check it out!