Avatar of akinamon1
akinamon1
 asked on

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
Microsoft Access

Avatar of undefined
Last Comment
akinamon1

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
akinamon1

ASKER
hello there,
the vba code refers to function.

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

ASKER
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
akinamon1

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


method or data member not found
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dale Fye

Take a look at the attached to see how I implement the FileDialog to select a file and enable the Import button.
ImportFile.accdb
akinamon1

ASKER
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
Dale Fye

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
akinamon1

ASKER
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.
akinamon1

ASKER
Hey Dale.

did u get my last comment?
Dale Fye

@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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Dale Fye

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

ASKER
hey Dale,

can i upload an access 2010 file?
Dale Fye

sure
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
akinamon1

ASKER
ok. i will upload it soon.

did u get my JPEG about the process?
Dale Fye

Yes, I got the jpeg.
akinamon1

ASKER
did you understand the proccess?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23