Access 2010 open Excel and select columns to write to Access table

Hello Experts,

I am using Access 2010,
is that possible to let users to open a Excel file and then select what columns in the file and writing to current access tables ?
For example, I open Excel A and it contains 3 columns, I just select column 1 and 2 data to write in current Table A fields B & C.

If yes, could you please advise how to do that by using VBA in Access 2010 ?

Thanks a lot !

Thanks & Regards,
Who is Participating?
Rey Obrero (Capricorn1)Commented:
you can use this command to import columns A and B to a temp table

docmd.TransferSpreadsheet acImport,,"tempTable","C:\myExcel.xlsx",True,"Sheet1!A1:B500"

after the import,
create a query to append the records from the tempTable to Table A.

another option is to open excel in VBA and read columns A and B row by row and add the records to Table A ( your VBA must be good to tackle this option)
Eric ShermanAccountant/DeveloperCommented:
I would Range Name Column 1 and 2 inside your worksheet then using the Import Wizard in Access import that data into a new table in your database.  

Patrick3388Author Commented:
Thanks for the replies.

My boss wants to do that as possible as by using VBA.
He expects we can choose what columns in Excel file and current access table as well, and then do the mapping for copying Excel data to Access table.

I really have no cue on such VBA codes...
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
<He expects we can choose what columns in Excel file and current access table as well, and then do the mapping for copying Excel data to Access table.>

Sorry Patrick, but you can tell your boss to hire an experience developer with extensive VBA knowledge to do the project.
Eric ShermanAccountant/DeveloperCommented:
As Rey commented ... Sounds like a project you will need to discuss with an experienced developer to handle for you.  There are a lot of unknowns as to what you are trying to accomplish and how you see this all working at the push of a button using VBA.  It's a full project and developers generally bill clients to do this level of development.  Here on EE, we answer specific questions you may have regarding Access and VBA.  If you are interested you can use the Hire Me option under any of our profiles to make contact regarding your project.

We also have a concept issue.  In an application based on a relational database, you wouldn't be creating tables on the fly.  Users should never see anything except forms and reports.  To do this, you would need to open up the application and allow them design view to create and modify objects.  This will not work at all in a shared database because Access doesn't allow multiple people to have the database open if someone is making design changes.  Not to mention the fact that data belongs in the BE where it is shared and so that is actually what they would need to be modifying to create new tables.

Please tell us more about your objective and we'll help to find a solution.
Patrick3388Author Commented:
My objective as below.

1.) Open Excel file as recordset
2.) Select what data to be copied (or imported) to existing access table

Thanks a lot !

I tried to find out some codes but not sure how to open Excel file as recordset and browse the field ?
Use DoCmd.TransferSpreadsheet..... to LINK to the spreadsheet.  Then create an append query that selects whatever columns and rows you need from the linked spreadsheet and appends them to the permanent table.
Patrick3388Author Commented:
Hello PatHartman,

Appreciated if you can have some sample codes for reference, thanks a lot !

Thanks & Regards,
You are going to need to make a little effort.

1. Add a button to a form.
2. Click on the builder button next to the click event.  If given the option, choose the one that creates code.  Access will create the click procedure header and place the insertion point into it.
3. Type DoCmd.TransferSpreadsheet     That will give you intellisense to help you fill in the rest of the pieces.
4. Once the spreadsheet is linked, then open the query builder.
5. Select the linked spreadsheet.
6. Select the columns you want to append to the permanent table.
7. Change the query type to Append.
8. Choose the name of the permanent table you want to append to.
Patrick3388Author Commented:
Hello experts,

Now I can select the Excel file (*.xls) and load it to listbox (attached screenshot).
What I am going to do is letting users to select what columns to be imported to Access table.
For example, we can select Field1 and Field2 only to import to table A Field3 and Field4

BTW, I am thinking to have defined relationship Access table to define the field mapping.
Search for ListBox column name and find its destination, and then import the data.

Is that possible to do that ?
Any help ?
Are you creating a new table each time or are you appending to the same table?  Is the data staying in Access or is this simply a work table?  If the data is not staying in Access, have you considered linking to the spreadsheet and using a query to select the columns you want?  If the data isn't permanent, then linking is more efficient.

Is this database used by multiple people?  Have you considered the ramifications of having users create their own tables?  Are you planning on doing this in the FE or in the BE?  

A simple way to present a list of columns for selection is to link to the spreadsheet.  Then you can set the RowSourceType of a listbox to the name of the linked table.  Make the listbox multi-select and write code to collect the selections and create a query.

It is really easy for someone to copy-and-paste cells from Excel into a table or, better yet, a Datasheet Form in Access.

1.  Create a single generic table in Access that has more columns than any user would need.
2. Use the Form Wizard to create a Datasheet Form from this generic table
3.  In your main application form, add a button that will clear out the generic table and then bring up the generic Datasheet Form
4.  The user brings up Excel, copies whatever range of cells of interest, and pastes them into the Datasheet Form
5.  The user dismisses the Datasheet Form
6.  Another button can then be pressed to process the information in the generic table

If you can bring up the Datasheet form in Dialog mode, then as soon as the user closes the form you can put the post-processing code there, so the user only has to press one button and copy-paste from Excel.  What's more easy than that?

There is no need for importing data, no need to create and open an Excel object from Access, no need to ask the user what range of cells he wants to copy.  The only VBA you need to write is a couple of simple lines to clear the generic table, bring up the form, and then whatever you need to process the copied Excel data.

Does that seem like something that will work for you?
A couple of other things I just thought of:

The method above won't guarantee that the rows are stored in the database in the same order as in the Excel worksheet

To have the rows in the database in the same order as the rows in the Excel worksheet you would need to do these additional things:

1. Have the first column of your generic table be an Autoindex column
2. In the OnOpen event of your generic DataSheet form you want to hide this column.  If the column name is 'ID' then:
  Me.ID.ColumnHidden = True
3. When the user pastes the data into Access he should be told to Paste Special > Text.  If he doesn't, then the first row will be interpreted as a header row and won't be entered into the generic table.

I've only tried this a few times, but it seems that the rows are pasted into the database in the order they are in the workbook, and by sorting on the ID row you can keep them in that order until you are done processing them.
Sub Foo()
Dim wb As Workbook
Dim AC As Object
Dim ret As Byte
Const SAVE_PATH As String = "C:\myTemp\AccessUpload.xls"
    '//Save data as an xls file
    Set wb = ActiveWorkbook
    Application.DisplayAlerts = False
    If CreateObject("Scripting.FileSystemObject").FileExists(SAVE_PATH) Then
        Kill SAVE_PATH
    End If
    wb.SaveAs SAVE_PATH, 56 '//56 => Excel 2003 File Format
    wb.Close False
    Application.DisplayAlerts = True
    '//Import Spreadsheet
    '//Note: Must set Access macro settings so that Access doesn't warn about macros when it opens
    Set AC = CreateObject("Access.Application")
    With AC
        .OpenCurrentDatabase "C:\myTemp\db1.mdb", False
        ret = .Run("GetXLData")
    End With

End Sub

IN ACCESS (public function in a standard module):
Public Function GetXLData() As Byte
Dim ret As Byte

    On Error GoTo ErrHandler
    ret = 1
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", "C:\myTemp\AccessUpload.xls", True
    ret = 0
GetXLData = ret
End Function
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.