Hopefully a quick one (please help!)

Mattman55

New Member
Joined
Dec 16, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,951
.
Here is a small project I use ...

VBA Code:
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.
 
Solution

Mattman55

New Member
Joined
Dec 16, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
.
Here is a small project I use ...

VBA Code:
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,992
Messages
5,622,029
Members
415,874
Latest member
JockPC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top