Solved

import an excel file to an existing table and add fields

Posted on 2014-11-17
16
184 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 48

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

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


method or data member not found
0
 
LVL 48

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 48

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
 

Author Comment

by:akinamon1
ID: 40450870
Hey Dale.

did u get my last comment?
0
 
LVL 48

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 48

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 48

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 48

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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