Importing Multiple Text Files

[Felicity Shagwell]

I have a Workbook (hereinafter called Workbook 1) in which I want the user to
be able to search for 4 tab-delimited text files and import them, each as a
separate worksheet.

So far, I've been able to use a couple of macros (downloaded from the
Internet - see below).

The first macro will allow the user to browse for the text files and combine
them into a single NEW workbook (hereinafter called Workbook 2) where each
text file is in a separate worksheet.

I then have to have the user save and close Workbook 2 - I have no macro for
this; I just have to give the user instructions to save and close Workbook 2
I've found this saving and closing of Workbook 2 to be essential, otherwise
it seems to create problems with filenames when repeating the operations.

The second macro then allows the user to browse for Workbook 2 and import
its worksheets into Workbook 1.

But what I'd really like is for the above two operations to be combined into
one. In other words, for the user to search for the text files and import
them directly into Workbook 1.

I'm not that familiar with VB but am okay with recording macros. Any help
would be sincerely appreciated. Here are the macros I've been referring to:

First Macro; Getting and combining text files into Workbook 2:
Sub A_CombineTextFiles()

' Macro obtained by Felicity Shagwell
' on May 28 2007 from:
'
http://exceltips.vitalnews.com/Pages/T1281_Importing_Multiple_Files_to_a_Single_Workbook.html

Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp 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:="Browse and Select Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
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:="|"
x = x + 1

While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.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:=sDelimiter
End With
x = x + 1
Wend

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

ErrHandler:
MsgBox Err.Description
Resume ExitHandler

End Sub

Second macro; importing worksheets from Workbook 2 into Workbook 1:
Sub B_CombineWorkbooks()

'Obtained by Felicity Shagwell on May 29 2007 from"
'http://exceltips.vitalnews.com/Pages/T1123_Merging_Many_Workbooks.html

Dim FilesToOpen
Dim x As Integer

On Error GoTo ErrHandler
Application.ScreenUpdating = False

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(x)
Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

 

[JMB]

Perhaps this will help some:

Sub A_CombineTextFiles()

' Macro obtained by Felicity Shagwell
' on May 28 2007 from:

'http://exceltips.vitalnews.com/Pages/T1281_Importing_Multiple_Files_to_a_Single_Workbook.html

Const sDelimiter As String = "|"
Dim FilesToOpen
Dim x As Integer
Dim wksActive As Worksheet

On Error GoTo ErrHandler
Application.ScreenUpdating = False

Set wksActive = ActiveSheet

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Browse and Select Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

For x = LBound(FilesToOpen) To UBound(FilesToOpen)
Workbooks.OpenText _
Filename:=FilesToOpen(x), _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="|"

With ThisWorkbook
ActiveWorkbook.Sheets(1).Move After:=.Sheets(.Sheets.Count)
End With
Next x

ExitHandler:
wksActive.Parent.Activate
wksActive.Activate
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler

End Sub

 

[Felicity Shagwell]

Thank you so much, JMB. That worked exactly as I wanted it to. You've made
my day!

 

[JMB]

glad to hear that helped, thanks for the feedback

 

[Felicity Shagwell]

Whoops! I just noticed that, although it imported all the files into
Workbook 1 and put them into separate worksheets, the data in each worksheet
is all in one column. I guess there's something wrong with the way in which
the delimited file is being imported - is the macro recognising the tab
delimiters?

 

[Felicity Shagwell]

Further to my last post, I think I found the offending syntax in the macro:

I changed, "Tab:=False, _" to "Tab:=True, _", and it seems to work fine.
I'm still a bit puzzled as to why the syntax, "Tab:=False, _" didn't seem to
stop the files coming in with separate columns when I used the original
macros. Anyway, it seems to be doing it fine now.

 

[JMB]

the code I posted used "|" (pipe symbol) as the delimiter (that is what was
used in your original code), but it sounds like you are getting it
straightened out.

If your data could have | in it, you should change the "other" argument to
False. And, although it is moot at this point, I intended to use
"sDelimiter" for the OtherChar argument instead of hardcoding the "|" in the
code.

Workbooks.OpenText _
Filename:=FilesToOpen(x), _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:=sDelimiter

 

[Felicity Shagwell]

Yes, I think I got it now. I don't completely understand some of the syntax
but I get the drift. All seems to be working fine now. I'm very grateful
for your guidance. Thanks very much