473,569 Members | 2,604 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Exporting to Excel - Specific cell references

99 New Member

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 10043
32,564 Recognized Expert Moderator MVP
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
99 New Member
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
32,564 Recognized Expert Moderator MVP
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
32,564 Recognized Expert Moderator MVP
BTW The multiple worksheet idea was also what I was thinking about using assuming that were acceptable to you.
Jan 9 '07 #5
99 New Member
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
32,564 Recognized Expert Moderator MVP
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
99 New Member
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
32,564 Recognized Expert Moderator MVP
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
99 New Member
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

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

Similar topics

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 urgent to us. i have a sample code which works only exporting to single worksheet. but i need to export data to multiple worksheets. it is very urgent to us. so please help me in code.
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 one problem: one of the fields is a "SchoolYear" TEXT field that contains data such as 2000/01, 2001/02, etc. If I export a Query with this kind of data to Excel, it gives me the text value of...
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 cell in Excel. I have been able to figure out how to open and modify cells in my spreadsheet, but I don't understand how to select elements of the Access table to export it to Excel. Here is what I...
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 formula. I can run a formula and insert the formula value into a cell. BUT this is where it comes "complex".
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 = "application/vnd.ms-excel" Which works fine however the problem is that when I have a number in a column with a leading zero the zero gets dropped.
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 SQL server into a specific cell of the excel file. Is this doable? Can somebody give me some direction on how to accomplish this? I appreciate any suggestions.
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 =__T() I am not able to set a value to a given cell. region.Value2="qwe"; //bumm! A exception will be trown. On all PCs with excel. HRESULT 0x800A03EC
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 Marshal.ReleaseComObject(clsExcel); But when i go and see in the TaskManager --Processes Tab its still having that Excel.exe. If i opened more than one excel sheet and do these operations then more than one...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.