472,353 Members | 1,738 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

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 9898
NeoPa
32,511 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,511 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,511 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,511 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,511 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,511 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,511 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,511 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

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

Similar topics

3
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very...
4
by: D | last post by:
I've created a report with many subreports of aggregate data. I want my client to be able to export this data to Excel to make her charts, etc. Only...
1
by: setterst | last post by:
I am quite new to VBA and Access, but I am trying to figure out how to export specific values from a table in Access, so I can save it in a given...
3
by: Carlos Magalhaes | last post by:
Hey All, I am doing some excel automation using the excel COM. I can do most of the functions and its working well until I come across a...
6
by: Kevin Humphreys | last post by:
Hi There, I am trying to export a recordset to an excel file using the Content Type below in the header. Response.ContentType =...
8
by: Edgar | last post by:
Tools: SQL Server 2K, Excel 2000 Hi, I have an Excel report worksheet with formatted headings. What I want to do is to export data from the...
18
by: Frank M. Walter | last post by:
Hello, I have made an small AddIn with udf for excel 2003. I use vs2003. The point of view is the function __T() I call it in excel sheet writing...
2
by: B.N.Prabhu | last post by:
In my web application(C#) . I am adding one excel sheet and then save and closing that excel file. In the finally bloack i gave ...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.