Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Rochester, NY
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Change import dialog default settings

    Is there a way to set the defaults used by the excel 2007 file open dialog.
    It defaults to Tab delimited.
    Most of my files are pipe delimited as in |
    I know I can change it from the dialog box, but after opening hundreds of these files, I am tired of constantly changing,
    I would like to make the default to Other: and the | filled in.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Ihogg welcome to the lounge.

    For this type of repeative work your best solution is VBA.

    Since I use Excel 2003 the attached workbook is an xls.

    However, the code should run in Excel 2007.

    I have also included the VBA procedures below.

    To use the Workbook

    1. In Cell C5 put the file path where the file or files you will delimit are located
    2. Hit the Box called "Download" It will run a Macro and list the files in the folder.
    3. On the Worksheet in Col B select the row that contains the file you want to convert.
    4. Hit the Box called "Create Delimited". It will run a Macro and parse the file based on "|"

    Hope it all works in Excel 7 and that it save you a lot of time.

    TD

    Sub GetFiles()
    '
    ' GetFiles Macro

    '
    Dim nfiles As String
    Dim nextRow As Integer 'Next row index
    Dim hold As String
    nextRow = 1
    If ActiveSheet.Range("B10").Value _
    = "" Then
    Range("B10").Select
    Else
    Range("B10").Select
    ActiveCell.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    End If
    hold = Range("b5").Value
    With ActiveCell
    nfile = Dir(hold, vbNormal)
    .Value = nfile
    Do While nfile <> ""
    nfile = Dir
    .Offset(nextRow, 0).Value = nfile
    nextRow = nextRow + 1
    Loop
    End With

    End Sub

    Sub DelFile()
    '
    ' DelFile Macro
    '
    Dim MyFile As String
    Dim MyPath As String

    'Create Variable using the value in the ActiveCell
    MyFile = ActiveCell.Value
    '
    'Create Variable using the value in the ActiveSheet wiht Path
    MyPath = ActiveSheet.Range("B5").Value
    '
    'Combine Variables into full path and file
    '
    MyFilePath = MyPath & MyFile
    '
    'Open the text file and delimit using Pipe
    'To change Delimiter Character modify OtherChar:="|" to new Chacter i.e. OtherChar:="?"
    Workbooks.OpenText Filename:=MyFilePath, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=True, OtherChar:="|"
    End Sub
    Attached Files Attached Files

  3. The Following User Says Thank You to duthiet For This Useful Post:

    lhogg (2011-03-04)

  4. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Rochester, NY
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    wow two cliks and I got my data,
    Outstanding!
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •