Importing a text file using Application.GetOpenFilename

Question

I'm trying to import the full contents of a text file onto a specific sheet within a specific workbook. I can get it to work using the following code when importing another Excel file, but am having trouble figuring out how to do the same concept with a text file. I'm pretty new to VBA code so any help would be greatly appreciated!

Sample code I use daily for an excel import:

Sub Run_NEW_ROB()

    'IDeaS ROB Extract Import

    Cur_file = ActiveWorkbook.Name
    OldFN = Application.GetOpenFilename(, , "Select IDeaS ROB Extract")
    If LCase(OldFN) = "false" Then
    MsgBox "You Did Not Select A File - Stopping"

    Exit Sub
    Else

    Workbooks.Open Filename:=OldFN
    Set WBK = ActiveWorkbook
    End If

    Sheets("Hotel").Select
    Range("A1:Y366").Copy
    Windows(Cur_file).Activate
    Sheets("H_CUR").Select
    Range("A1").Select
    ActiveSheet.Paste
    WBK.Activate
    Application.CutCopyMode = False
    ActiveWindow.Close SaveChanges:=False
    Windows(Cur_file).Activate

    Workbooks.Open Filename:=OldFN
    Set WBK = ActiveWorkbook

    Sheets("Market Segments").Select
    Range("A1:K10221").Copy
    Windows(Cur_file).Activate
    Sheets("SEG_CUR").Select
    Range("A1").Select
    ActiveSheet.Paste
    WBK.Activate
    Application.CutCopyMode = False
    ActiveWindow.Close SaveChanges:=False
    Windows(Cur_file).Activate

End Sub

This should help: [Importing text file into excel sheet](http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet). — djikay, Jul 19 '14 at 03:08
You can use `Workbooks.OpenText` method. To see the syntax, record a macro while importing your text file. — L42, Jul 19 '14 at 03:29

score 0 · Answer 1 · answered Jul 19 '14 at 14:35

Here is an example that uses GetOpenFilename to pick and open the destination workbook. It also uses GetOpenFilename to pick the text file.

The text is imported into a specific cell in a specific worksheet. The destination workbook is then saved and closed:

Sub OpenDemo()
    Dim b1 As Workbook, s As String, J As Long

'
'   Select and open the destination workbook
'
    s = Application.GetOpenFilename()
    Workbooks.Open s
    Set b1 = ActiveWorkbook
    Sheets("xxx").Select
'
'   Select and open the text file
'   put the data in B9 and down
'
    s = Application.GetOpenFilename()
    Close #1
    Open s For Input As #1
    J = 9
    Do While Not EOF(1)
        Line Input #1, TextLine
        Cells(J, "B") = TextLine
        J = J + 1
    Loop
    Close #1
'
'   Save and close the destination workbook
'
    b1.Save
    b1.Close

End Sub

score 0 · Answer 2 · answered Aug 12 '20 at 15:43

Simple version from @Gary's Student. This version does not promp to select the destination file, but does propmt to select a txt file from win explorer to import. It will import the txt file contents into the active worksheet in the active workbook,

Sub txtimport()
Dim b1 As Workbook, s As String, J As Long

s = Application.GetOpenFilename()
Close #1
Open s For Input As #1
J = 3
Do While Not EOF(1)
    Line Input #1, TextLine
    Cells(J, "A") = TextLine
    J = J + 1
Loop
Close #1
End Sub

Is it possible to add a link, so that readers can more easily find the passage you quoted in its original context? — Buster, Aug 12 '20 at 18:27

Importing a text file using Application.GetOpenFilename

2 Answers2