import an excel file to an existing table and add fields

i would like to import an excel file to an existing table in access. (i create a button for this import  in form 1)

how do i write a VBA code so that:
1. after i click the button, it will open a dialog box that the user will choose from which folder he wants to import the file.
2. a message box will popup if the import has succeed (or not succeed)
3. i would like to add and connect some fields (which are in the form 1, like ID_Order, Name_Order) to the table after the import has succeedded
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
To select the file for import, I generally use a function which uses the FileDialog

Public Function GetFile(Optional Title As String = "", _
                        Optional DefaultPath As String = "C:\", _
                        Optional FileTypes As String, _
                        Optional MultiSelect As Boolean = False) As String

    Dim fd As Object    'FileDialog
    Dim strFileTypes() As String, intLoop As Integer
    Set fd = FileDialog(3)  'msoFileDialogFilePicker
    With fd
        .Title = IIf(Title = "", "Select a file", Title)
        .InitialFileName = DefaultPath

        'Allow for multiple filetypes in the FileTypes string
        strFileTypes = Split(FileTypes, ";")
        For intLoop = LBound(strFileTypes) To UBound(strFileTypes)
            If Trim(strFileTypes(intLoop)) = "Access" Then
                .Filters.Add "Microsoft Access", "*.mdb;*.mda;*.mde;*.accdb;*.accda;*.accde"
            ElseIf Trim(strFileTypes(intLoop)) = "Excel" Then
                .Filters.Add "Microsoft Excel", "*.xl*;*.xls*"
            ElseIf Trim(strFileTypes(intLoop)) = "Text" Then
                .Filters.Add "Text", "*.txt;*.csv"
            ElseIf Trim(strFileTypes(intLoop)) = "Image" Then
                .Filters.Add "Picture/Image", "*.bmp;*.jpg;*.png"
            End If
        'If no file type is designated, use *.*
        If UBound(strFileTypes) = -1 Then .Filters.Add "Any file type", "*.*"
        .AllowMultiSelect = MultiSelect
        .InitialView = 2    'msoFileDialogViewDetails
        'Display the popup and process the return
        If .Show = 0 Then
            GetFile = ""
            For intLoop = 1 To .SelectedItems.Count
                If GetFile = "" Then
                    GetFile = .SelectedItems(intLoop)
                    GetFile = GetFile & ";" & .SelectedItems(intLoop)
                End If
        End If
    End With
    Set fd = Nothing
End Function

Open in new window

You would save this into a code module (not code behind a form) and call it from a form or some other location.  I generally have a textbox (txtFileName) with a command button (cmd_Browse) next to it.  In the Click event of cmd_Browse, I do something like:

Private Sub cmd_Browse_Click

    me.txt_FileName = GetFile("Title", , "Excel", False)

    'Enable/Disable the import command button depending on whether the file selected actually exists
    me.cmd_Import.Enabled = Dir(me.txt_FileName) <> ""   

End Sub

Open in new window

The next step, actually reading that file into an existing table is a little more complicated, so lets focus on getting this part working first.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
akinamon1Author Commented:
hello there,
the vba code refers to function.

how do i connect it to "Private Sub Cmd_Click()?
akinamon1Author Commented:
hi there,

i copy the function code.

now i didn`t understand the part
I generally have a textbox (txtFileName) with a command button (cmd_Browse) next to it.  In the Click event of cmd_Browse, I do something like
here is a print screen.cmd-browse.pngplease be more specific.

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

akinamon1Author Commented:
compile error:    me.cmd_Import.Enabled = Dir(me.txt_FileName) <> ""  

method or data member not found
Dale FyeOwner, Developing Solutions LLCCommented:
Take a look at the attached to see how I implement the FileDialog to select a file and enable the Import button.
akinamon1Author Commented:

i will  try it.

lets move to the next phase- installing the data which was imported to an existing table and adding fields which are shown in the form to the same table.


after the i
Dale FyeOwner, Developing Solutions LLCCommented:
Need to know a little more about the file you are importing from and the table you want to import to.

I generally recommend creating a "staging" table for the Excel data.  Excel data is inherently corrupt, there are way to many ways for a user to screw it up (putting a text character in a numeric field is the most obvious).  This table is generally configured with all of the fields as text values, and with the exact same column headings and sequence as the Excel spreadsheet (oh, this is another place where users excel at corrupting Excel files, renaming headers or repositioning them).

BTW, what version of Access are you using?
akinamon1Author Commented:
i am using access 2010.

i can send you the excel file which i want to import
. basicilly, the excel is in the same format every time.  before the importantion the user save the excel file in his desktop and then imports it to access. also, the table in access is in the same format all time.

so i need your help in :
1. save the excel file in an existing table
2. popup a message if the importation was succesfully
3. add fields to the table after the importantion.
akinamon1Author Commented:
Hey Dale.

did u get my last comment?
Dale FyeOwner, Developing Solutions LLCCommented:

Do you already have the table structure that you want to import into?  If so, it would be helpful if you could post a copy of that table structure in a new db with just that table, and no data.  Also post a copy of the Excel spreadsheet, with at least a couple of records, with any proprietary or personal information removed.

Don't know when I will get to it but will try to take a look within the next day or so.  In the meantime, you might want to take a look at the DoCmd.TransferSpreadsheet method described in the Access Help or do a search in EE on that subject, you should find lots of posts.

Dale FyeOwner, Developing Solutions LLCCommented:

This is where I expected you to add the sample database with the table you want to import to, and the sample spreadsheet with the information you wanted to upload.

Simply click the "Attach File" link below the "Post a Comment" box, browse to the two files (one at a time) and attach them to the message.
akinamon1Author Commented:
hey Dale,

can i upload an access 2010 file?
Dale FyeOwner, Developing Solutions LLCCommented:
akinamon1Author Commented:
ok. i will upload it soon.

did u get my JPEG about the process?
Dale FyeOwner, Developing Solutions LLCCommented:
Yes, I got the jpeg.
akinamon1Author Commented:
did you understand the proccess?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.