Debugging moment....
I modified your code - I moved all declarations (Dim) to the top of the procedure.
I added a "Debug.Print" statement.
Code:
Debug.Print intLine & " - " & strSqlDml
I set a breakpoint on the Do statement (Line 38), then single stepped through the code.
After the Debug statement was executated, the Immediate window had
Code:
1 - INSERT INTO tblExcelImport VALUES(1,'Eldon Base for stackable storage shelf, platinum,'35)
2 - INSERT INTO tblExcelImport VALUES(2,'1.7 Cubic Foot Compact "Cube" Office Refrigerators,'68.02)
3 - INSERT INTO tblExcelImport VALUES(3,'Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl,'2.99)
4 - INSERT INTO tblExcelImport VALUES(4,'R380,'3.99)
5 - INSERT INTO tblExcelImport VALUES(5,'Holmes HEPA Air Purifier,'5.94)
What do you notice? Hint, look at the Values clause. I can wait.....
OK,
The number to the left of "INSERT" is the number of times the code looped. Can ignore it.
Because you have not used field names, the values will be inserted into the fields of the table from left to right. So the first number to the right of the "VALUES" is the number in column A. You are trying to insert this number into the PK field (which is an Autonumber type field) "ItemId" which you cannot do. Next, the text would be inserted into the field "Description" and the last number would be inserted into the field "Price".
But look where you put the comma which separates the values for the fields. The comma is INSIDE the single quote, so you, in effect, only have 2 values, not 3 - the 35 (col G) is just hanging. (because you did not specify the field names you MUST fill ALL fields in the table. If you NEED the number from column A, you need to add another field to the table.
This should be the SQL for the current table design.
Code:
strSqlDml = "INSERT INTO tblExcelImport (Description, Price) VALUES('" & strColumnBcleaned & "'," & strColumnGcleaned & ")"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Full code
Code:
Option Compare Database
Option Explicit
Private Sub cmdImportExcel_Click()
On Error GoTo cmdImportExcel_Click_err:
Dim fdObj As Office.FileDialog 'declares variable fdObj
Dim varfile As Variant 'variant type variable that will store the selected file path
'variables declaration
Dim xlApp As Excel.Application 'the excel application
Dim xlWb As Excel.Workbook 'the excel workbook reference that will point to the opened workbook
Dim xlWs As Excel.Worksheet 'the excel worksheet with data
Dim intLine As Long 'the line counter
Dim strSqlDml As String 'string variable that stores the executed SQL statements
Dim strColumnBcleaned As String 'string variable that stores values from column B after replacing single quotes by four single quotes
'remember the quote is the string delimiter in SQL so it needs to be escaped
Dim strColumnGcleaned As String 'string variable that stores values from column G cleaned, the clean step replaces commas by dots as
Set fdObj = Application.FileDialog(msoFileDialogFilePicker) 'instantiates the variable creating a filepicker object using early binding
With fdObj 'using the with statement we will be working with fdObj by default
.AllowMultiSelect = False 'does not allow selecting more than one file
.Filters.Clear 'clears the file dialog file type existing filters
.Filters.Add "Excel 2003", "*.xls" 'this file dialog will only allow the selection of excel files, this is achieved handling the Filters collection
.Filters.Add "Excel 2007+", "*.xlsx" 'optional set the file dialog title
.Title = "Please select the excel file to import …"
.Show
If .SelectedItems.Count = 1 Then 'a file was selected so data can be imported from Excel from this point the loop import process will run
'the decimal separator in SQL is the dot
varfile = .SelectedItems(1) 'picking the selected file full path
CurrentDb.Execute "DELETE * FROM tblExcelImport", dbFailOnError 'clean the existing table
'instantiate the Excel application, creating the Excel application in memory, the excel Accplication will be visible so the user is able to see the loop iterating through Excel rows but usually it is hidden and only visible if indeed required
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open(varfile) 'opening the picked file by calling the Excel workbooks collection open method, it receives the file location as parameter and returns a reference for the opened file
Set xlWs = xlWb.Worksheets(1) 'setting the worksheet to the first one within the available, as it is the one having data to be imported
intLine = 1 'default counter initial value/line, this means we start iterating in line one
Do
'the next two lines replace single quotes in column B value and commas by dots as decimal separator in column G
strColumnBcleaned = Replace(xlWs.Cells(intLine, 2).Value2, "'", """")
strColumnGcleaned = Replace(xlWs.Cells(intLine, 7).Value2, "'", """")
'the next line creates a SQL insert statement using the previous obtained cleaned variables and the value for column A.
'The insert statement must have the sequence present in the destination table and is obtained by concatenating values per each line presented in the Excel file while iterating
'-----------------------------------------------
'strSqlDml = "INSERT INTO tblExcelImport VALUES(" & xlWs.Cells(intLine, 1).Value2 & ",'" & strColumnBcleaned & ",'" & strColumnGcleaned & ")" 'Org line
'-----------------------------------------------
strSqlDml = "INSERT INTO tblExcelImport(Description, Price) VALUES('" & strColumnBcleaned & "'," & strColumnGcleaned & ")" 'modified line
' Debug.Print intLine & " - " & strSqlDml '<<-- I added
CurrentDb.Execute strSqlDml, dbFailOnError 'executes the insert statement against the database, the dbFailOnError is an optional value that will make the Execute process return an error if the SQL was not properly executed
xlWs.Cells(intLine, 1).Select 'only puts the selected cell in Excel in the actual line position, this is not required and will even make the process slower, it is just present here so the reader can see things running
intLine = intLine + 1
Loop Until IsEmpty(xlWs.Cells(intLine, 1)) 'stopping criteria, when values in column A stop the loop will stop,
'please note in cells collection the first index is the row and the second one the column so we are making row changing.
'Once the loop stops the steps after close the open workbook, quit excel and clean the memory references to the created objects
xlWb.Close False
xlApp.Quit
Set xlApp = Nothing
Set xlWb = Nothing
Set xlWs = Nothing
DoCmd.OpenTable "tblExcelImport", acViewNormal, acEdit 'opens the loaded table so the user can see imported data
'this branch only happens if no file was selected
Else 'no file was selected
Call MsgBox("No file was selected.")
End If
End With
Exit Sub
cmdImportExcel_Click_err:
Select Case Err.Number
Case Else
Call MsgBox(Err.Number & "-" & Err.Description, vbCritical + vbOKOnly, "System Error …")
End Select
End Sub
Hope I was clear enough.....
Code executes correctly (for me)