Formatting Tables

[justlearning4]

I have searched through the existing threads that pertain to things like this and so far, I have not been able to find a solution. I have this text file (see number 2 below) that I need to get specific data from and load it into excel; the first line of each data chunk is the part that I am looking for specifically. Ideally, I would like to then space this data out into separate columns based on the "space" delimiter that is between each word/number/date/character. Additionally, I would like this to be repeatable since this is a project that will occur on a weekly basis.


For reference, this is what I would like the first lines of each data chunk to look like once it's brought into excel.

CITY AND COUNTY OF REGION, MEXICO	11/06	1,000,000.00	Aaa3/AA-/AA-/NR
New Caldwell State	11/04	1,000,000.00	Aaa/AAA/AAA/NR

2. For reference, here is a sample of how the txt file will look once it is loaded into excel. The code that I am looking for would then take the data from the sheet that the txt file loads into and converts it into the format I laid out above:

Book3
	A	B
1	Column1
2	CITY AND COUNTY OF REGION, MEXICO 11/06 1,000,000.00 Aa3/AA-/AA-/NR
3	FOR AND ON BEHALF OF ITS DEPARTMENT OF AVIATION
4	AIRPORT SYSTEM REVENUE BONDS
5	SERIES 2022C (FIXED RATE NON-AMT)
6	SERIES 2022D (FIXED RATE AMT)
7	SERIES 2022E (TERM RATE NON-AMT)
8	MGR: Barclays Capital Inc., New York
9
10
11	New Caldwell State 11/04 1,000,000.000 Aaa/AAA/AAA/NR
12	Environmental Facilities Corporation
13	State Revolving Funds Revenue Bonds, Series 2022 B
14	(2010 Master Financing Program) (Green Bonds)
15	MGR: Jefferies LLC, New York
16
17
NEGOTIATED_OFFERING_DATA

 

[justlearning4]

You don't explicitly say it, but if the 11/06 and 11/04 examples are MM/DD dates and they only occur in the lines you want to extract then a regular expression can be used to identify those lines and parse them into Excel.

This macro reads the text file directly (no need to import it or copy and paste it into Excel first) and parses the required data into 4 columns in the active sheet (modify the textFile string to suit).

Public Sub Import_Specific_Data_From_Text_File()

    Dim reMMDD As Object 'VBScript_RegExp_55.RegExp
    Dim matches As Object 'VBScript_RegExp_55.MatchCollection
    Dim textFile As String
    Dim fileNum As Integer
    Dim fileData As String, fileLine As Variant
    Dim destCells As Range, n As Long
    Dim amount As String, info As String

    textFile = "C:\path\to\THE TEXT FILE.txt"     'CHANGE THIS
   
    With ActiveSheet
        .Cells.ClearContents
        .Range("A1:D1").Value = Array("Name", "Date", "Amount", "More Info")
        Set destCells = .Range("A2:D2")
        n = 0
    End With
   
    Set reMMDD = CreateObject("VBScript.RegExp") 'New VBScript_RegExp_55.RegExp
    With reMMDD
        .Pattern = "\s(0[1-9]|1[012])/(0[1-9]|[12][0-9]|3[01])\s"  ' match " mm/dd "
        .Global = True
    End With
   
    fileNum = FreeFile
    Open textFile For Binary As #fileNum
    fileData = Space(LOF(fileNum))
    Get #fileNum, , fileData
    Close #fileNum
   
    For Each fileLine In Split(fileData, vbCrLf)
        Set matches = reMMDD.Execute(fileLine)
        If matches.Count = 1 Then
            amount = Split(Mid(fileLine, matches(0).FirstIndex + matches(0).Length + 1), " ")(0)
            info = Split(Mid(fileLine, matches(0).FirstIndex + matches(0).Length + 1), " ")(1)
            destCells.Offset(n).Value = Array(Left(fileLine, matches(0).FirstIndex), "'" & Trim(matches(0).Value), amount, info)
            n = n + 1
        End If
    Next
   
End Sub

Thank you very much for the code and your willingness to help; I think your code is exactly what I needed, so, I will go ahead and test this on the file: quick question on this: where do I run the code? Do I just open up the Visual Basic editor button and paste the code above in a new module/ or the active sheet?

Kind regards

 

[justlearning4]

I don't know why you didn't just add your XL2BB to your original post as requested by @rlv01, who would have been well placed to help you with this. By doing it as a separate post you have effectively excluded someone who has indicated that they are willing to assist you.

You can try the below on a copy of your workbook.

    Dim wsSrc As Worksheet, wsDest As Worksheet
    Dim rngSrc As Range, arrSrc As Variant
    Dim rngDest As Range
    Dim lrowSrc As Long
    Dim arrDest As Variant
    Dim destHdg As Variant
    Dim fullString As String, p1_desc As String, p2_sdate As String
    Dim p3_amt As Variant, p4_info As String
    Dim arrSplit As Variant
    Dim i As Long, j As Long, idest As Long
   
    Set wsSrc = Worksheets("NEGOTIATED_OFFERING_DATA")
    With wsSrc
        lrowSrc = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngSrc = .Range("A2:A" & lrowSrc)
    End With
   
    arrSrc = rngSrc.Value
    ReDim arrDest(1 To UBound(arrSrc), 1 To 4)
   
    Set wsDest = wsSrc                                      ' To facilitate changing output to a different sheet later
    Set rngDest = wsDest.Range("C1")
    destHdg = Array("Name", "Date", "Amount", "More Info")
   
    For i = 1 To UBound(arrSrc)
        If i = 1 Then
            fullString = arrSrc(i, 1)
        ElseIf arrSrc(i, 1) <> "" And Trim(arrSrc(i - 1, 1)) = "" Then
            fullString = arrSrc(i, 1)
        End If
       
        ' Split first row in each data set
        If fullString <> "" Then
        arrSplit = Split(fullString, " ")
            For j = 0 To UBound(arrSplit)
                If Len(arrSplit(j)) = 5 Then
                    If InStr(arrSplit(j), "/") Then
                        p2_sdate = arrSplit(j)
                        p3_amt = arrSplit(j + 1)
                       
                        p1_desc = Left(fullString, InStr(fullString, p2_sdate) - 2)
                        p4_info = Right(fullString, Len(fullString) - InStr(fullString, p3_amt) - Len(p3_amt))
                        fullString = ""
                        idest = idest + 1
                        arrDest(idest, 1) = p1_desc
                        arrDest(idest, 2) = p2_sdate
                        arrDest(idest, 3) = p3_amt
                        arrDest(idest, 4) = p4_info
                        Exit For
                    End If
                End If
            Next j
        End If
    Next i
   
    ' Output results
    With rngDest
        .Resize(, UBound(destHdg) + 1).Value = destHdg
        .Resize(, UBound(destHdg) + 1).Font.Bold = True
        .Offset(1, 1).Resize(idest, 2).NumberFormat = "@"
        .Offset(1).Resize(idest, UBound(destHdg) + 1).Value = arrDest
        .Offset(1).Resize(idest, UBound(destHdg) + 1).EntireColumn.AutoFit
    End With
End Sub

My apologies! I was having troubles downloading the XL2BB add-in on my Mac as most directions are for windows: at least that I am aware of. Thank you for developing this code, I will go ahead and give it a try, and come back and mark your reply as a solution if it works. I will keep in touch.

 

[Alex Blakenburg]

quick question on this: where do I run the code? Do I just open up the Visual Basic editor button and paste the code above in a new module/ or the active sheet?

For both code options just paste it into a new (or existing) module ("not" under sheet or ThisWorkbook). Then you can run it using Alt+F8 from within Excel or from the code or set up a button.

 

[justlearning4]

You don't explicitly say it, but if the 11/06 and 11/04 examples are MM/DD dates and they only occur in the lines you want to extract then a regular expression can be used to identify those lines and parse them into Excel.

This macro reads the text file directly (no need to import it or copy and paste it into Excel first) and parses the required data into 4 columns in the active sheet (modify the textFile string to suit).

Public Sub Import_Specific_Data_From_Text_File()

    Dim reMMDD As Object 'VBScript_RegExp_55.RegExp
    Dim matches As Object 'VBScript_RegExp_55.MatchCollection
    Dim textFile As String
    Dim fileNum As Integer
    Dim fileData As String, fileLine As Variant
    Dim destCells As Range, n As Long
    Dim amount As String, info As String

    textFile = "C:\path\to\THE TEXT FILE.txt"     'CHANGE THIS
   
    With ActiveSheet
        .Cells.ClearContents
        .Range("A1:D1").Value = Array("Name", "Date", "Amount", "More Info")
        Set destCells = .Range("A2:D2")
        n = 0
    End With
   
    Set reMMDD = CreateObject("VBScript.RegExp") 'New VBScript_RegExp_55.RegExp
    With reMMDD
        .Pattern = "\s(0[1-9]|1[012])/(0[1-9]|[12][0-9]|3[01])\s"  ' match " mm/dd "
        .Global = True
    End With
   
    fileNum = FreeFile
    Open textFile For Binary As #fileNum
    fileData = Space(LOF(fileNum))
    Get #fileNum, , fileData
    Close #fileNum
   
    For Each fileLine In Split(fileData, vbCrLf)
        Set matches = reMMDD.Execute(fileLine)
        If matches.Count = 1 Then
            amount = Split(Mid(fileLine, matches(0).FirstIndex + matches(0).Length + 1), " ")(0)
            info = Split(Mid(fileLine, matches(0).FirstIndex + matches(0).Length + 1), " ")(1)
            destCells.Offset(n).Value = Array(Left(fileLine, matches(0).FirstIndex), "'" & Trim(matches(0).Value), amount, info)
            n = n + 1
        End If
    Next
   
End Sub

Hi John, I had the time to run your code and it looks like I am getting an error message for the ".Cells.ClearContents" line. The error message that pops up says "Runtime Error 91: object variable or with block variable not set". Do you have any ideas why this may be?

 

[John_w]

I can't reproduce that error and don't know what could be causing it.

 

[justlearning4]

I can't reproduce that error and don't know what could be causing it.

Is it because there is not a range assigned to the clear contents function? As in, the range that we want to clear the contents of?

 

[Alex Blakenburg]

Is it because there is not a range assigned to the clear contents function? As in, the range that we want to clear the contents of?

The ".Cells" part is providing the range and tells it to use the whole sheet.

Please confirm you have put your code in Modules

Then using the VBA Button please post the actual code your are using.

PS: Did you try my code in post #8 ?

 

[John_w]

ActiveSheet.Cells.ClearContents also works in a sheet module, which is why I'm a bit mystified as to why you're getting that error.

Try deleting the .Cells.ClearContents line, as it isn't needed if the sheet is blank.