How to open a txt file with vba code and and copy its contents to excel?

Question

I need to open multiple txt files from same folder and copy its contents to a single excel file (like a template) to modify the values and then, I need to copy the modified values from excel to txt files. How can we do this VBA automation? any reply would be helpful.

Can you provide more information ? What do the data in the txt files look like ? Do you need to put the content of you txt file into a specific cell ? Which cells do you need to put back into a txt file ? — Teasel, Oct 09 '17 at 09:23
@Teasel Thanks for your concern.I need to copy the contents of txt file from its 4th line to excel file's 1st sheet (staring cell: A4), I have some formulas to modify the data of 1st sheet in 2nd sheet. Then the modified data from 2nd sheet need to get copy & paste into the same txt file's 4th line and then save it. Kindly look into it if you find time. — Divya, Oct 09 '17 at 10:39
I wrote an answer that should work. If that's the case accept the answer it'd be kind :) — Teasel, Oct 09 '17 at 11:09
@Teasel Thanks for your valuable reply. It works great with one file. For multiple files on same folder for the repetitive action what should I add there? You have any idea? and one more, i need to copy the contents of txt file from 4th line and paste it on excel file at staring cell A4? — Divya, Oct 09 '17 at 11:28
The answer on this post https://stackoverflow.com/a/45749626/5836929 will give you a good lead (it's what you need, you call you method in the loop) — Teasel, Oct 09 '17 at 11:34

score 2 · Accepted Answer · answered Oct 09 '17 at 11:05

You can open a file by using the following method (found there, adapt it!)

Sub OpenAndImportTxtFile()
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet

    Set wbI = ThisWorkbook
    Set wsI = wbI.Sheets("Sheet1") '<~~ Sheet where you want to import

    Set wbO = Workbooks.Open("path and name of your file")

    wbO.Sheets(1).Cells.Copy wsI.Cells

    wbO.Close SaveChanges:=False
End Sub

And export your sheet with the following method (found there)

Sub SaveFile()
Dim ans As Long
Dim sSaveAsFilePath As String

On Error GoTo ErrHandler:

sSaveAsFilePath = "path and name of your file"

If Dir(sSaveAsFilePath) <> "" Then
    ans = MsgBox("File " & sSaveAsFilePath & " exists.  Overwrite?", vbYesNo + vbExclamation)
    If ans <> vbYes Then
        Exit Sub
    Else
        Kill sSaveAsFilePath
    End If
End If

Sheet1.Copy '//Copy sheet to new workbook
ActiveWorkbook.SaveAs sSaveAsFilePath, xlTextWindows '//Save as text (tab delimited) file

If ActiveWorkbook.Name <> ThisWorkbook.Name Then '//Double sure we don't close this workbook
    ActiveWorkbook.Close False
End If

My_Exit:
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume My_Exit
End Sub

Call them with

OpenAndImportTxtFile
SaveFile

score 1 · Answer 2 · answered Oct 09 '17 at 09:17

1

There are pretty good guides I used to do the same work like you.

For Text to Excel:

http://www.excel-easy.com/vba/examples/read-data-from-text-file.html

vba: Importing text file into excel sheet

For Excel to Text:

http://www.excel-easy.com/vba/examples/write-data-to-text-file.html

Good luck

answered Oct 09 '17 at 09:17

KarmaWin

24
4

Thanks for your reply. – Divya Oct 09 '17 at 11:40

score 0 · Answer 3 · answered Oct 28 '17 at 12:37

It sounds like you want to merge all text files into one single file. How about this option?

Sub CombineTextFiles()
    Dim lFile As Long
    Dim sFile As String
    Dim vNewFile As Variant
    Dim sPath As String
    Dim sTxt As String
    Dim sLine As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .Show Then
            sPath = .SelectedItems(1)
            If Right(sPath, 1) <> Application.PathSeparator Then
                sPath = sPath & Application.PathSeparator
            End If
        Else
             'Path cancelled, exit
            Exit Sub
        End If
    End With
    vNewFile = Application.GetSaveAsFilename("CombinedFile.txt", "Text files (*.txt), *.txt", , "Please enter the combined filename.")
    If TypeName(vNewFile) = "Boolean" Then Exit Sub
    sFile = Dir(sPath & "*.txt")
    Do While Len(sFile) > 0
        lFile = FreeFile
        Open CStr(sFile) For Input As #lFile
        Do Until EOF(lFile)
            Line Input #1, sLine
            sTxt = sTxt & vbNewLine & sLine
        Loop
        Close lFile
        sFile = Dir()
    Loop
    lFile = FreeFile
    Open CStr(vNewFile) For Output As #lFile
    Print #lFile, sTxt
    Close lFile
End Sub

How to open a txt file with vba code and and copy its contents to excel?

3 Answers3