CODEWITHSUNDEEP
excelvba
globlobi2003
globlobi2003

Reputation: 29

Import from txt file to Excel as text type

Hi I gave a VBA code who imports in Excel some txt file and the do some text to columns and replace the. with nothing. I want some columns to be text format before I do the replacement.

How can I do that?

Here is my code:

Sub ImportFichierTxt()
    
    Dim ObjFSO As FileSystemObject
    Dim VarFileToOpen As Variant
    Dim WkNewWk As Worksheet
    Dim ObjFileToRead As Object
    Dim LngLine As Long
    Dim StrLine As String
    Dim fnd As Variant
    Dim rplc As Variant
    
    fnd = "."
    rplc = ""
    
    Application.ScreenUpdating = False
    
    VarFileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If VarFileToOpen = False Then Exit Sub
    Set WkNewWk = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    Set ObjFSO = CreateObject("Scripting.FileSystemObject")
    Set ObjFileToRead = ObjFSO.OpenTextFile(VarFileToOpen, ForReading)
    LngLine = 1
    Do While ObjFileToRead.AtEndOfStream = False
       StrLine = ObjFileToRead.ReadLine
       WkNewWk.Cells(LngLine, 1) = StrLine
       LngLine = LngLine + 1
    Loop
    ObjFileToRead.Close
 
    WkNewWk.Columns("A:A").TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
        1), Array(59, 1), Array(80, 1), Array(92, 1), Array(105, 1), Array(123, 1), Array(134, 1), _
        Array(146, 1), Array(165, 1)), TrailingMinusNumbers:=True
    
    WkNewWk.Columns.AutoFit
     
 
 

Dim Cell As Range, NoCol As Integer
Dim NoLig As Long, DerLig As Long, Var As Variant
 
 
    
    DerLig = Split(WkNewWk.UsedRange.Address, "$")(4)
 
    'Fixe le N° de la colonne à lire
    NoCol = 2
    
    
    

 
    
   For NoLig = 1 To DerLig
        Var = WkNewWk.Cells(NoLig, NoCol)
        
        If Var = "MOIS DE" Then

         With WkNewWk.Cells(NoLig + 1, NoCol)
        .NumberFormat = "m/d/yyyy;@"
        WkNewWk.Cells.Replace what:=fnd, Replacement:=rplc, _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
         End With
        
        End If

    Next

    Set WkNewWk = Nothing
    Set ObjFSO = Nothing
    Set ObjFileToRead = Nothing
    
    

    

End Sub

In the txt file the number format is something line 123.870, and when I import in the txt file the 0 at the end disappears.

Upvotes: 1

Views: 159

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Every column you get is determined in the code in this part:

Array(Array(0, _
        1), Array(59, 1), Array(80, 1), Array(92, 1), Array(105, 1), Array(123, 1), Array(134, 1), _
        Array(146, 1), Array(165, 1))

So Array(0, 1) is your first column, Array(59, 1) is the second one and so on.

Locate the column you want as text and change 1 to 2. So,a s example, if your target column is the second one, just do Array(59, 2) and it should be considered as Text

Upvotes: 1

Related Questions