CSV file with Comma's between quotation marks - Reg Expression - CSV to Array

Option Explicit
Sub CVStoArray1()
Dim dbArrayConst, dbRowConst As Long, dbColConst As Long, strlFilePathName As String


strlFilePathName = Application.GetOpenFileName("Text Files (*.csv),*.csv", , "Select CSV file...")


With Workbooks.Open(Filename:=strlFilePathName)
dbArrayConst = .Sheets(1).UsedRange.Value2
.Close
End With


MsgBox "Lowerbound " & LBound(dbArrayConst)


dbRowConst = UBound(dbArrayConst)
dbColConst = UBound(dbArrayConst, 2)


MsgBox "Rows " & dbRowConst & " Columns " & dbColConst


End Sub

Sub SpltCmm1()
Dim strLine As String
Dim m




strLine = "012,034 ABCDE,,,""EFG,56,,,HIJ,,KLMN"",,56""OP,QR,ST,"",07890,UVWXYZ,"


With CreateObject("VBScript.RegExp")
.Pattern = """[^""," & Chr(2) & "]+,[^""" & Chr(2) & "]+"""
Do While .test(strLine)
Set m = .Execute(strLine)(0)
Mid$(strLine, m.FirstIndex + 1, m.Length) = Replace(m.Value, ",", "|")
Loop
End With
MsgBox strLine
End Sub

Sub SpltCmm2()
Dim lngb As Long, lngz As Long, strLine As String


strLine = "012,034 ABCDE,,,""EFG,56,,,HIJ,,KLMN"",,56""OP,QR,ST,"",07890,UVWXYZ,"


lngb = InStr(strLine, Chr(34))
lngz = InStr(lngb + 1, strLine, Chr(34))


strLine = Mid(strLine, 1, lngb - 1) & Replace(Mid(strLine, lngb + 1, lngz - lngb - 1), ",", "|") & Mid(strLine, lngz + 1)
MsgBox strLine
End Sub

Sub CVStoArray2()
Dim dbArrayConst, dbRowConst As Long, dbColConst As Long, strlFilePathName As String

strlFilePathName = Application.GetOpenFileName("Text Files (*.csv),*.csv", , "Select CSV file...")

dbCSVArray strlFilePathName, dbArrayConst

MsgBox "Lowerbound " & LBound(dbArrayConst)

dbRowConst = UBound(dbArrayConst)
dbColConst = UBound(dbArrayConst, 2)

MsgBox "Rows " & dbRowConst & " Columns " & dbColConst
End Sub

Function dbCSVArray(ByRef strfile As String, ByRef CSVArray As Variant) As Boolean
Dim objFSO As FileSystemObject, objStream As TextStream
Dim dbLine As String, objLine As String, dbArray As Variant
Dim LineCnt As Long, ColCnt As Long, n As Long, e As Long
dbCSVArray = False
Set objFSO = CreateObject("Scripting.FilesystemObject")
Set objStream = objFSO.OpenTextFile(strfile)
LineCnt = 0
ColCnt = 0
While Not objStream.AtEndOfStream
objLine = objStream.ReadLine
If InStr(1, objLine, Chr(34)) > 0 Then
dbLine = SpltCmm(objLine)
Else
dbLine = objLine
End If
LineCnt = LineCnt + 1
ColCnt = WorksheetFunction.Max(ColCnt, UBound(Split(dbLine, ",")), Len(dbLine) - Len(Replace(dbLine, ",", "")))
Wend
objStream.Close
ReDim CSVArray(1 To LineCnt + 1, 1 To ColCnt + 1)
If LineCnt > 1 And ColCnt > 1 Then
dbCSVArray = True
End If
Set objStream = objFSO.OpenTextFile(strfile)
e = 0
While Not objStream.AtEndOfStream
objLine = objStream.ReadLine
If InStr(1, objLine, """") > 0 Then
dbLine = SpltCmm(objLine)
Else
dbLine = objLine
End If
dbArray = Split(dbLine, ",")
e = e + 1
For n = 0 To UBound(dbArray)
CSVArray(e, n + 1) = dbArray(n)
Next n
Wend
Set objStream = Nothing
Set objFSO = Nothing
End Function


Function SpltCmm(strLine As String) As String
Dim matches
With CreateObject("VBScript.RegExp")
.Pattern = """[^""," & Chr(2) & "]+,[^""" & Chr(2) & "]+"""
Do While .test(strLine)
Set matches = .Execute(strLine)(0)
Mid$(strLine, matches.FirstIndex + 1, matches.Length) = Replace(matches.Value, ",", Chr(124))
Loop
End With
SpltCmm = strLine
End Function