Solved

import an excel file to an existing table and add fields

Posted on 2014-11-17
16
176 Views
Last Modified: 2014-11-26
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
0
Comment
Question by:akinamon1
  • 9
  • 7
16 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40448656
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
        Next
        
        '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 = ""
        Else
            For intLoop = 1 To .SelectedItems.Count
                If GetFile = "" Then
                    GetFile = .SelectedItems(intLoop)
                Else
                    GetFile = GetFile & ";" & .SelectedItems(intLoop)
                End If
            Next
        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.
0
 

Author Comment

by:akinamon1
ID: 40449564
hello there,
the vba code refers to function.

how do i connect it to "Private Sub Cmd_Click()?
0
 

Author Comment

by:akinamon1
ID: 40449679
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.

thanks
0
 

Author Comment

by:akinamon1
ID: 40449689
compile error:    me.cmd_Import.Enabled = Dir(me.txt_FileName) <> ""  


method or data member not found
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40449855
Take a look at the attached to see how I implement the FileDialog to select a file and enable the Import button.
ImportFile.accdb
0
 

Author Comment

by:akinamon1
ID: 40449909
thanks.

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.

thanks,


after the i
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40449965
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?
0
 

Author Comment

by:akinamon1
ID: 40450387
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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:akinamon1
ID: 40450870
Hey Dale.

did u get my last comment?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40450919
@akinamon1,

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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40466980
Itay,

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

Author Comment

by:akinamon1
ID: 40466988
hey Dale,

can i upload an access 2010 file?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40467037
sure
0
 

Author Comment

by:akinamon1
ID: 40467044
ok. i will upload it soon.

did u get my JPEG about the process?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40467178
Yes, I got the jpeg.
0
 

Author Comment

by:akinamon1
ID: 40467932
did you understand the proccess?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now