Copy from Excel into Access using VBA -dynamic columns

Question

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).

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.

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.

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).

---

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.

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.

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.

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.

Answer 1

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:

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:

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

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.

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

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.

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

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.

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

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

Hope this helps :)