使用VBA -dynamic列从Excel复制到Access [英] Copy from Excel into Access using VBA -dynamic columns

问题描述

我有一个Access数据库，用户应该在其中从Excel复制并导入到Access表中.用户可以从多个导入"中选择，他们可以自己创建新的导入.一旦他们选择了导入-便会动态创建一个查询，其中包含要导入到表中的必需列.所有导入将填充同一表，但并非所有列都与每个导入相关.因此，如果选择了Import1，则查询将显示例如. column2，column3和column4.如果选择了import2，则可以是column2和column5等.此后，导入查询将在VBA代码中删除.

I have an access database where users are supposed to copy from Excel and import into an Access table. The users can select from several "imports", and they can create new imports themselves. Once they have selected an import - a query is created on the fly with the required columns to be imported into the table. All imports will populate the same table, but not all columns are relevant for each import. So if Import1 is selected, then the query will display eg. column2, column3 and column4. If import2 is selected, then it could be column2 and column5 etc. Afterwards the import query is then deleted in the VBA code.

我不确定这是否是解决该问题的最佳方法，但是原则上它是可行的(但是，如果用户在完成操作之前退出数据库，有时删除查询可能会遇到问题-因此可能需要手动删除旧查询).

I am not sure this is the best solution to the problem, but in principle it works (however sometimes there may be problems deleting the queries if users exit the database before completing etc- so some manual removal of old queries may be required).

但是，问题是，除了用户复制到表中的数据外，我还需要用VBA代码中设置的预定义值来完成一些列.例如，我需要一个ImportID列，用于存储用户为用户复制的每一行选择的导入的ID.当用户将数据从Excel粘贴到查询中时，这需要同时完成.否则，我将无法根据需要从其他导入中识别表中的数据.

The problem is however that I need some columns to be completed with a predefined value set in the VBA code in addition to the data copied into the table by the user. An example of this could be that I need an ImportID column storing the ID of the import the user has selected for each row copied by the user. This needs to be done simultaneously as the user pastes data from Excel into the query. Otherwise I will not be able to identify the data in the table from other imports as required.

对此，我唯一的解决方案是动态创建一个表单(针对请求的每个导入)，在表单中包括ImportID列，通过VBA为Import列设置预定义值，锁定导入列，然后将其隐藏的形式.这样，当用户从Excel中粘贴数据时-ImportID列也将填充正确的值-无需用户对此进行关联.

My only solution to this is to dynamically create a form (for each import that is requested), include the ImportID column in the form, set a predefined value for the Import column through VBA, lock the import column and then hide it in the form. That way, when the user pastes the data from Excel - the ImportID column is also populated with the correct value - without the user having to relate to that.

问题是我担心这是一个混乱"的解决方案，需要通过VBA在数据库中创建多个查询和表单，这些查询和表单需要删除-我确信其中有很多查询和表单会保留下来，因为用户将在此过程中退出完成等之前的过程.

The issue is that I am worried this is a "messy" solution, creating multiple queries and forms in the database through VBA that need to be deleted - and I am sure a lot of them will be left as users will exit during the process before completing etc.

如果有人对此有更好的方法，将不胜感激.我确实需要通过复制/粘贴来完成导入(不读取文件，尽管这可能是一项附加操作-它不能替代当前解决方案).

If anyone has a better approach to this, it will be much appreciated. I do need the imports to be done through copy/paste however (not reading files, although that could be an addition - it can not replace the current solution).

我将尝试更详细地解释该过程的工作方式:首先，我们定义了一个会计科目表".假设帐户4000-4100是来自CRM系统的数据.帐户5000-5100是来自薪资系统的数据.然后，用户可以设置一个名为"CRM导入"的导入，要求使用帐户4000-4100完成第2列，第3列和第5列，并且需要使用账户5000填写一个名为薪水导入"的导入，要求第2列，第3列和第7列-5100.

I’ll try to explain how the process works in more detail: First of all we have defined a «chart of accounts». Where let’s say account 4000-4100 is data from a CRM system. Account 5000-5100 is data from a payroll system. The user may then set up one import called «CRM import» requiring column 2, column3 and and column5 to be completed with accounts 4000-4100, and one import called «Payroll import» requiring column2, column3 and column7 to be completed with accounts 5000-5100.

启动«CRM»导入时– VBA代码仅打开由VBA创建的查询(DoCmd.OpenQuery)，显示第2、3和5列.在Excel工作表中–用户具有相同格式的数据.因此，用户只需从Excel工作表中复制并粘贴到打开的Access查询中即可.同样，当用户启动工资单导入"时，VBA生成的查询中将显示第2、3和7列.用户还可以从该格式复制另一个Excel工作表.这些是高级用户"，它们不仅可以复制并粘贴到数据库中，还可以粘贴到数据库中.来自不同来源的所有数据都被复制到同一个访问表中，以便以后进行处理(因此查询仅显示同一表的相关列).对于这种解决方案，从Excel复制/粘贴是将这些数据导入Access的首选方法.

When launching the «CRM» import – the VBA code simply opens a query created by VBA (DoCmd.OpenQuery) displaying column 2,3 and 5. In the Excel sheet – the user has the data on the same format. So the user then simply copies from the Excel sheet and pastes into the open Access query. Similarly when the user launches the «Payroll import», column2,3 and 7 are displayed in the VBA generated query. The user has another Excel sheet on this format to copy from as well. These are «power users» copying and pasting into the database – not just any users. All the data from the different sources are copied into the same access table in order to be processed later (so the queries simply display the relevant columns of the same table). Copy/paste from Excel is the preferred method of getting these data into Access for this solution.

更新18日.行进: 我仍然遇到的问题是我不能为每个导入使用一个预定义的表单.可以有多个导入，用户可以通过界面删除，添加和修改导入.所以我的想法是，当用户从列表中选择一个导入时，将打开该表单(所有导入都使用一个表单).并且基于所选的导入，VBA代码选择应在子表单中显示哪些列.每次打开导入时，都需要检查哪两栏显示.

Update 18th. March: The issue I've still got is that I can not have one predefined form for each import. There can be multiple imports, imports may be deleted, added and modified by the user through the interface. So my idea is that when the user selects an import from a list, the form is opened (one form for all imports). And based on the import selected, the VBA code selects which columns should be displayed in the subform. Which columns two display, need to be checked each time an import is opened.

但是，如果我在VBA中执行此操作，两个用户是否可以同时打开具有两个不同导入并且显示不同列的同一表单(它们在同一访问文件中工作)?还是我需要为每个用户创建表单的实例，然后在用户完成后删除该实例?这似乎不是最好的主意，但我不确定如何解决.

However if I do this in VBA, can two users open the same form at the same time (they work in the same access file) with two different imports and have different columns displayed ? Or do I need to create an instance of the form for each user, and then delete that instance when the user is done ? That doesn't seem like the best idea, but I'm not sure how to solve this.

推荐答案

如果我理解此设置并且用户正确遵循了流程，则您将拥有一个主表来存储用户将从CRM和工资单中复制和粘贴的所有各种记录电子表格.例如这样的表:

If I understand the setup and the process users are following correctly, you have a master table to store all the various records users will copy and paste from CRM and payroll spreadsheet. For example a table like this:

然后，您将获得单独的CRM和薪资查询，这些查询仅显示了与导入表不同的一组列:

You've then got separate CRM and Payroll queries, that simply show a different set of columns from the import table:

然后您已经建立了一些表单，该表单允许用户打开正确的查询:

You've then got some form set up, which allows users to open the correct query:

.. e.g. qryCRM:

..e.g. qryCRM:

..并从相关的Excel电子表格中复制/粘贴数据:

..and copy/paste data from the relevant Excel spreadsheet:

据我所知，我想您将很难捕获与查询对象之间的任何用户交互，就查询对象而言，没有太多事件可以触发VBA代码.

I think you are going to have trouble trying to catch any user interaction with a query object as query objects, as far as I know, don't have many events to trigger VBA code from.

我建议做的是为您的CRM，工资单等每个导入设置一个带有子表单的表单.

What I'd suggest doing is setting up a form with a subform for each of your CRM, payroll, etc imports.

然后，子表单可以将您的CRM和薪资查询作为记录源.例如，这是一个使用查询 qryCRM 作为记录源的子表单:

The subform can then take your CRM and Payroll queries as a recordsource. For example, here's a subform using the query qryCRM as a recordsource:

...，如果您还将子窗体设置为数据表"的默认视图，则也是如此.

...and if you also set the subform to a default view of "Datasheet"..

...用户将能够以与查询对象本身类似的电子表格样式与子表单进行交互:

...users will be able to interact with the subform in a similar spreadsheet-style of the query object itself:

然后您可以将导航表单设置为指向该表单而不是查询:

You can then set up your navigation form to point to the form rather than query:

Private Sub cmdImportCrm_Click()

    DoCmd.OpenForm "frmCRM"

End Sub

现在，当用户粘贴新记录时，您可以利用子窗体上的一系列事件将其他数据添加到数据集中.

You can now utilise a range of events on the subform to get additional data in to the dataset when the user pastes new records.

到目前为止，我发现的最好方法是使用子窗体的BeforeInsert事件:

The best way I've found so far is to use the subform's BeforeInsert event:

每次将新记录添加到数据集时都会触发此事件，因此，如果粘贴5个新行，则该事件应针对这5行中的每行触发一次.

This event is triggered each time a new record is added to the dataset, so if you paste 5 new rows, the event should trigger for each of those 5 rows.

对于这个事件，我只是简单地指定我希望 ImportID 列获取文本"CRM Import"(显然，您可以指定其他内容):

For this event I simply specified that I wanted the ImportID column to get the text "CRM Import" (you can obviously specify something else):

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.ImportID = "CRM Import"

End Sub

因此，当我们将数据粘贴为新记录时，我们也将获得为 ImportID 列指定的文本:

So when we paste our data as new records, we also get the text we specified for the ImportID column as well:

希望这会有所帮助:)

这篇关于使用VBA -dynamic列从Excel复制到Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！