Link to home
Start Free TrialLog in
Avatar of AmadeusC27
AmadeusC27Flag for United States of America

asked on

access Button to download excel file into ms access 2007 table

I have a MS Access 2007 database where I need to give the user the ability to download an excel file of their choice (excel file could be located in multiple places but will always be formatted the same way) into an established table in my database.  The field names of the access table match the column names of the excel file.  Can anyone give me the code that the click event of the button needs to have to do this?  

To be clear, when the user clicks the button, I'd like them to browse to the excel file and then, once the excel file is chosen, it would automatically be downloaded into the table, maybe with an "Are you sure?" prompt first.  Is this possible?  Thanks!
Avatar of SiddharthRout
SiddharthRout
Flag of India image

>>> Can anyone give me the code that the click event of the button needs to have to do this?  

You don't need to write a separate code for this. Access has an inbuilt feature for this. Please see the snapshot.

Sid
Untitled.jpg
Avatar of AmadeusC27

ASKER

Sid,

Thanks.  I'm aware of that.  The problem is that my users aren't sophisticated enought to use that feature...I was hoping to make the import more transparent to them.
In that case you could use the Open FileDialog to let the user choose the file for example

    Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog(FileDialogType:=msoFileDialogOpen)
    dlgOpen.Show

Open in new window


and then use the

DoCmd.TransferSpreadsheet command to import the file in the relevant table?

Sid
Sid,

I put your code in the Click event of my button and it didn't work.  I got an error that says "User-defined type not defined"

Any other ideas?
Hi Amadeus,

Try this:

The following code will ask you for the DB Name....

When prompted, just type the name of the Acces DB without the .mdb or any other file extension.
In order to work, the Workbook must be in the same folder than the DB is and the Table fields must have the same name as the values in row 1 (Column Headers) in the spreadsheet.


Private Sub CommandButton1_Click()

 Dim cnt As ADODB.Connection
    Dim stSQL As String, stCon As String, stDB As String
    Dim stSQL2 As String
    Dim ANS As String
     
     'database path - currently same as this workbook
ANS = Application.InputBox("PLEASE TYPE THE DATA BASE NAME")
     
    stDB = ThisWorkbook.Path & "\" & ANS & ".mdb"
    
    stCon = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & stDB & ";UID=admin;PWD=9ja75HHe320zP;"
     
     ' SQL code for GL Insert to Access
    stSQL = "INSERT INTO -YOUR TABLE NAME HERE SELECT- * FROM [-YOUR SHEET NAME HERE-$] IN '" _
    & ThisWorkbook.FullName & "' 'Excel 8.0;'"
     
     'set connection  variable
    Set cnt = New ADODB.Connection
     'open connection to Access db and run the SQL
    With cnt
        .Open stCon
        .CursorLocation = adUseClient
        .Execute (stSQL)
    End With
     'close connection
    cnt.Close
     
     'release object from memory
    Set cnt = Nothing

End Sub 

Open in new window


This code works with excel and access 2003...BUT...If you have an error runing it in 2007, probably could be the ".mdb" that is the type file for access 2003... I'm not sure if the extension was changed for Access 2007... if so, change the ".mdb" to the right extension name in  all the code.

Hope it works,
Roberto.
typo:

in this line:   stSQL = "INSERT INTO -YOUR TABLE NAME HERE SELECT- * FROM [-YOUR SHEET NAME HERE-$] IN '"

it should be:

  stSQL = "INSERT INTO -YOUR TABLE NAME HERE- SELECT * FROM [-YOUR SHEET NAME HERE-$] IN '"

And you have to change: -YOUR TABLE NAME HERE- for you table name
and -YOUR SHEET NAME HERE- for the name of the worksheet where the data will be exported to access...

The Final result should be something like:  
stSQL = "INSERT INTO mytable SELECT * FROM [Sheet1$] IN '"

Roberto.
Pabilio,

I haven't tried your code because it looks to me as if the code would need to be embedded in the excel file.  I need the code to be embedded in the Access file.  

Maybe my original question wasn't clear, but there are multiple different excel files that will be being imported into the access database.  I want the access database user to be able to, with a click of a button within access, browse to an excel file of their choice and import it into a pre-defined table in my database.

Is this possible?
AmadeusC27: Show me the exact code that you are using?

Sid
Sid,

I don't have the code...that's what I'm asking for.  

>>> I don't have the code...that's what I'm asking for.  

I meant the code that you tried.

Anyways do this. I tested this and it works. Also do set a reference to the Office Object library. For that in the VBA editor, Click to menu Tools~~> References and select the Microsoft Office xx.xx Object Library.

Option Compare Database

Private Sub Command1_Click()
    Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
    dlgOpen.Show
End Sub

Open in new window


Please note that the above code show you how to call the file open dialog. You will still have to code more for opening the file and using the DoCmd.TransferSpreadsheet to achieve the final objective.

Sid
Sid,

That code does work...can you help me on the code for opening the file and importing the spreadsheet?

Thanks!

Sure. Can you upload your database(you can empty it if you want) and a sample Excel file so that I can test it before posting?

Sid
Sid,

Sure!  I've emptied out everything that is immaterial.  Here is the database with the code you provided (attached to the button on the form) and the table that I want to import the excel file data into.  I've also attached a sample excel file that would be imported into the table.

Let me know if I can provide anything else to help!

Thank you!
sample.mdb
sample-je.xls
ASKER CERTIFIED SOLUTION
Avatar of AmadeusC27
AmadeusC27
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>>>I guess I should reward the points to myself (just kidding)...

And I would support that if that was possible. Great job :)

Just to let you know that this question was on my radar and I was working on it and it's the 10th today (3rd Day) ;) I am sometimes slow in answering questions as [I have a private life too ;)]

I see you have incorporated the "FileDialog" that's good. However you are looping the Excel Rows. That is very slow as compared to DoCmd.TransferSpreadsheet.

Did you try that angle?

Sid
No one gave me a answer that would work for my application - so I figured it out