Link to home
Create AccountLog in
Avatar of dougf1r
dougf1r

asked on

Import excel file and append to existing table with a Command Button

My apologies for the duplicate post, however, I am nearing a deadline and need to get this sorted out...

I need configure a button in a form to 1) browse to and 2) import an Excel file and 3) append it to an existing table.

How might I implement this?

I am using this Module for a similar functionality, however it is for exporting to an Excel file and not importing.

I was provided some advice here, however, It was not sufficiently detailed for me to implement.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Avatar of dougf1r
dougf1r

ASKER

I copied the query, form, and module (with same names) into my own db and I get the compile error: Ambiguous name detected: ahtAddFilterItem when trying to browse for the Excel file

What might be going wrong here?

I am also wondering if this module will allow me to append the Excel sheet to an existing table?



you have an existing  function ahtAddFilterItem from your original modules,  look for it and delete the module

i suggest that you  run the sample db by itself.

<I am also wondering if this module will allow me to append the Excel sheet to an existing table? > yes it can be done

locate the codes in the click event of the "Import" button.
change the name of the table in the codes with the name of your table

Avatar of dougf1r

ASKER

<you have an existing  function ahtAddFilterItem from your original modules,  look for it and delete the module>

I need all of the modules I currently have in the dabase.


<i suggest that you  run the sample db by itself.>

I ran the sample db by itself and got a run-time error '3011' MS Jet could not find the object 'Orders$A:I'
replace 'Orders$A:I'   with Name of the sheet you want to get the data from

if name of sheet is Sheet1, like this


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "NameOfYourTable", Me.txtFileName, True, "Sheet1!"


Avatar of dougf1r

ASKER

so this will hardwire the name of the sheet on the Excel import table then? What if the sheet is named something else?
if you are going to import the data from the first sheet, just omit the sheet name

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "NameOfYourTable", Me.txtFileName, True
Avatar of dougf1r

ASKER

Ok. That works.

Only thing is I had to delete one of my necessary modules because of the "ahtAddFilterItem" issue.

Can I work around this somehow?
just copy the form from the sample db to your application.

then test, if the import is successful, then you don't need to import the module.
Avatar of dougf1r

ASKER

Ok. That works too.

One last thing... how can I allow the user to close the form without the whole db closing?

Either show the Red X in the the top right corner and/or modify this code:

Private Sub cmdQuit_Click()
    DoCmd.Quit
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dougf1r

ASKER

All is good! Many thanks for your help.