Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I combine these two?
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I combine these two?
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) "Steve" wrote in message om... 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine worksheets w/o enough rows to combine? | Excel Worksheet Functions | |||
Combine cells with the same reference and combine quantities | Excel Discussion (Misc queries) | |||
how to combine........ | Excel Discussion (Misc queries) | |||
How Can i combine | Excel Discussion (Misc queries) | |||
How do I combine IF and OR? | Excel Worksheet Functions |