Solved

access Button to download excel file into ms access 2007 table

Posted on 2011-03-02
16
378 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:AmadeusC27
  • 8
  • 6
  • 2
16 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>> 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
0
 

Author Comment

by:AmadeusC27
Comment Utility
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
0
 

Author Comment

by:AmadeusC27
Comment Utility
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?
0
 
LVL 5

Expert Comment

by:Pabilio
Comment Utility
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.
0
 
LVL 5

Expert Comment

by:Pabilio
Comment Utility
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.
0
 

Author Comment

by:AmadeusC27
Comment Utility
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?
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
AmadeusC27: Show me the exact code that you are using?

Sid
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:AmadeusC27
Comment Utility
Sid,

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

0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>> 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
0
 

Author Comment

by:AmadeusC27
Comment Utility
Sid,

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

Thanks!

0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
0
 

Author Comment

by:AmadeusC27
Comment Utility
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
0
 

Accepted Solution

by:
AmadeusC27 earned 0 total points
Comment Utility
I guess I should reward the points to myself (just kidding)...I figured it out on my own.  In case anyone else is looking for the solution, here's what I did:
Private Sub Command40_Click()
 
Dim dlgOpen As FileDialog
'Open a dialog box and get a filename for the data database.
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
.AllowMultiSelect = False
.Show
End With
'Returns the table connection file path string.
GetFileLink = dlgOpen.SelectedItems.Item(1) 'Can only be item 1.


Dim myRec As DAO.Recordset
Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim RowNum, X As Double

Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(GetFileLink)
Set xlsht = xlWrkBk.Worksheets("Journal_Details")
Set myRec = CurrentDb.OpenRecordset("Journal Entries")

'Find out how many data rows in the worksheet
RowNum = 2
While xlsht.cells(RowNum, 1) <> ""
    RowNum = RowNum + 1
Wend
RowNum = RowNum - 1

For X = 2 To RowNum
myRec.AddNew
myRec.Fields("BATCHNBR") = xlsht.cells(X, 1)
myRec.Fields("JOURNALID") = xlsht.cells(X, 2)
myRec.Fields("TRANSNBR") = xlsht.cells(X, 3)
myRec.Fields("DESCOMP") = xlsht.cells(X, 4)
myRec.Fields("ROUTE") = xlsht.cells(X, 5)
myRec.Fields("ACCTID") = xlsht.cells(X, 6)
myRec.Fields("TRANSAMT") = xlsht.cells(X, 7)
myRec.Fields("RATEDATE") = xlsht.cells(X, 8)
myRec.Fields("TRANSDESC") = xlsht.cells(X, 9)
myRec.Fields("TRANSDATE") = xlsht.cells(X, 10)
myRec.Update
Next X

End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>>>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
0
 

Author Closing Comment

by:AmadeusC27
Comment Utility
No one gave me a answer that would work for my application - so I figured it out
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now