Hello,
I want to Import Multiple Files to a Single Workbook and there is code on Importing Multiple Files to a Single Workbook (Tips.Net) that does the job with one exception:

I want all the imported data to be formated as text and not general (I need leading zeros etc...). What to add to that code & where ? TIA

Hi

Without knowing you code, I suggest something like

ActiveSheet.Cells.NumberFormat = "@"

or based on the link you posted perhaps

.Worksheets(x).Cells.NumberFormat = "@"

??

This will format the whole sheet as text


MTB

Hi,

Rather than using the Workbook.Open() method to open the text file in Excel (as per the example on the link you provided), you can directly import the text file using a query table. To get an idea for the code you can follow these steps:

• Turn on the macro recorder
• Data | Import External Data | Import Data
• Browse to a text file and open it
• The text import wizard appears. Follow the steps choosing the approriate options until step 3.
• In step 3 you can customise the column data format. To preserve leading zeros you can select the relevant column(s) and choose Text format.
• Turn off the macro recorder.
• Review the code.

Then you need to understand the code and adapt it so it can be incorporated into your existing procedure.

Hope that helps...

after a while, here's the modified solution code:
----------

'http://excel.tips.net/Pages/T003148_Importing_Multiple_Files_to_a_Single_Workb ook.html

Sub CombineTextFiles()
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:="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)) *** below is forced text format

Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x), Format:=xlTextFormat)


wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns , _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:= _
xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma _
:=False, Space:=False, Other:=False, OtherChar:="|", _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2), Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 2), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 9))

' **** array forcing text format (2)



x = x + 1

While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns , _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:= _
xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma _
:=False, Space:=False, Other:=False, OtherChar:="|", _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2), Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 2), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 9))

' **** array forcing text format (2) although seems not necessary


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
----------------------------------------------------