import multiple text files to seperate sheets in the existing workbook

Question

I have an excel file (2013) (eg test.xlsm). The excel file contains sheets with graphs and pivot tables which are refreshed monthly, based on text files. I need a VBA code which can import multiple text files from my local drive (which I import from a server) and append them at the end (sheets named similar to text file names) in this excel file. Every month, when I import text files, it has to replace this data sheets with new files.

Problem:
I have found a VBA code in this link! It works perfectly fine. But my problem is it imports the data into a newly opened Workbook instead of existing Workbook.

Solution

I modified the lines from

Set wkbAll = ActiveWorkbook
wkbTemp.Sheets(1).Copy

to

Set wkbAll = ThisWorkbook
wkbAll.Activate
wkbTemp.Sheets(1).Copy After:=Sheets(wkbAll.Sheets.Count)

but I get error 1004, no data selected to format the data with delimiter

wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
    Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, Semicolon:=False, _
    Comma:=False, Space:=False, _
    Other:=True, OtherChar:="|"

Solution I have found the some questions similar to mine (like this one), but none of them worked for me.

Please help me to solve this problem.

Here is my code with changes

Sub copydata()

    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim sDelimiter As String


    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    sDelimiter = "|"

    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


    Set wkbAll = Application.ActiveWorkbook
    x = 1

    With Workbooks.Open(fileName:=FilesToOpen(x))
        .Worksheets(1).Columns("A:A").TextToColumns _
            Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
            Other:=True, OtherChar:="|"
        .Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count)
        .Close False
    End With

    x = x + 1

    While x <= UBound(FilesToOpen)
        With Workbooks.Open(fileName:=FilesToOpen(x))
            .Worksheets(1).Columns("A:A").TextToColumns _
                Destination:=Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, _
                Tab:=False, Semicolon:=False, _
                Comma:=False, Space:=False, _
                Other:=True, OtherChar:=sDelimiter
            .Sheets(1).Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count)

        End With
        x = x + 1
    Wend

    wkbAll.Save
ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Are you trying to format the data with the deliminator in the new sheet? — VBA Pete, Jan 17 '17 at 19:52
@VBA Pete,yes sir, if it can format in the temporary file and copy the data into my excel file, can also be helpful to me — lvars, Jan 17 '17 at 20:22
To better understand you code: Why did you activate the worksheet wkbAll.Activate? — VBA Pete, Jan 17 '17 at 21:32
Because the active workbook was **wkbTemp**, so i activated **wkbAll**. — lvars, Jan 17 '17 at 21:44
Would you mind sharing your full code? It is hard to follow how you changed the code. — VBA Pete, Jan 17 '17 at 21:46
Yes, please share your full code block so we can reproduce. Snippets are not helpful. — Parfait, Jan 17 '17 at 21:58
What kind of files are you selecting in the opening window that pops up when you run the code? — VBA Pete, Jan 17 '17 at 22:53
@VBAPete i select all text files (nearly 30 files). i am able to import the files and format the data with delimiter using the code given in the link. the only problem the all the sheets are imported to new workbook. But i need to import them to existing workbook as i mentioned in my problem. — lvars, Jan 18 '17 at 01:15

user3598756 · Accepted Answer · 2017-01-18T15:26:27.553

2

edited after OP's new request (see bottom of the answer)

change

wkbTemp.Sheets(1).Copy After:=Sheets(wkbAll.Sheets.Count)

to

wkbTemp.Sheets(1).Copy After:=wkbAll.Sheets(wkbAll.Sheets.Count)

thus you can also change the whole section:

Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
Set wkbAll = ThisWorkbook
wkbAll.Activate
wkbTemp.Sheets(1).Copy After:=Sheets(wkbAll.Sheets.Count)
wkbTemp.Close (False)

to

With Workbooks.Open(Filename:=FilesToOpen(x))
    .Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count)
    .Close False
End With

and get rid of wkbTemp variable at all

should you need to copy data into an existing worksheet of the same workbook, then substitute

With Workbooks.Open(Filename:=FilesToOpen(x))
    .Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count)
    .Close False
End With

with

With Worksheets("Data1") '<--| change "Data1" to your actual name of existing sheet where to paste data into
    .UsedRange.ClearContents
    Worksheets(1).UsedRange.Copy .Range("A1")
End With

edited Jan 18 '17 at 15:26

answered Jan 18 '17 at 07:02

user3598756

28,893
4
18
28

@Ivars, did you get through it? – user3598756 Jan 18 '17 at 13:58
Thanks a lot, After rewriting code to do **delimiter format** in the temperory file and then copy the data to my excel workbook along with the changes you have specified.The code is working as i would like it to be. Awesome. I reposted the full code with the changes. – lvars Jan 18 '17 at 14:59
Is it possible to copy the data into the same sheet instead of creating new one. For example if tthe file name is **Data1** , If i have a sheet name called **Data1** in the workbook, is it possible to clear the contents of the sheet and update the sheet with new data – lvars Jan 18 '17 at 15:03
Yeah but i have multiple data sheets like Data1,Data2,Data3. Data1 text file should copy into Data1 sheet and Data2 file into Data2 sheet and so on. Tahnk you once again – lvars Jan 18 '17 at 17:14
then just use `With Worksheets(textFileName)`, where `textFileName` is a variable storing the current text file name – user3598756 Jan 18 '17 at 17:23
Sorry can you show me how to do that, i am rteally new to VBA but in the process of learning. But what about the delimiter format.Is it possible for you to edit my code and update it? – lvars Jan 18 '17 at 17:40
The above code for copying into same sheet omits the delimiter format. – lvars Jan 18 '17 at 19:54
Is there a solution for my second problem? Thanks once again – lvars Jan 19 '17 at 14:51
please make a _new_ post for _new_ question. be sure to add all relevant info and examples along with your code attempts specifying what's wrong with it – user3598756 Jan 19 '17 at 14:58
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/133568/discussion-between-lvars-and-user3598756). – lvars Jan 19 '17 at 15:20

import multiple text files to seperate sheets in the existing workbook

1 Answers1

Linked