Importing tables from text file into excel with VBA

D89 · May 14th 2022

I'm trying to import many tables into excel (with fixed width option selected) and I would like it to paste it to certain areas as a function of "i", see below: For i = 0 to X

I would like "X" to be the number of tables that are imported from the text file, I think the Count function can be used here but I'm not sure how

So far I have this code written up, but I'm not sure what the syntax is for the commends I posted after some of the commands:

Code

Sub ImportLPileTextFile()
    Dim myFile As String, text As String, textline As String, pos1 As Integer, pos2 As Integer

    myFile = Application.GetOpenFilename()
        
    Open myFile For Input As #1
        
    Do Until EOF(1)
        Line Input #1, textline
        text = text & textline
    Loop
    Close #1
        
    pos1 = InStr(text, "y, inches p,lbs/in ")
    pos2 = InStr(text, "BLANK LINE") 'can I set this second condition to be "if two blank lines appear" somehow?
                                       'There are a minimum of 2 open spaces after the table ends, typical of each table. See photo

     For i = 0 To 'Count(how many items are pasted)
     
         Range(.Cells(8, 3 * i + 1)).Value = Mid(text, pos1 + 0, 0) ' is 0 right? I want to include this in what I want copied, see photo example
         'How do i paste this table as a special paste with "Fixed width" option?
                
     Next i
End Sub

Display More

I know the i = 0 to ... loop is not in the right area, it needs to be before Loop for Do Until ? Right?

Below are two attachments:

Of what the file would look like after it's pasted into my ActiveSheet (where the macro will be located). I've placed notes in the image to show the purpose of my i = 0 to ... function. Note the "paste data below..." is where I'd like the data pasted.
The actual text file and the exact data I need to pull from.

When I copy from the text file and paste special into excel with "Fixed Width" option, then it pastes perfectly with the two columns as shown in my excel image above.

Thanks in advance for taking the time to look at this and giving me advice and direction.

Worst case scenario, the tables I want will all always have "y,inches p,lbs.in" above them, so I can always use that as the first string to look for, I don't necessarily have to import those in my excel, I can manually input them and have the actual numbers be the data that's copied. At the end of each table there is at least 1 indented line with no characters so can the Mid() function pick from "y,inches p,lbs.in" to first line with no data? Any help on this code is appreciated.

**pike** · May 29th 2022

Try...

Code

Option Explicit
Sub ImportLPileTextFile()
    Dim myFile As String, txtLine As String, blnCopy As Boolean, n As Long, e As Long

    myFile = Application.GetOpenFilename()
    e = 1
    Open myFile For Input As #1

    Do Until EOF(1)
        Line Input #1, txtLine
        If txtLine Like "   y, inches         p, lbs/in   " Then
            blnCopy = True
            e = e + 3
        ElseIf Len(txtLine) < 1 Then
            blnCopy = False
            n = 1
        End If

        If blnCopy Then
            n = n + 1
            Cells(n, e - 2).Value = Trim(Right(txtLine, 18))
            Cells(n, e - 3).Value = Trim(Left(txtLine, 18))
        End If

    Loop
    Close #1
    MsgBox "Table Count :" & (e - 1) / 3, vbInformation, "Table Count"
End Sub

Display More

**pike** · May 30th 2022

with skip lines for the data sets only

Code

Option Explicit
Sub ImportLPileTextFile()
    Dim myFile As String, txtLine As String, blnCopy As Boolean, nRow As Long, eCol As Long, iCount As Long
    myFile = Application.GetOpenFilename()
    eCol = -2
    Open myFile For Input As #1
    Do Until EOF(1)
        Line Input #1, txtLine
        If txtLine Like "   y, inches         p, lbs/in   " Then
            blnCopy = True
            eCol = eCol + 3
            iCount = 1
        ElseIf Len(txtLine) < 1 Then
            blnCopy = False
            nRow = 1
            iCount = 0
        Else
            iCount = iCount + 1
        End If
        If blnCopy And iCount > 2 Then
           Sheet1.Cells(nRow, eCol).Value = Trim(Right(txtLine, 16))
           Sheet1.Cells(nRow, eCol + 1).Value = Trim(Left(txtLine, 16))
           nRow = nRow + 1
        End If
    Loop
    Close #1
    MsgBox "Table Count :" & (eCol + 2) / 3, vbInformation, "Table Count"
End Sub

Display More

Importing tables from text file into excel with VBA

Participate now!

Share

Similar Threads

Remove Duplicates from range of cells in excel vba

File Splitter Add-In

Changing file name and dates VBA macro

Create excel & Text file based on filter data save to a specific location

Create Outlook Item with specific set of pdf files from specific folder for specific emails

Extract Specific Text from a Cell

Tags