VBA - Import bas module with special characters

Question

I have implemented a automated import+version controlling for excel-modules (bas, frx and frm files), so the clients will always get the newest version of VBA scripts.

The solution is something like this:

1. A vba scripts (on Workbook_Open) checks the github repository if the version number is equal
2. If not, the script will now do following

• Download main.zip from github
• Extract the zip files
• Remove all existing modules
• Import all modules

But the problem is, when importing modules, somehow the special characters in bas/frm/frx files will not be kept, they will be encoded to something else like:

- pensionSheet.Cells(26, 2).value = JsonObject("Børnerente")

It should be

- pensionSheet.Cells(26, 2).value = JsonObject("Børnerente")

Iam using this following function to import bas modules:

Function addBasFile(strPath As String)

Dim path As String
Dim objModule As Object

path = strPath & "\pb_integration-main\pensionBrokerExport.bas"
Set objModule = Application.VBE.ActiveVBProject.VBComponents.Import(path)
objModule.Name = "PensionBrokerExport"
Debug.Print ("PensionBrokerExport imported")

End Function

Any idea how i can keep special chars when importing modules to excel?

Answer 1

If you open the files downloaded from GitHub inside VS Code, Notepad++ or even Notepad you'll probably see at the bottom right corner that the file is encoded with UTF-8. That's a problem since the VBA editor expects the file to be encoded in the local "ANSI" encoding (more info).

If you also see LF to the right of the file encoding, that means that the line endings are using Unix-style line endings (line feed) instead of Windows-style line endings (carriage return + line feed). That might cause issues as mentioned here.

---

To fix it

Option 1: If you have full control over the Github repo, you could fix this easily by forcing the GitHub export-to-zip process to convert the line endings and text encoding for you. I explain it more in details here, but the idea is to use a .gitattributes file. For example, here's what I use for VBA projects:

# Auto detect text files and perform LF normalization
* text=auto

# VBA extensions - Preseve CRLF in working directory
*.bas text eol=crlf working-tree-encoding=CP1252
*.cls text eol=crlf working-tree-encoding=CP1252
*.frm text eol=crlf working-tree-encoding=CP1252

# VBA extensions - Mark as binary
*.frx binary

Option 2: If you can't add a .gitattributes file or you don't want to, you could add the following to your code and run it on the files that need to be imported in VBA:

Sub ConvertGitHubFileForVbaImport(FilePath As String)

    'Read content of the file with utf-8 encoding
    Dim Content As String
    With CreateObject("ADODB.Stream")
        .Type = 2  ' Private Const adTypeText = 2
        .Charset = "utf-8"
        .Open
        .LoadFromFile FilePath
        Content = .ReadText(-1)  ' Private Const adReadAll = -1
        .Close
    End With

    'Replace Unix-style line endings with Windows-style line endings (Need to check if that applies to your file)
    If InStr(Content, Chr$(13) & Chr$(10)) = 0 Then
        Content = Replace(Content, Chr$(10), Chr$(13) & Chr$(10))
    End If

    'Write file with default local ANSI encoding (generally Windows-1252 on Western/U.S. systems)
    Open FilePath For Output As #1
    Print #1, Content
    Close #1

End Sub