title: Set the cell format to text in excel before importing
body: <p>i am importing multiple text files in excel using the Macro given at this site</p>
  
  <p>it is working but say for example u have data as 0010  it is changing  it to 10  i tried to modify the code by adding 
   <code>Destination:=Range("A1").NumberFormatLocal = "@"</code>    in the script but it is giving error 
  texttocoloums method of range class failed</p>
  
  <p>here is the original code</p>
  
  <pre><code>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))
      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 &lt;= 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
  </code></pre>