导出到Excel - 特定单元格引用 [英] Exporting to Excel - Specific cell references

问题描述

嘿，


我正在创建一个将信息导出到excel的查询。我有一个特定的Excel文档，其中包含逐行项（对应于访问查询）。


这里是excel doc的布局。每行都是不同的订单项。每列是不同的实体。因此，大约有750个实体将导出到此excel文档。 （每列1个实体）。


对于访问，我有与excel doc将使用的信息相同的信息，每个信息由实体分隔。因此，每条记录都是一个实体。


如何将访问查询信息导出到Excel文档，以便将每条记录插入到新列中。也就是说，每个记录都不会导出到excel中的相同单元格引用，而是1以上。


因此实体1导出到C3-C100，实体2导出到D3- D100，....

另外我需要设置它，这样一个特定的实体将被导出到单元格B2-B100，其他的可以按任何顺序进行（我没有偏好） ）。


感谢您的帮助。我也很紧急，我想知道如何在本周末完成这项工作。你不喜欢在最后一分钟发现的截止日期。谢谢大家，我很感激帮助。

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.

推荐答案

没有什么比Robert 8更简单了 -


我能想到一个方式，但它非常复杂。

第一步是将数据划分为行（你知道，正常的做法）。

坚持 - Excel只有256列，所以你（或项目请求者）认为将750列拟合成256列的难度是多少？

也许在我继续之前要求回答这一列。

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.

没有什么比Robert 8更简单了 - ）


我能想到一个方法，但它是'非常复杂。

第一步是将数据划分为行（你知道，正常的做法）。

等待 - Excel只有256列那么你（或项目请求者）是否考虑过将750列装入256列的难度？

也许在我之前要求回答这一列ontinue。

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.

好​​吧，不幸的是，我唯一想到的就是拥有excel的多个副本。 （即1-250,251-500,501-750或其他一些）。这是一个由法规强制要求的表格（excel doc），不能更改。所以我不允许改变这个文件设计。


我可以将它导出到不同的excel文件，该文件基于行，然后将单元格链接到正确的excel文件。我知道这是一个2步（2个excel文件）的想法，但我还没有想到其他任何东西。


如果我不能改变excel布局的事实不会影响你的想法请继续。另外，感谢您对此消息做出如此快速的响应。

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.

我认为使用不同的QueryDef名称多次导出到同一Excel文件将导致创建多个工作表。

但是，我怀疑我们在这里不需要它。

首先是警告。

这不容易，我不会能够为你做这一切所以你需要有一些决心去接受它。

基本步骤

1.输出750或者100的左右字段所以记录到Excel工作簿。

这个应该适合这一点。

2.使用Excel库打开Excel应用程序并使用复制/粘贴特殊功能转置选项。在Excel中执行操作的代码很容易找到，因为您可以通过宏记录器从操作员操作中创建代码。

您无法将其直接发布到一个工作表中。这是您需要创建多个工作表来处理转置方向的记录的地方。


如果/当您对此感到满意时，我们可以考虑组织QueryDef的SQL以确保首先出现一个特定的条目。

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.

这篇关于导出到Excel - 特定单元格引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！