LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default Format number as text on multiple files import to a SingleWorkbook

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
----------------------------------------------------
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to import multiple csv files in single excel file automaticall D Joshi Excel Programming 1 March 17th 08 03:27 PM
import multiple text files into single sheet separate column Jootje Excel Programming 10 February 8th 08 05:09 PM
Import of Multiple Text Files yanks6rule[_2_] Excel Programming 1 January 26th 06 11:34 PM
Import multiple text files into a single worksheet Scott Excel Discussion (Misc queries) 0 January 13th 05 10:22 PM
Importing multiple text files into single workbook Steve[_56_] Excel Programming 1 January 15th 04 08:18 PM


All times are GMT +1. The time now is 02:04 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"