Results 1 to 11 of 11
  1. #1
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    82

    VBA to import excel problems

    Good afternoon,
    I am attempting to write vba to import excel spreadsheet to a table in access, select the columns wanted and delete. I have the following code that when clicking on cmdImportExcel it gives me the error "ambiguous name detected". I believe it is because I have two Private Sub cmdImportExcel_Click(). Can someone help me fix the problem. Below is the code. Not sure how to combine so it works.

    Private Sub cmdImportExcel_Click()


    On Error GoTo cmdImportExcel_Click_err:
    Dim fdObj As Office.FileDialog 'declare variable fdObj
    Dim varfile As Variant 'variant type variable that will store the selected file path
    Set fdObj = Application.FileDialog(msoFileDialogFilePicker) 'instantiates the variable creating a filepicker object using late 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 2007+", "*.xls" 'this file dialog will only allow the selection of xlsx files
    .Title = "Please select the Excel file to import..." 'set the file dialog title
    .Show ' Show the dialog box. If the .Show method returns True, the
    ' user picked at least one file. If the .Show method returns
    ' False, the user clicked Cancel.
    If .SelectedItems.Count = 1 Then 'a file was selected
    Call MsgBox("The selected file was: " & .SelectedItems(1)) 'for now we will test the file picking by sending the file path to a message box on screen
    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

    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
    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
    '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
    '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 & ")"
    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

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,109
    Remove the first one (or comment it out) and see what you get.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    82
    I removed the first portion and received a system error 3075 Syntax error (missing operator) in query expression "Eldon Base for stackable storage shelf platinum 35"

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,219
    Would you post a sample spreadsheet and
    your dB or a new dB with just the table "tblExcelImport"?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    82

  6. #6
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    82
    sorry, it put it in twice. Any help would be appreciated

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,219
    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)
    Attached Files Attached Files
    Last edited by ssanfu; 02-23-2021 at 08:12 PM. Reason: Added modified dB
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    82
    Wow, what an oversight. I really appreciate the the help and addition. You Rock it

  9. #9
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    82
    on this same code, I am trying to now do 6 rows from an excel spreadsheet. I have made the adjustments in the code to accommodate such, but having a problem with this line.
    strSqlDml = "INSERT INTO tblImportProduct(DocumentID, DateMailing, ClientName, ProductName, SheetCount, ProductNumber) VALUES('" & strColumnBcleaned & "'," & strColumnGcleaned & "'," & strColumnGcleaned & "'," & strColumnGcleaned & "'," & strColumnGcleaned & "'," & strColumnGcleaned & ")"

    In addition, trying to have the import start at row 2 from excel. The first line is header and would like to keep it as I am importing only some of the information and would like to keep the excel spreadsheet alone. Do you know if this is possible?

  10. #10
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    82
    NVM on the row, I realized what I did and fixed it, intLine = 2

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,219
    Happy you were able to figure it out. Good job.
    Troubleshooting isn't so hard, is it? But there have been times when you have to just walk away and take a break to let the brain recover..........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-08-2019, 03:18 PM
  2. excel to access import problems
    By chr1stoper1 in forum Macros
    Replies: 2
    Last Post: 12-03-2015, 10:13 AM
  3. Replies: 4
    Last Post: 08-10-2015, 03:55 PM
  4. Import Problems
    By patpot44 in forum Import/Export Data
    Replies: 3
    Last Post: 04-12-2011, 08:58 PM
  5. Problems with Import Spreadsheet Wizard
    By Conan Kelly in forum Import/Export Data
    Replies: 1
    Last Post: 11-29-2005, 09:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums