dropping leading zeros on import

  • Thread starter Thread starter scameron
  • Start date Start date
S

scameron

I have a csv file that has leading zeros for zip codes. I
want to be able to open the file without manually switching
from General format to Text format every time it is opened.
Ay suggestions?
 
change the extension to .txt, then turn on the macro recorder and open it
manually. Make the appropriate setting for the column (treat it as text) in
the wizard and complete importing it.

Now you can use this code to bring in the file. You can rename it in the
code, use openText with the recorded settings.

If you leave it named with .csv extension, your settings will be ignored (at
least through xl2000).
 
You can work around that by using data>import external data, put *.* in the
file name
and select the CSV file. That way the text import wizard will open

(I believe older excel versions would be data>import external data>import
text file or something)
 
Thanks. Is there any way to have Excel automatically use
that macro when I double click the csv file in explorer or
when I pick it from excel's recently used list?
 
Not by clicking on the .csv file.

But you could record the macro and run that macro whenever you wanted to import
a like-formatted file (with extension .txt).

After you've recorded the macro, you'll probably want to adjust the code a
little to make it more generic. When you recorded your macro, you got something
that looked like:

Option Explicit
Sub Macro1()

Workbooks.OpenText Filename:="C:\myfile.txt", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15, 1), _
Array(41, 1))

End Sub

Well, instead of having your filename in the code, you can give the user a
chance to pick it themselves (take a look at getopenfilename in VBA's help):

Sub macro1A()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

I like to drop a big old button from the Forms toolbar on a worksheet in the
workbook that contains the code. I assign the macro to the button. And I add a
few notes to that worksheet.

Then just hit the big old button when I want to bring in my .txt file.
 
Back
Top