Macro Assistance

[pratiksuhasaria]

While importing a text file with city, states, and zip codes, i run into an issue during my data review because Excel is dropping the leading 0’s from the zip codes. How can be this fixed so that leading 0's does not get drop? here is the code on which i was trying.

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 <= 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

 

[Misca]

Welcome to the message board!

Try adding the datatypes to your code:

FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))

The problem with datatypes is you should have fixed number of columns + the data must be in the same order (at least according to data type). In this case it seems like you can set everything to text.

 

[pratiksuhasaria]

Hi Misca

Not possible to have fixed number of columns + the data may vary depending on file.
So i need something that would directly import text ile in excel without making any changes

 

[Joe4]

So, are you saying that you may have a different number of columns with each file (you do not know how many columns the file will have)?
If so, determine what is the most possible number of columns that the file may have, and then add misca's suggestion for that number of fields, i.e.
if your file will never have more than 5 columns, then try:

FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4,2), Array(5,2))

adjust accordingly.

The issue is if you do not tell it the data type of each particular field, Excel is going to try to "guess" and figure it out itself. Since your Zip Codes are numeric, it thinks they are numeric entries and using the General or Number data type. Leading zeroes have no meaning for numeric values, so they will be dropped. So you need/want to import those fields as Text so the leading zeroes are not dropped. That is what misca's code does. It tells Excel that each field is to be brought it as Text, not General or Number.

 

[pratiksuhasaria]

Thanks Joe4 & Misca for u help it worked.

 

[Joe4]

it worked

Excellent! Glad to hear it!