Importing multiple text files and replace contents

Question

Hi all, 

I managed to find a piece of code that imports a series of .txt files into separate worksheets, naming each worksheet after the imported file name.

The code (at the bottom of the post) is working fine if I use an empty workbook, however, I would like to import these files into a workbook that already contains the  named WorkSheets, so I am looking at changing this code in order to replace the contents of the woksheets.

For Example, the text files available are "X1.txt" and "X2.txt".

I want the contents of "X1.txt" to replace the contents of the WorkSheet X1 and the contents of "X2.txt" to replace the contents of the Woksheet X2.

Is anyone able to help with this?

Thanks!

Mariana

Sub ImportData()

Dim FilesToOpen

Dim x As Integer

Dim DestWB As Workbook

Dim wkbTemp As Workbook

Set DestWB = Workbooks("Book1.xlsm")

On Error GoTo ErrHandler

Application.ScreenUpdating = False

FilesToOpen = Application.GetOpenFilename _

    (FileFilter:="Text Files (*.txt), *.txt", _

    MultiSelect:=True, Title:="Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then

    MsgBox "No Files were selected"

    GoTo ExitHandler

End If

For x = 1 To UBound(FilesToOpen)

Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))

wkbTemp.Sheets(1).Copy DestWB.Sheets(1)

wkbTemp.Close (True)

    With DestWB

        .Sheets(1).Move After:=.Sheets(Sheets.Count)

        .Worksheets(.Sheets.Count).Columns("A:A").TextToColumns _

            Destination:=Range("A1"), DataType:=xlDelimited, _

            TextQualifier:=xlDelimited, _

            ConsecutiveDelimiter:=True, _

            Tab:=True, Semicolon:=True, _

            Comma:=True, Space:=True, _

            Other:=True, OtherChar:=","

    End With

Next

ExitHandler:

Application.ScreenUpdating = True

Set wkbAll = Nothing

Set wkbTemp = Nothing

Exit Sub

ErrHandler:

MsgBox Err.Description

Resume ExitHandler

End Sub

Answer 1

Hi,

try this... 

using 'folder picker' method

select One folder ect, ect...

Sub Alpha_A()

'May 20, 2014

Dim myWB As Workbook, wb As Workbook

Set myWB = ThisWorkbook

Dim fPath As String, fName As String

On Error Resume Next

With Application.FileDialog(msoFileDialogFolderPicker)

.Title = "please, select One folder"

.AllowMultiSelect = False

If .Show = True Then

fPath = .SelectedItems(1) & ""

fName = Dir(fPath & "*.txt")

If fName <> "" Then

Application.ScreenUpdating = False

Do Until fName = ""

Application.DisplayAlerts = False

myWB.Sheets(fName).Delete

Application.DisplayAlerts = True

Workbooks.OpenText Filename:=fPath & fName, DataType:=xlDelimited, Tab:=True

Set wb = ActiveWorkbook

wb.Sheets(1).Copy after:=myWB.Sheets(myWB.Sheets.Count)

myWB.Sheets(myWB.Sheets.Count).Name = fName

wb.Close False

fName = Dir()

Loop

Application.ScreenUpdating = True

Else

MsgBox "no files"

End If

Else

MsgBox "Cancel"

End If

End With

End Sub

Answer 2

Hi mtlee27,

Assuming that a txt file already exists as xlsx (in a spreadsheet)

in my opinion,

it's more practical  to delete the 'old' sheet than to compare

the old (xlsx) with the new (txt)

Answer 3

This code was perfect.  Simple enough for me to implement and worked as expected.  I am trying to figure out how to modify it to accommodate the following functionality.  

Currently it creates a copy of each sheet that is existing in the workbook that is performing the importing.  Ideally, if it found an existing sheet in the workbook that matches a xlsx file in the folder it would update those values and not create a copy of the sheet. 

It would copy in any new xlsx files as new sheets in the workbook as it does now.  

Any help would be appreciated.  

I have found this reference, but unable to get it implemented correctly. 

https://msdn.microsoft.com/en-us/library/office/ff195098.aspx

Answer 4

Hi Martimpana,

I am trying to perform a similar task - import data from multiple .txt files, each into a separate sheet in an excel book.

Could you help me identify where in the code I need to specify the location of my folder containing the txt files that should be imported into each excel sheet?

Many thanks for any help!

Answer 5

Thanks TasoK!

The code works great! Exactly what I was looking for.