How to copy the whole txt file to Excel

Question

I have a code which doesn't completely work:

Sub Import_TXT()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook

    Application.GetOpenFilename ("Text Files (*.txt), *.txt")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A1").Copy
        ThisWorkbook.Worksheets("BOM").Range("C1").PasteSpecial xlPasteValues
        OpenBook.colse False
    End If
End Sub

I am trying to paste all contents of a txt file to cell "C1" of the active workbook sheet "BOM"

By fixing code as suggested I got:

Sub Import_TXT()

Dim FileToOpen As Variant
Dim OpenBook As Workbook

FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).Cells.Copy
ThisWorkbook.Worksheets("BOM").Range("A1").PasteSpecial xlPasteValues
OpenBook.Close False
End If

End Sub

Which now pastes all the contents in cell "A1" when I need it in "C1". There's an error popping out when I change

ThisWorkbook.Worksheets("BOM").Range("A1").PasteSpecial xlPasteValues

to

ThisWorkbook.Worksheets("BOM").Range("C1").PasteSpecial xlPasteValues

Saying that I only can paste in "A1"

By simply changing

.cells

To

.UsedRange

I can define cell "C1" as range where to paste all the contents

Note that *"doesn't work"* is a pretty useless error description. Which error do you get and where would be the information we need, or what does your code do vs. what you expect it to do. — Pᴇʜ, Feb 23 '21 at 09:11

Pᴇʜ · Answer 1 · 2021-02-23T10:02:16.667

0

The issue is FileToOpen is always False because after Dim FileToOpen As Variant you don't put any value into this variable.

You probably meant to do

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

Also OpenBook.colse False has to be OpenBook.Close False (see the typo in colse).

Update after edit of the question:

you get that error message because you copy all cells of the entire sheet with OpenBook.Sheets(1).Cells.Copy. So they can only fit in the sheet where you paste if you start at A1 otherwise you it will exceed the borders of the sheet. The solution is not to copy all cells but only the cells you need.

Probably using OpenBook.Sheets(1).UsedRange.Copy can fix this (depending on how your data looks like).

edited Feb 23 '21 at 10:02

answered Feb 23 '21 at 09:12

Pᴇʜ

56,719
10
49
73

Updated the main post – Eduards Feb 23 '21 at 09:36
That makes sense, however doesn't answer the question of how to get the entire txt in the range I need. I did figure it out on my own just now simply by changing .cells To .UsedRange – Eduards Feb 23 '21 at 09:45
@Eduards I edited the answer so you can use this to mark it as solved. – Pᴇʜ Feb 23 '21 at 10:02
@Eduards that is a new question so please ask a new question for that including a proper description of the problem and at best a [mcve] to reproduce the issue. Note we cannot answer questions in comments properly. – Pᴇʜ Feb 23 '21 at 10:09

Robson Moreira · Answer 2 · 2021-02-23T10:37:23.093

I often import txt files into my workbooks, the following code might help you:

            sub Copy_TXT_To_My_Plan()
            dim TextFile as integer
            dim FilePath as string
            dim FileContent as string
            FilePath = "C:/Files/MyExampleTextFile.txt" 'put the complete path to your textfile here

            TextFile = FreeFile
            Open FilePath for Input as TextFile
                    FileContent = Input(LOF(TextFile), TextFile)
            Close TextFile

            'now you have your txt in the FileContent string
            'you could just use Plan1.cells(1,1).Value = FileContent or
            'you could split it in a line array, something like:

            dim MyLineArray() as String
            MyLineArray = Split(FileContent, VbCrlf) 'or VbNewLine
            'then you can loop through the content of your file
            End Sub

score 0 · Accepted Answer · answered Feb 24 '21 at 04:40

This code will loop through all text files in a folder, and import one file into one cell, then import another files into another cell, and so on and so forth.

Sub Import_All_Text_Files()

    Application.ScreenUpdating = False

    Dim thisRow As Long
    Dim fileNum As Integer
    Dim strInput As String

    Const strPath As String = "C:\your_path_here\"  'Change as required
    Dim strFilename As String

    strFilename = Dir(strPath & "*.txt")

    With ActiveSheet
        thisRow = .Range("A" & .Rows.Count).End(xlUp).Row

        Do While strFilename <> ""

            fileNum = FreeFile()
            Open strPath & strFilename For Binary As #fileNum
            strInput = Space$(LOF(fileNum))
            Get #fileNum, , strInput
            Close #fileNum

            thisRow = thisRow + 1
            .Cells(thisRow, 1).Value = strInput

            strFilename = Dir
        Loop
    End With

    Application.ScreenUpdating = True

End Sub

If you have just one file that you need to import, it will import only that one.

Thank you very much for your answer, that's a useful piece of code right there — Eduards, Feb 24 '21 at 16:09

How to copy the whole txt file to Excel

3 Answers3