Change Default Settings for Data Import

[Jim]

How can I change the default settings from importing data into Excel? For
example, the default setting for format is "fixed width" but most of the
datasets that I import are "Delimited." When I select "Delimited" the
default Delimiter is "Tabs" but my delimited datasets are Comma Delimited.

What would be really cool would be able to set the defaults according to
what template is being used, because sometimes I use a template where the
data are Fixed width delimited.

Thanks.

Jim

 

[Dave Peterson]

The default for me is Delimited.

If you close excel and reopen it, you'll see the default.

But if you do a data|text to columns or file|open and use the text import
wizard, you'll see that excel likes to help by remembering the settings that you
last used in that excel session. (Excel will forget those settings when you
close excel and reopen it, though.)

If you really want to have the options set the way you like, you could record a
macro when you do a "dummy" data|text to columns. If you run that macro, then
the next time you use data|text to columns or File|open a text file, you'll see
the settings that you chose.

But I wouldn't bother with this approach.

If the files you're opening are delimited by commas, you could use a dedicated
macro that asks you for the *.txt file and opens it using the settings you like.

I'd create a new workbook that contains the macro. And put a giant button from
the Forms toolbar on Sheet1 (along with instructions to the user). Then assign
this macro to that button:

Option Explicit
Sub testme01()
Dim fName As Variant
Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

fName = Application.GetOpenFilename(filefilter:="Text Files, *.txt")

If fName = False Then
Exit Sub 'user hit cancel
End If

maxFields = Worksheets(1).Columns.Count

ReDim myArray(1 To maxFields, 1 To 2)
For iCtr = 1 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 1 'general
Next iCtr

Workbooks.OpenText Filename:=fName, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=myArray

End Sub

If your data has fields that are not general, then you'll want to modify the
code so that each field is brought in correctly.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm