VBA import excel into access glitches when excel cell contains double ' '

Question

I am trying to import data from Excel into Access. Both 2010. Everything worked perfectly until I came across a cell that contained [text 'A' text]. Access completely stops the Sub at this point. When I manually change the Excel cell to [text A text] or the '' to ``, everything works perfectly again. But having to manually changing the source Excel defeats the purpose.

How do I import an Excel sheet when one or more cells contain [ 'A' ]? Thank you in advance for any help.

'This checks if file exsist, imports file, then imports any sequential files. 
Option Explicit
Public Sub ImportXL2(bolJustExcelFile As Boolean, Optional bolRefresh As Boolean)

            Dim rstXL As DAO.Recordset
            Dim x As Integer, y As Long
            Dim strPath1 As String, strPath2 As String
            Dim strPN As String, strDescription As String, strPrime As String
            Dim intOHB As Integer, sngCost As Single, intMin As Integer, intMax As Integer
            Dim strCode As Integer, strNumber As String, strDate As String, strQty As Integer, strRepairable As String, strEntity As String

            DoCmd.SetWarnings False
            DoCmd.RunSQL "DELETE FROM ExcelFile"

            If bolJustExcelFile = False Then
                DoCmd.RunSQL "DELETE FROM ExcelFileCombined"
            End If

            For x = 1 To 10

            DoCmd.RunSQL "DELETE FROM ExcelFiletemp"

            strPath1 = Environ("userprofile") & "\Desktop\Folder\ExcelFile.xlsx"
            strPath2 = Environ("userprofile") & "\Desktop\Folder\ExcelFile" & x & ".xlsx"

                If x = 1 Then
                    If FileExists(strPath1) = -1 Then
                        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ExcelFiletemp", strPath1, False, "A:L"

                        Else
                        If bolRefresh = True Then
                            MsgBox "ExcelFile File Not Found", , "Missing ExcelFile File"
                        End If

                        Exit For
                    End If
                Else
                    If FileExists(strPath2) = -1 And bolJustExcelFile = False Then
                        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ExcelFiletemp", strPath2, False, "A:L"
                    Else
                        GoTo SkipXL
                    End If
                End If

                Set rstXL = CurrentDb.OpenRecordset("SELECT * FROM ExcelFiletemp", dbOpenSnapshot)

                rstXL.MoveLast
                rstXL.MoveFirst

                    For y = 1 To 4
                        rstXL.MoveNext
                    Next y

                strEntity = Right(rstXL![F1], 6)

                    For y = 1 To 4
                        rstXL.MoveNext
                    Next y
            On Error GoTo ErrHandler

                    For y = 1 To rstXL.RecordCount - 8

                            strPN = rstXL![F1]
                            strDescription = rstXL![F2]
                            strPrime = rstXL![F3]
                            intOHB = rstXL![F4]
                            sngCost = rstXL![F5]
                            intMin = rstXL![F6]
                            intMax = rstXL![F7]
                            strCode = rstXL![F8]
                            strRepairable = rstXL![F12]

                            If x = 1 Then
                                DoCmd.RunSQL "INSERT INTO ExcelFile (PN, Description, Prime, OHB, Cost, Min, Max, Code, Repairable, Entity) VALUES ('" & strPN & "','" & strDescription & "','" & strPrime & "'," & intOHB & "," & sngCost & "," & intMin & "," & intMax & "," & strCode & ",'" & strRepairable & "','" & strEntity & "');"
                            End If

                            If bolJustExcelFile = False Then
                                DoCmd.RunSQL "INSERT INTO ExcelFileCombined (PN, Description, Prime, OHB, Cost, Min, Max, Code, Repairable, Entity) VALUES ('" & strPN & "','" & strDescription & "','" & strPrime & "'," & intOHB & "," & sngCost & "," & intMin & "," & intMax & "," & strCode & ",'" & strRepairable & "','" & strEntity & "');"
                            End If
                    rstXL.MoveNext
                    Next y
                rstXL.Close
SkipXL:
                Next x

            Set rstXL = Nothing

            DoCmd.SetWarnings True
ErrHandler:
                    If Err.Number = 94 Then 'Invalid use of Null

                    rstXL.MoveNext
                    End If

            End Sub

You're using the single quote to wrap your text fields. You either need to replace the single quotes in your string with nothing - or escape them. You should also modify your error handler to trap and exit if needed on SQL Insert errors. How you handle the singl quotes depends on whether you want them in your database exactly as they are - or cleaned up — dbmitch, Aug 14 '16 at 18:16
A cumbersome, yet possibly effective way, might be to import the data as arrays instead and loop through them escaping the single quotes (ascii 39, a.k.a. typewriter apostrophes). This might help: http://stackoverflow.com/questions/881194/how-do-i-escape-special-characters-in-mysql/881208#881208 — Miqi180, Aug 14 '16 at 18:25
[Parameter query](http://stackoverflow.com/search?q=%5Bms-access%5D+parameter+query) — HansUp, Aug 15 '16 at 03:43

score 1 · Answer 1 · answered Aug 14 '16 at 18:24

1

You can escape single quotes by doubling them up.

Function EscQ(text As String)

    EscQ = Replace(text, "'", "''")

End Function

Usage:


DoCmd.RunSQL "INSERT INTO ExcelFileCombined (PN, Description, Prime, OHB, Cost, Min, Max, Code, Repairable, Entity) VALUES ('" & EscQ(strPN) & "','" & EscQ(strDescription) & "','" & EscQ(strPrime) & "'," & intOHB & "," & sngCost & "," & intMin & "," & intMax & "," & strCode & ",'" & EscQ(strRepairable) & "','" & EscQ(strEntity) & "');"

answered Aug 14 '16 at 18:24

Thomas Thank you, Function EscQ(text As String) EscQ = Replace(text, "'", "''") End Function was an elegant solution. – MrJCob Aug 15 '16 at 04:52
I'm glad that I was able to help. You might want to consider posting your code on [Code Review](http://codereview.stackexchange.com/). You can get an in depth analyze of your code there. You can combine the `Select` and `Insert` statements into a single query. Something like: – Aug 15 '16 at 05:09
`INSERT INTO ExcelFile (PN, Description, Prime, OHB, Cost, Min, Max, Code, Repairable, Entity) SELECT [F1],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F12] FROM ExcelFiletemp` – Aug 15 '16 at 05:09

score 0 · Answer 2 · answered Aug 14 '16 at 18:23

I think using recordset to add new record will somehow make you worry free about SQL Syntax getting wrong because of special characters e.g. single or double quotes. You can try add a function:

Function insrt_item(rstXL as DAO.Recordset, tbl_dest as String) ' set tbl_dest   to ExcelFile or ExcelFileCombined  since they are same fields anyway
     With currentdb.OpenRecordSet(tbl_dest)
       .AddNew
       !PN = rstXL!F1
       !Description = rstXL!F2
        '.. add more fields here
       .Update
       .Close 
    End With
End Function

VBA import excel into access glitches when excel cell contains double ' '

2 Answers2