Combining all workbooks into one.

[gjsala]

So far my code will allow me to select text files and convert them into excel workbooks, one text file = one workbook. There may be times when 20 text files are selected and other times 10. The part I'm stuck with is taking all of the workbooks and combining all of the information into one workbook calling it "Master" and then deleting the workbooks not needed. Any help would be appreciated. Thanks in advance! Here is the code so far:

Application.ScreenUpdating = False
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
'   Default filter to *.*
    FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Select Start Drive & Path
ChDrive ("J")
ChDir ("J:\dell\use\reports\test")
With Application
    ' Set File Name Array to selected Files (allow multiple)
    FileName = .GetOpenFilename(Filter, FilterIndex, Title, , True)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(FileName) Then
    MsgBox "No file was selected."
    Exit Sub
End If
' Open Files
For i = LBound(FileName) To UBound(FileName)
    'msg = msg & Filename(i) & vbCrLf ' This can be removed
    Workbooks.Open FileName(i)
    Set wsThis = ActiveSheet
    Set myRange = wsThis.Range("A1:A5000")
    numR = myRange.count
        For r = 1 To numR + 1
            If (wsThis.Cells(r, 2)) <> "Error" Then
                    Rows(r).Select
                    Selection.Delete Shift:=xlUp
                    r = r - 1
            End If
            If (wsThis.Cells(r + 1, 2)) = "" Then
                    Exit For
          End If
        Next r
        Cells.Select
        Cells.EntireColumn.AutoFit
        Range("A1").Select
        If Cells(1, 1) = "" Then
            ActiveWindow.Close False
        End If
Next i
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Hi,

Take a look at Data/Get External Data/Import Data.

You can do one of these on each sheet.


Skip,

_[red][/red]

 

[gjsala]

Skip,
Thanks for the response! Do you have an example of the Data/Get External Data/Import Data?

Thanks.

 

[SkipVought]

Have you tried Data/Get External Data/Import Data?

Have you tried HELP? MS actually has a training video on this subject.

Skip,

_[red][/red]

 

[SkipVought]

audio, sorry!

Skip,

_[red][/red]