Macro Assistance

pratiksuhasaria

New Member
Joined
Mar 26, 2019
Messages
24
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Welcome to the message board!

Try adding the datatypes to your code:
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.
 
Upvote 0
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
 
Upvote 0
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:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,276
Messages
6,171,138
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top