Excel VBA importing data value as text

RobotCarl 0
Viewed 31

I am new to VBA and I am trying to automate importing data into excel. The VBA tutorial got me 95% there but I am losing leading 0's. I am unsure of where or how to edit my VBA to ensure leading 0's are maintained. Column A is where Im losing the 0's but if it's simpler the entire file can be imported as text value.

My VBA is below.

Sub RSupplyOutput()

Dim fileToOpen As Variant
Dim filefilterpattern As String
Dim wsMaster As Worksheet
Dim wbtextimport As Workbook


Application.ScreenUpdating = False

filefilterpattern = "Text Files (*.txt; *.csv), *.txt; *csv"

fileToOpen = Application.GetOpenFilename(filefilterpattern)

If fileToOpen = False Then
    ' Input cancled
    MsgBox "No File Selected"
Else
    'we have a file to open
    
    Workbooks.OpenText _
                Filename:=fileToOpen, _
                StartRow:=2, _
                DataType:=xlDelimited, _
                Tab:=True, _
                FieldInfo:=Array(Array(1, xlTextFormat))
                                                         
                                                         
    Set wbtextimport = ActiveWorkbook
        
    'Set wsMaster = ThisWorkbook.Worksheets.Add
     Set wsMaster = ThisWorkbook.Worksheets("RSupply")
    
    wbtextimport.Worksheets(1).Range("A3").CurrentRegion.Copy wsMaster.Range("A3")
        
    wbtextimport.Close False
    
End If

Application.ScreenUpdating = True

End Sub
text vba excel
1 Answers

Here is Edited Code:

Sub RSupplyOutput()
Dim fileToOpen As Variant
Dim filefilterpattern As String
Dim wsMaster As Worksheet
Dim wbtextimport As Workbook
Application.ScreenUpdating = False
filefilterpattern = "Text Files (*.txt; *.csv), *.txt; *csv"
fileToOpen = Application.GetOpenFilename(filefilterpattern)
If fileToOpen = False Then
    ' Input cancled
    MsgBox "No File Selected"
Else    
    Workbooks.OpenText _
                Filename:=fileToOpen, _
                StartRow:=2, _
                DataType:=xlDelimited, _
                Tab:=True                 
    Set wbtextimport = ActiveWorkbook        
    'Set wsMaster = ThisWorkbook.Worksheets.Add
     Set wsMaster = ThisWorkbook.Worksheets("Sheet1")
     wbtextimport.Worksheets(1).Range("A3").CurrentRegion.Copy wsMaster.Range("A3")
    wsMaster.Columns("A").NumberFormat = "00000" '**Number of Zeros As per your original Data**        
    wbtextimport.Close False    
End If
Application.ScreenUpdating = True
End Sub
userQfzh0
Related