Solved Smart Quotes

[Gasman]

There was a thread recently where smart quotes were causing an issue?

I asked a good while back on an Excel forum, how to mailmerge excel data to a label sheet.

This code was supplied to me. I might have modified it for my case, but the smart quotes had to remain. In fact when I saw them, I immediately corrected them to single quote and then the code did not work.

    With wdDoc
        With .MailMerge
            .MainDocumentType = wdMailingLabels
            .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
                            LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
                                                             "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
                            SQLStatement:="SELECT * FROM `" & StrName & "$` WHERE Surname <> ''"
            .Execute Pause:=False
            .MainDocumentType = wdNotAMergeDocument
        End With
        '  .Close SaveChanges:=False
        ' Document has extra pages with empty labels, unable to find out why.

        'Save as PDF file
        strPDFName = "GCCS Passengers - " & StrName
        With wdApp.ActiveDocument
            .SaveAs Filename:=StrMMPath & strPDFName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
            .Close SaveChanges:=False
        End With

So it seems, sometimes they do have their uses?
The smart quotes are in the SQLStatement line. They are ASC(96).

 

[sonic8]

The smart quotes are in the SQLStatement line. They are ASC(96).

There is no "smart quote" anywhere in your posting. The ASCII character 96 is the backtick, not a smart quote. It is used by some DBMS, e.g. MySql, as enclosing characters for entity names with whitespace or special characters, the same way as square brackets are used in Access or SQL Server.

The somewhat similar looking characters used as smart quotes in MS Word are the Unicode characters 0x201A and 0x2018. - I don't think they have any semantic meaning in any programming language.

 

[ComputerVersteher]

Hi,

backticks``delimit identifiers like[], so try replacing each other. Usually they are used in MySQL.

 

[Gasman]

There is no "smart quote" anywhere in your posting. The ASCII character 96 is the backtick, not a smart quote. It is used by some DBMS, e.g. MySql, as enclosing characters for entity names with whitespace or special characters, the same way as square brackets are used in Access or SQL Server.

The somewhat similar looking characters used as smart quotes in MS Word are the Unicode characters 0x201A and 0x2018. - I don't think they have any semantic meaning in any programming language.

Ah, I see. Thank you @sonic8 for explaining.

 

[GPGeorge]

There was a thread recently where smart quotes were causing an issue?

FWIW, just this week I encountered a problem with smart quotes -- Unicode characters 0x201A and 0x2018 -- in a different context. I have an application which retrieves information from the Google Book Catalog via an API. The Google Book response comes back as JSON. Sometimes, one of the elements of the JSON response includes quotes like, " The New York Time calls him the "the greatest living American writer ". "

When my VBA-JSON parser tried to parse out that element, the internal quotes were interpreted as delimiters and caused incorrect element boundaries to be identified; as a result my import failed. I asked ChatGPT for help. After a few prompts back and forth it came up with the basis for a function to convert the ordinary quotes to smart quotes so the parser didn't misidentify them as element delimiters. I added a second function to convert smart quotes to ordinary quotes.

Function ASCII2SmartQuotes(ByVal str As String) As String

    Dim replacements() As Variant
    Dim i As Integer
    
    replacements = Array(Array(" """, " “"), Array(""" ", "” "), Array(" '", " ‘"), Array("' ", "’ "))
    
    For i = LBound(replacements) To UBound(replacements)
        str = Replace(str, replacements(i)(0), replacements(i)(1))
    Next i
    
    ASCII2SmartQuotes = str
    
    Exit Function
    
End Function

 

Function SmartQuotes2ASCII(ByVal str As String) As String

    Dim replacements() As Variant
    Dim i As Integer

    replacements = Array(Array(" “", " """), Array("” ", """ "), Array(" ‘", " '"), Array("’ ", "' "))
    
    For i = LBound(replacements) To UBound(replacements)
        str = Replace(str, replacements(i)(0), replacements(i)(1))
    Next i
    
    SmartQuotes2ASCII = str
    
    Exit Function

End Function