Import a text onto a sheet - Multiple spaces as a separator. MINEDW

[wwj]

The code to import MINEDW hydrography *.dat onto Excell sheet.
- verified!

Code: Select all

Sub a_import()
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet

    ThisWorkbook.Activate
    
    mdlSht = "modeledHead"
    Set wbI = ThisWorkbook
    Set wsI = wbI.Sheets(mdlSht)  'Sheet where the modeled heads are import to.

    filePath = ActiveWorkbook.Path & "\dirTRg\"
    inputSheet = "1_hydrograph_x.dat"     ' Data file name without .csv extension.
    inputFile = filePath & inputSheet
    
    Set wbO = Workbooks.Open(inputFile, Format:=5)

    wbO.Sheets(1).Cells.Copy wsI.Cells
    wbO.Close SaveChanges:=False
    
    ' Extra processes
    ' Trim
    maxRow = Sheets(mdlSht).Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To maxRow
        strRow = wsI.Cells(i, 1)
        strTmd = LTrim(strRow)        ' trim spaces on the left.
        
        With CreateObject("vbscript.regexp")  ' convert multiple spaces into single space.
          .Pattern = "\s{2,}"
          .Global = True
          wsI.Cells(i, 1).Value = .Replace(wsI.Cells(i, 1).Value, " ")
          strTmd = .Replace(strTmd, " ")
        End With
        
        strArr = Split(strTmd, delimiter:=" ")  ' split and create a 0-based 1d array.
        sz = UBound(strArr)
        
        For j = 0 To UBound(strArr)
          wsI.Cells(i, j + 1) = strArr(j)
        Next j
        
    Next i
    
End Sub

[wwj]

Hot to convert multiple spaces into single space.
https://stackoverflow.com/questions/497 ... sing-regex