Solved: Import multiple text files as new worksheets

[RECrerar]

My requirement is to be able to import multiple (and amout between 1 and 20) text files each as a new worksheet in a workbook.

Currently I am using the follwoing to import the text file in the format that I need it in, but this creates a new workbook each time, which was fine, but now my requirements have changed.

 sFile = Application.GetOpenFilename("Text & Excel Files(*.txt; *.xls), *.txt; *.xls")
    
    If Right(sFile, 3) = "txt" Then
        Workbooks.OpenText Filename:= _
        (sFile), Origin _
        :=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True

I have had a quick look (not as extensive as i probably should have done, sorry) and found the following two links which seem to deal with this,

http://www.vbaexpress.com/forum/show...+new+worksheet

http://www.vbaexpress.com/kb/getarticle.php?kb_id=497

But they both seem to be dealing with importing multiple text files into the same worksheet, which I do not want. So is there a way to modify the above code so that it creates a new sheet each time rather than a new workbook?

Also as another small question, is there a limit to the number of sheets a workbook can contain and if so how much is it?

Regards

Robyn

[RECrerar]

PS. The first section that allows the selection of excel files can easily be ignored for this progess, sorry shouldn't have included it, it's necessary in the current version but probably won't be used in the new version

[RECrerar]

Wow a whole thread to myself.

Just thought I would post my efforts so far. The following code works (except for renaming the sheet), Was manly produced by using the macro recorder, so maybe not the neatest, if anyone has comments on how to improve it that would be great, else will assume that it is okay as it is.

Sub Test()
Dim Sfile As String
Dim count As Integer
Dim ans As String
count = 0
Do
    Sfile = Application.GetOpenFilename("Text Files(*.txt), *.txt")
    
    If Sfile <> "False" Then
    count = count + 1
    If count = 1 Then
        Workbooks.OpenText Filename:=Sfile, Origin _
        :=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
    Else
       ActiveWorkbook.Worksheets.Add
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Sfile, Destination:=Range("A1"))
            .Name = Sfile
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        ActiveSheet.Name = count * 1000
    End If
    
    ans = MsgBox("Import Another?", vbYesNo)
    If ans = vbNo Then Exit Do
    Else: Exit Do
    End If
      
Loop
FINISH:
End Sub

[figment]

are all the text files in the same folder? for if they are it would be easyer to load all the file paths into sfile right from the start rather then making each user enter one name at a time. other then that it looks like you have your answer. its not writen how i would wright it but thats not a bad thing.

[RECrerar]

Hmm, I don't know. I would like all the files to be in the same folder, not sure I can garantee in though. I would be interested to know how I would load them all into SFile and how I would then be able to import them though as this would be the ideal situation. I'm writing the part that generates the text files as well so sure I can force it that they must all be in the same folder

[RECrerar]

One mare thing I've noiticed, If I then save the file with a different name, it renames the first sheet, is there a way to stop it doing this?

(The sheets are all named as the name of their text file)

[RECrerar]

Hi just a quick prod. If I could garantee all the text files were in the same folder, how would I then load them all into Sfile so that the user wouldn't have to click on each file individually?

Aside// The comment in the last post about the changing sheet name has been resolved, this was a result of my trying to save the file in the wrong format.

[figment]

well there are a couple ways to get the files you need. the folowing function takes all the files in a designated folder and loads them into an excel sheet, it would need a bit of modification to load into an array, but the idea is there.

[VBA]Function list1(location, start)
a = 1
With Application.FileSearch
.LookIn = location
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
If (.Execute <> 0) Then
While a <= .FoundFiles.Count
b = .FoundFiles.Item(a)
If (Right(b, 3) = "dwg" Or Right(b, 3) = "DWG") Then
Worksheets("list1").Range("A" & start) = .FoundFiles.Item(a)
start = start + 1
End If
a = a + 1
Wend
End If
End With
list1 = start
End Function[/VBA]


or this code allows the user to select multiple files at a time. but they would have to input for each folder that the text files are in.

[VBA] Set fd = wdApp.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
.Filters.Add "All Files", "*.*", 1
.Show
olWin.Activate
a = 1
b = .SelectedItems.Count
ReDim files(1 To b) As String
For Each s In .SelectedItems
files(a) = s
a = a + 1
Next
End With[/VBA]

there are probibly a few ways that i dont know of, but this should get you started.

[RECrerar]

thanks, I like the look of the second method, will have a go and get back to you if I encounter any dificulties

[RECrerar]

Hi again, and sorry for the delay, decided to ignore this issue for a while, but have now come back to it.

So I am using the second lot of code that was posted, and just puting this at the start of my code, so that it still imports the text files in the same manner but does it for each file automatically.

Just a quick question.

What does the line [VBA]olWin.activate [/VBA] do?

This line always bugs when I run the code, so as a quick fix I just commented it out and the code still seems to work fine, so am a bit confused as to the perpose of thi line? If someone could explain it to me it would be appreciated

[figment]

sorry that a pointer to one of my windows, it's called in my code to reactivate the window, but has now affect on the file selection code.

[RECrerar]

No worries, thanks for the clarification. Your code is working for me and makes the whole file loading process much quicker, so thanks a lot.

Regards, Robyn