Hopefully a quick one (please help!)

[Mattman55]

I'm trying to import data from a text file (it has an .xer extension but it's just a text file) into a worksheet.

I found most of the code below on-line but can't seem to make it work. It lets me select the file, runs without errors, but I'm left with a blank worksheet at the end. I think I need some looping code to walk through each line of the text file and copy it to the worksheet(?) My text file looks like this and my code is below it. Thank you so much for taking a look.

ERMHDR 18.8 2020-12-16 Project tpaxton Troy Paxton dbxDatabaseNoName Project Management USD
%T CURRTYPE
%F curr_id decimal_digit_cnt curr_symbol decimal_symbol digit_group_symbol pos_curr_fmt_type neg_curr_fmt_type curr_type curr_short_name group_digit_cnt base_exch_rate
%R 1 2 $ . , #1.1 (#1.1) Dollar USD 3 1
%R 10 2 $ . , #1.1 (#1.1) Argentine Peso ARS 3 3.077
%R 11 2 A$ . , #1.1 (#1.1) Australian Dollar AUST 3 1.208
%R 13 2 R$ . , #1.1 (#1.1) Brazilian Real BRL 3 2.014
.... (and so on)

Sub ImportXerFile()
'Import a Primavera .xer file.
Dim vFileName
Dim textline As String
Dim Text As String
On Error GoTo ErrorHandle

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

If Right(vFileName, 3) <> "xer" Then
GoTo BeforeExit
End If

Application.ScreenUpdating = False

Workbooks.OpenText Filename:=vFileName, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:="-"

BeforeExit:
Application.ScreenUpdating = True
Exit Sub

ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

 

[Logit]

.
Here is a small project I use ...

Option Explicit

Sub ImportTXTFiles()
    Dim importrow As Long
    Dim fso As Object
    Dim xlsheet As Worksheet
    Dim qt As QueryTable
    Dim txtfilesToOpen As Variant, txtfile As Variant

    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")

    txtfilesToOpen = Application.GetOpenFilename _
                 (FileFilter:="Text Files (*.txt), *.txt", _
                  MultiSelect:=True, Title:="Text Files to Open")

    With ActiveSheet

        For Each txtfile In txtfilesToOpen

            importrow = 1 + .Cells(.Rows.Count, 1).End(xlUp).Row
            
            With .QueryTables.Add(Connection:="TEXT;" & txtfile, _
              Destination:=.Cells(importrow, 1))
                .TextFileParseType = xlDelimited
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "|"
                .Refresh BackgroundQuery:=False
            End With


        Next txtfile

        For Each qt In .QueryTables
            qt.Delete
        Next qt

    End With

    Application.ScreenUpdating = True
    MsgBox "Successfully imported text files!", vbInformation, "SUCCESSFUL IMPORT"

    Set fso = Nothing
End Sub

You'll need to do minor editing of the macro so it matches your file extension.

If the above doesn't work for you, please post a small example of the XER file you are working with. Since you can't post a file here, you will need to use
a cloud website and provide the download link here.

 

[Mattman55]

.
Here is a small project I use ...

Option Explicit

Sub ImportTXTFiles()
    Dim importrow As Long
    Dim fso As Object
    Dim xlsheet As Worksheet
    Dim qt As QueryTable
    Dim txtfilesToOpen As Variant, txtfile As Variant

    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")

    txtfilesToOpen = Application.GetOpenFilename _
                 (FileFilter:="Text Files (*.txt), *.txt", _
                  MultiSelect:=True, Title:="Text Files to Open")

    With ActiveSheet

        For Each txtfile In txtfilesToOpen

            importrow = 1 + .Cells(.Rows.Count, 1).End(xlUp).Row
           
            With .QueryTables.Add(Connection:="TEXT;" & txtfile, _
              Destination:=.Cells(importrow, 1))
                .TextFileParseType = xlDelimited
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "|"
                .Refresh BackgroundQuery:=False
            End With


        Next txtfile

        For Each qt In .QueryTables
            qt.Delete
        Next qt

    End With

    Application.ScreenUpdating = True
    MsgBox "Successfully imported text files!", vbInformation, "SUCCESSFUL IMPORT"

    Set fso = Nothing
End Sub

You'll need to do minor editing of the macro so it matches your file extension.

If the above doesn't work for you, please post a small example of the XER file you are working with. Since you can't post a file here, you will need to use
a cloud website and provide the download link here.

Genius! That actually seems to have worked! Thank you very much!!!

 

[Logit]

Ok ... you are welcome.