Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Default Settings for Data Import
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 -- Jim (a.k.a. ORTNPALMS) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Default Settings for Data Import
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 Jim wrote: 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 -- Jim (a.k.a. ORTNPALMS) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change default printer settings | Setting up and Configuration of Excel | |||
How do I change the default folder when I import data? | Excel Worksheet Functions | |||
Can you change the settings for an Import Wizard | Setting up and Configuration of Excel | |||
Change default import settings | Excel Discussion (Misc queries) | |||
[Excel] How do I change the default import external data settings. | Excel Discussion (Misc queries) |