import from excel to access

[akinamon]

hello everyone,

i would like you to help me solving my next problem:

i am trying to write a macro code (when i press a button in a form which i created) to import an excel file to an existing table (named " newtable")

the code should include the following:
1. a msg box that will pop-up and say "from which path to import the file"


2. the name of the file will insert automataclly in a field (named " the file name") in the same form where the button is.

also the excel file includes headers and i would like also to import them.

thanks

[June7]

Common topic. Did you search forum/web?

Did you try the import wizard? Can save import specification and reference the specification with TransferSpreadsheet method.

Importing headers to existing table does not make sense to me.

[akinamon]

Common topic. Did you search forum/web?

Did you try the import wizard? Can save import specification and reference the specification with TransferSpreadsheet method.

1Importing headers to existing table does not make sense to me.

hey there,

first of all, thanks for the quick response.
altough there is a way to it by import wizard, i still want to write it using vba code. also, i don`t think that the import wizard can pop-up a dialog box (from which path in the computer to import)
and i was wrong when i wrote "the excel file includes headers and i would like also to import them" - i want to import to existing table without the headers

thanks

[June7]

Sorry my bad, the import specification is not used with TransferSpreadsheet, although TransferSpreadsheet is one method for importing.

Consider DoCmd.RunSavedImportExport that runs an import specification built and saved with the wizard.

Otherwise, require more complex VBA that opens and manipulates Excel objects.

[akinamon]

thanks,

can u help me writing the complex VBA code?

[June7]

This is a common topic. Search forum and web. Attempt code. If you have issues, post specific question.

If TransferSpreadsheet or RunSavedImportExport won't suit your situation, start with review of http://www.ehow.com/how_8156781_use-...el-access.html

Regardless of which import method, if you want to allow user to select file path\name, need to use FilePicker dialog. That is different programming issue. Also a common topic.

[ssanfu]

See Ken Snell's site at http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

[gambit1430]

akinamon,

ive tried it before....i used forms and vba control on this...and created a dummy table

1. Create a form that will locate your xls file (ex...browse file form kinda staff)
then put your code on the button...to import that excel to your dummy table (you will use vba on this, "docmd.transferspreadsheet"

2. create queries to clean up your dummy table

3. create query to append cleaned dummy table to your final table

4. delete data on dummy table

Follow June7's advise, check on google for the sample and codes..you can follow the steps given above and i hope this will guide you.