Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    13

    Unanswered: Format number as text on multiple files import to a Single Workbook

    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

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  4. #4
    Join Date
    Jun 2009
    Posts
    13
    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
    ----------------------------------------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •