Importing tables from text file into excel with VBA

  • 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:

    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:

    1. 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.
    2. 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," 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," to first line with no data? Any help on this code is appreciated.

  • Try...

  • with skip lines for the data sets only

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!