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.

[NeoPa]

I'm sorry - I can only help with VBA code - I never use macros - too much trouble - too little power/control.
I suppose you could try using the full path name rather than the filename alone. Just a guess.

[robertmeyer1]

I'm sorry - I can only help with VBA code - I never use macros - too much trouble - too little power/control.
I suppose you could try using the full path name rather than the filename alone. Just a guess.

That semi-works. It now exports it to the correct path, but it wants to replace my file instead of inserting a sheet. I'll play around and see if it works. THanks again.

[NeoPa]

No problem Robert. Good luck.

[maxamis4]

Here is what I use. Now I created a template, which will allow you to save ever new instance so use it as you will

Private Sub cmdTransferDataToExcel_Click()
On Error Resume Next
 
    Dim db As Database
    Dim rs As Recordset
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim rst3 As Recordset
    Dim rst4 As Recordset
    Dim rst5 As Recordset
 
 
   ' Excel built in Functions 
    Dim objApp As Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
  ' End Excel
 
 
    Dim sSql As String
 ' I used this to define the path where the file resides 
    Dim Path As String
 
 
 
    Dim myBook As String ' This equals the name of the spread sheet that I want to export too
 
    myBook = cboDatabases
 
 
     Set db = CurrentDb()
     Set objBook = Workbooks.Add(Template:=CurrentProject.Path & "\HCDEUCTemplate1.xlt")  'Your excel spreadsheet file goes here
 
                Set objApp = objBook.Parent
                    'Set objSheet = objBook.Worksheets("'" & cboDatabases & "'") 'Name of sheet you want to export to
 
                     If Me.cboDatabases = "all" Then
 
                        Set rs = db.OpenRecordset("select * [YOUR DATABASE]")
                        Do While Not rs.EOF
                            myBook = rs!DatabaseName
                            Set objSheet = objBook.Worksheets(myBook) 'Name of sheet you want to export to
                            objBook.Windows(1).Visible = True
                            Set rst = db.OpenRecordset("[YOU SQL]")
                                                                                     With objSheet
                                    .Select
                                        .Range("a3").CopyFromRecordset rst ' This will copy exactly as the query by starting
 
 
 
                                    End With
                                    objApp.Visible = True
 
                            rs.MoveNext
 
                        Loop
 
    Set db = Nothing
    Set objSheet = Nothing
    Set objBook = Nothing
    Set objApp = Nothing
 
End Sub
 

[NeoPa]

That semi-works. It now exports it to the correct path, but it wants to replace my file instead of inserting a sheet. I'll play around and see if it works. THanks again.

Just to check - you are using a differently named record source (Table or QueryDef) from the previous export. If not it will certainly try to overwrite.
I looked back over the thread to highlight where I'd mentioned this before - but found I hadn't :embarassed:

[MMcCarthy]

That semi-works. It now exports it to the correct path, but it wants to replace my file instead of inserting a sheet. I'll play around and see if it works. THanks again.

Robert

If you don't want it to replace the sheet each time you will have to find some way to rename it each time.

Mary

P.S. Sorry I didn't get back to you after christmas. I wasn't well and then got bogged down in my new Admin duties.

[robertmeyer1]

Robert

If you don't want it to replace the sheet each time you will have to find some way to rename it each time.

Mary

P.S. Sorry I didn't get back to you after christmas. I wasn't well and then got bogged down in my new Admin duties.

No problem Mary. I hope things are going better for you now. Let me know if you are going to have a chance to review the information I sent you. Please don't feel rushed on this though. Thanks.