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:
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
I know the
i = 0 to ... loop is not in the right area, it needs to be before
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.