template for opening delimited text file

J

Jerry h

Can I create something like a template for reading a tab-delimited file? I have an application that requires the user to opening a tab-delimited file frequently. Not all the columns in the file are to be read. One column of the original is a date but its format is DMY (example 21/04/04). Unless the user specify DMY in step 3 of the text import wizard, the data is not read correctly.

Though I could write some vba codes to automate this process, but I might have overlooked some Excel build-in functions (for example: something like a template that could be created in a user-friendly way) that already exist. Can someone advise
 
D

DNF Karran

using VBA for the whole import and then reformatting would be the onl
way i can think of as functions can't change data outside of their cel
and you want to delete ranges.

If you want ease of use you could create a custom button that will ope
the macro sheet for them and set things up so all they have to do i
selcet the right file.

Dunca
 
D

Dave Peterson

I think I'd start a new workbook and record a macro when I did it once manually.

Then I could just replay this macro whenever I wanted to import a file with the
same layout.

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:\My Documents\excel\Sample1.txt", Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
_
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

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).
(If the filename never changed, you wouldn't even need this change.)

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 text file.


=======
And keep recording to add as much as you want--titles, page setup, filters,
freezepanes, all that stuff.
 
Top