How do I combine these two?

[Steve]

Hello. I have 'borrowed' the following code to allow me to select the
text files I would like to open in excel:

Sub Combine()

Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.*),*.*", _
Title:="Select Files To Open", MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
''' GetFiles is Array of Strings (File Names)
''' File names include Path
nFiles = UBound(GetFiles)
For iFiles = 1 To nFiles
'' List Files in Immediate Window
Debug.Print GetFiles(iFiles)
Next
End If
End Sub

I then have 'borrowed' the following code to open files and paste the
sheet into a different worksheet in the same workbook:

Sub Multiple_Text_files()
' Get the list of files
With Application.FileSearch
.NewSearch
.LookIn = "C:\" '<== Alter to proper drive\directory
.SearchSubFolders = False
.fileName = "*.*" '<== All files
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.OpenText fileName:=.FoundFiles(i)
Set wkbk = ActiveWorkbook
ActiveSheet.Copy After:=ThisWorkbook.Worksheets(1)
'Note: Thisworkbook refers to the workbook the macro is running
from
wkbk.Close
Next i
Else
MsgBox "There were no files found."
End If

End With
End Sub

So somehow I need to combine the two, which would give the result of
allowing me to select the text files I would like to open in Excel,
and then have the sheet in each text file be copied to a new worksheet
in the 'Master' workbook.

Please help! Thanks in advance!!

 

[Bob Phillips]

Steve,

Like this

Sub Combine()
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim wkbk As Workbook

GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.*),*.*", _
Title:="Select Files To Open", MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
''' GetFiles is Array of Strings (File Names)
''' File names include Path
For iFiles = LBound(GetFiles) To UBound(GetFiles)
Workbooks.OpenText Filename:=GetFiles(iFiles)
Set wkbk = ActiveWorkbook
wkbk.ActiveSheet.Copy After:=ThisWorkbook.Worksheets(1)
'Note: Thisworkbook refers to the workbook the macro is
runningfrom
wkbk.Close

Next
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)