CODEWITHSUNDEEP
excelvba
Ajility
Ajility

Reputation: 567

Extract Text File data to worksheet

I want to paste the columns of this text file in my workbook.

USAF-WBAN_ID STATION NAME                   COUNTRY                                            STATE                          LATITUDE LONGITUDE ELEVATION
------------ ------------------------------ -------------------------------------------------- ------------------------------ -------- --------- ---------
404200 99999 AL AHSA                        SAUDI ARABIA                                                                       +25.285  +049.485   +0179.2

I want to pull 7 variables (7 columns)

USAF-WBAN_ID; STATION NAME; COUNTRY; STATE; LATITUDE; LONGITUDE; ELEVATION

Below is what I've tried in several variations, and results are not reliable.

Sub A1_StationID()

Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

MsgBox "Please select weather stn.txt document"

Dim vFileName

vFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")

Application.ScreenUpdating = False

Workbooks.OpenText Filename:=vFileName, _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(13, 1), Array(44, 1), _
    Array(127, 1), Array(136, 1), Array(147, 1), Array(154, 1)), _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, _
    Other:=False, TrailingMinusNumbers:=True

Set wbCopyFrom = Workbooks.Open(vFileName)

End Sub

Some of the output I get:
enter image description here

If I add a breakpoint on the second to last line of code, I get closer to the output I desire (with messed up columns).
enter image description here

Upvotes: 2

Views: 100

Answers (1)

Mike
Mike

Reputation: 644

First off - fantastic job posting working code and usable example data.

You were missing the array/field info designation for state and your final 3 columns were ~1/2 characters off. I removed the delimiter options as they don't matter if you're using xlFixedWidth.

Change

Workbooks.OpenText Filename:=vFileName, _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(13, 1), Array(44, 1), _
    Array(127, 1), Array(136, 1), Array(147, 1), Array(154, 1)), _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, _
    Other:=False, TrailingMinusNumbers:=True

to 

Workbooks.OpenText Filename:=vFileName, _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(13, 1), Array(44, 1), _
    Array(95, 1), Array(126, 1), Array(135, 1), Array(144, 1), Array(154, 1)), _
    TextQualifier:=xlDoubleQuote, TrailingMinusNumbers:=True

As a side-note Excel has a bad tendency to remember Text to Columns settings so if you were messing with those earlier, make sure they are set to standard settings.

Upvotes: 2

Related Questions