Exporting to Excel - Specific cell references

[robertmeyer1]

Hey,

I am working on creating a query which will export the information to excel. I have a specific excel document which has line by line items (corresponds to access query).

Here's the layout of the excel doc. Each row is a different line item. Each column is a different entity. So there are about 750 entities which will be exported to this excel doc. (1 entity per column).

For access, I have the same information as the excel doc will use, each is separated by entity. So each record is an entity.

How can I export the access query information to an excel doc so that it will make each record be inserted on a new column. That is, each record will not export to the same cell reference in excel, but instead 1 over.

So entity 1 is exported to C3-C100, entity 2 gets exported to D3-D100,....

Also I need to set it so 1 specific entity will be exported to cells B2-B100, the others can go in any order (no preference on my part).

Thanks for the help with this. It's also urgent that I figure out how to do this by the end of the week. Don't you love deadlines you find out about at the last minute. Thanks guys, I appreciate the help.

[NeoPa]

Nothing too simple then Robert 8-)

I can think of a way but it's very complex.
First step would be to get the data across in rows (You know, the normal way of doing it).
Hold on - Excel only has 256 columns so have you (or the project requestors) considered the difficulty of fitting 750 columns into 256?
Perhaps an answer to this one is called for before I continue.

[robertmeyer1]

Nothing too simple then Robert 8-)

I can think of a way but it's very complex.
First step would be to get the data across in rows (You know, the normal way of doing it).
Hold on - Excel only has 256 columns so have you (or the project requestors) considered the difficulty of fitting 750 columns into 256?
Perhaps an answer to this one is called for before I continue.

Well, unfortunatley the only thing I can think of doing will be to have multiple copies of the excel. (i.e. 1-250, 251-500, 501-750 or some such). This is a form (excel doc) that is mandated by regulations and can't be changed. So I'm not allowed to alter this documents design.

Could I export it to a different excel file, which is based off rows and then link the cells to the proper excel file. I know this is a 2 step (2 excel files) idea, but I haven't been able to think of anything else.

If the fact that I can't change the excel layout doesn't affect your idea please continue. Also, thanks for responding so quickly to this message.

[NeoPa]

I think that multiple exports to the same Excel file, using different QueryDef names will result in multiple Worksheets being created.
However, I suspect we won't need that here.
Firstly a warning.
This won't be easy, and I won't be able to do it all for you so you need to have some determination to take it on.
Basic Steps
1. Export the 100 or so fields of the 750 or so records to an Excel Workbook.
This should fit ok at this point.
2. Using the Excel library, open the Excel application and do a Copy / Paste Special using the Transpose option. Code to do things in Excel is easy to find as you can get the Macro Recorder to create code from your operator actions.
You won't be able to post it directly into one worksheet though. This is where you will need to create multiple worksheets to handle the records in Transposed orientation.

If/when you are happy with this we can look at organising the SQL of the QueryDef to ensure a particular entry comes out first.

[NeoPa]

BTW The multiple worksheet idea was also what I was thinking about using assuming that were acceptable to you.

[robertmeyer1]

First off, thanks. I don’t do many complex actions in excel and didn’t know about the transpose option. This part (export, copy, paste special, transpose) works well and I could finish it all up like this. However, for the purpose of not making mistakes or mixing something up accidentally, is it possible to automate the copy/paste/transpose in a predetermined manner?

That is, in access insert a macro/code which will run an export on the qry (qryReport05-162) to the same excel file each time? Then assuming the steps we follow for the excel-to-excel transfer works properly, it will update the 2nd excel book appropriately?

Also, is it possible to have access export this query in groups through code? That is, create a macro which when run will export (qryReport05-162) information records 1-250 to (exceldoc1) worksheet 1; 251-500 to (exceldoc1) worksheet2, ….. ? This way, each worksheet could be set to a specific copy of the final excel document.

I know I’m asking a lot here, so thanks once again for the assistance.

*If the excel automation cannot be set up, it would still help a lot to have the access export set up properly. Thanks again.

[NeoPa]

I was thinking of doing it all in code. Too much to do here for you though I'm afraid.
There shouldn't need to be separate exports though, as the code would work out dynamically which data should be copied and pasted to which worksheet, on a basis of 250 records per worksheet.

As I say, we can help you with particular parts of this if/when you get stuck. Doing the whole job is way to large in scope and far too complicated to do by 'remote control'.

[robertmeyer1]

As I say, we can help you with particular parts of this if/when you get stuck. Doing the whole job is way to large in scope and far too complicated to do by 'remote control'.

Alright. I'll keep ya'll posted on how its going and if i need any additional advice.

[NeoPa]

If it helps any, though this will probably be a large endeavour, I don't forsee any show-stoppers here. It should all be possible.
Good Luck.

[robertmeyer1]

Hey again. I have it mostly set up. WHat I need to do now is make it so the access program will export to the correct excel file. I created a macro to do so:

McrOutput: (action - OutputTo)

object type: query
object name: qryoutput
obj format: ms excel 97-03
output file: Export Document 1.xls

Now, instead of outputting to this file, it creates a new file named the same as the output file iin the my documents folder. How can I change the destination from my docs to another (using a drive folder) and how can I make it so it inserts a new sheet in the original file Export Document 1 instead of trying to create a new workbook? Thanks.