469,336 Members | 5,570 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

Exporting to Excel - Specific cell references

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.
Jan 9 '07 #1
16 9660
NeoPa
32,182 Expert Mod 16PB
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.
Jan 9 '07 #2
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.
Jan 9 '07 #3
NeoPa
32,182 Expert Mod 16PB
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.
Jan 9 '07 #4
NeoPa
32,182 Expert Mod 16PB
BTW The multiple worksheet idea was also what I was thinking about using assuming that were acceptable to you.
Jan 9 '07 #5
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.
Jan 10 '07 #6
NeoPa
32,182 Expert Mod 16PB
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'.
Jan 10 '07 #7
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.
Jan 10 '07 #8
NeoPa
32,182 Expert Mod 16PB
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.
Jan 10 '07 #9
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.
Jan 10 '07 #10
NeoPa
32,182 Expert Mod 16PB
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.
Jan 11 '07 #11
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.
Jan 11 '07 #12
NeoPa
32,182 Expert Mod 16PB
No problem Robert. Good luck.
Jan 11 '07 #13
maxamis4
295 Expert 100+
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


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTransferDataToExcel_Click()
  2. On Error Resume Next
  3.  
  4.     Dim db As Database
  5.     Dim rs As Recordset
  6.     Dim rst As Recordset
  7.     Dim rst2 As Recordset
  8.     Dim rst3 As Recordset
  9.     Dim rst4 As Recordset
  10.     Dim rst5 As Recordset
  11.  
  12.  
  13.    ' Excel built in Functions 
  14.     Dim objApp As Excel.Application
  15.     Dim objBook As Excel.Workbook
  16.     Dim objSheet As Excel.Worksheet
  17.   ' End Excel
  18.  
  19.  
  20.     Dim sSql As String
  21.  ' I used this to define the path where the file resides 
  22.     Dim Path As String
  23.  
  24.  
  25.  
  26.     Dim myBook As String ' This equals the name of the spread sheet that I want to export too
  27.  
  28.     myBook = cboDatabases
  29.  
  30.  
  31.      Set db = CurrentDb()
  32.      Set objBook = Workbooks.Add(Template:=CurrentProject.Path & "\HCDEUCTemplate1.xlt")  'Your excel spreadsheet file goes here
  33.  
  34.                 Set objApp = objBook.Parent
  35.                     'Set objSheet = objBook.Worksheets("'" & cboDatabases & "'") 'Name of sheet you want to export to
  36.  
  37.                      If Me.cboDatabases = "all" Then
  38.  
  39.                         Set rs = db.OpenRecordset("select * [YOUR DATABASE]")
  40.                         Do While Not rs.EOF
  41.                             myBook = rs!DatabaseName
  42.                             Set objSheet = objBook.Worksheets(myBook) 'Name of sheet you want to export to
  43.                             objBook.Windows(1).Visible = True
  44.                             Set rst = db.OpenRecordset("[YOU SQL]")
  45.                                                                                      With objSheet
  46.                                     .Select
  47.                                         .Range("a3").CopyFromRecordset rst ' This will copy exactly as the query by starting
  48.  
  49.  
  50.  
  51.                                     End With
  52.                                     objApp.Visible = True
  53.  
  54.                             rs.MoveNext
  55.  
  56.                         Loop
  57.  
  58.     Set db = Nothing
  59.     Set objSheet = Nothing
  60.     Set objBook = Nothing
  61.     Set objApp = Nothing
  62.  
  63. End Sub
  64.  
Jan 11 '07 #14
NeoPa
32,182 Expert Mod 16PB
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:
Jan 11 '07 #15
MMcCarthy
14,534 Expert Mod 8TB
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.
Jan 12 '07 #16
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.
Jan 16 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by setterst | last post: by
3 posts views Thread by Carlos Magalhaes | last post: by
6 posts views Thread by Kevin Humphreys | last post: by
18 posts views Thread by Frank M. Walter | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.