Use a "Button" to automatically import Data into an access Table **PICS**

[KingOf206]

Ok so I have a basic form which allows users to enter single records. However sometimes they need to enter in 100 records and it would be very time consuming to do this 1 by 1. So the idea would be to create a "button" that will import the data from excel into the Access table.

This function could potentially be done multiple times a day so I would not want to save over the table data each time it was imported, but would rather "append" the data.

In the following picture I have the form (Left, Frm_WorkInProgress) I am using, there is an Orange "button" called "Import Multiple Trays" I would that button to add all the records from the excel file (right, Book1). The excel file shown would be modified daily and when I import this data into access, I would like it to only add the "new records" and dont change any data in the Table "Tbl_WorkInProgress"



What is the best way to accomplish this?



I really appreciate any help that is given.

[June7]

Review http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

[KingOf206]

I have tried that one, but it's not working. I will keep trying but I am not getting any good results. When I click the button, absolutely nothing happens...

[June7]

Post your code. Have you selected [Event Procedure] in the button Click event?

[KingOf206]

Yes, event procedure is there.

here is my Written Code:



Private Sub Command8_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean




blnHasFieldNames = True


strPath = "C:\Users\buchanl\Desktop\Book1.xlsx"


strTable = "Tbl_WorkInProgress"


strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames


' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile


strFile = Dir()
Loop


End Sub




HERE IS A SCREEN SHOT:

[June7]

Why are you concatenating strPath and strFile? strPath already has the file name. Don't include Book1.xlsx in the strPath string.

Step debug. What is strPathFile value? Refer to link at bottom of my post for debugging guidelines.

[KingOf206]

The Code that is in there is the exact same code as what is in the link that you gave me. I only copied what you gave me and nothing else was extra. I am not a "programmer" so its near impossible for me to answer your questions, although I wish that I could.

Right now I am stuck. I really do appreaciate your help on this one. I am just not sure what i should be trying now. I looked over the "debuggerr" link that you suggested... I am still looking through it but its not helping me out much. How else can I help you to assist me?

[June7]

The example code does not include Book1.xlsx file name in strPath string. It refers only to folder names. Look at it again.

[KingOf206]

OK I re did my code based on taking out "Book1.xlsx"

Here it is (And still nothing happens):

Private Sub Command8_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean




blnHasFieldNames = True


strPath = "C:\Users\buchanl\Desktop"


strTable = "Tbl_WorkInProgress"


strFile = Dir(strPath & "*.xlsx")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames


' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile


strFile = Dir()
Loop


End Sub

[June7]

The path must end with \ character to separate it from the file name. That is also demonstrated in the example.

[KingOf206]

June7 You are the FRIGGIN MAN!! That was it!!! I cant believe I over looked that!! Thank you for pointing that out! It now works perfectly!

[ssanfu]

June7 You are the FRIGGIN MAN!!

Uh.... shouldn't that be: "June7 You are the FRIGGIN WOMAN!!"