Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Open Text File forcing use of the wizard (97)

    I often have to download text files from a mainframe that are then to be opened in Excel using the Text Import Wizard.

    To save time, I thought it would be useful to set an alternative file association for txt files so that I could right click and 'open in Excel' - rather than having to open Excel, use the 'Open File' dialogue to navigate to the folder, change the 'files of type' etc etc.

    My problem is that if I set the file association to excel.exe it bypasses the Text Import WIzard, and dumps each line in column A - Is there a way of forcing excel to use the Text Import Wizard when opening a file?

    Are there DDE settings I could use? - I can't see any switch settings that will force the use of the Wizard.

    I know I could simply use the 'text to columns' wizard after opening the file, but a one-click solution would be useful.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Text File forcing use of the wizard (97)

    You might do it like this:

    - in a new workbook:

    Open the Thisworkbook module and paste in this code:

    <pre>
    Option Explicit

    Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:00:01"), "ProcessTextfile"
    End Sub
    </pre>


    In a normal module, paste this code:


    <pre>Option Explicit

    Dim oWkbk As Workbook
    Function IsThereATextFile() As Boolean
    Dim bTxtfile As Boolean
    For Each oWkbk In Workbooks
    Select Case Right(oWkbk.Name, 4)
    Case ".prn"
    bTxtfile = True
    Case ".txt"
    bTxtfile = True
    Case ".csv"
    bTxtfile = True
    Case Else
    bTxtfile = False
    End Select
    If bTxtfile Then Exit For
    Next
    IsThereATextFile = bTxtfile
    End Function

    Sub ProcessTextfile()
    Dim sFilename As String
    If IsThereATextFile Then
    sFilename = oWkbk.FullName
    If MsgBox("You opened a textfile, Import it using the wizard?", vbYesNo) = vbYes Then
    oWkbk.Close False
    SendKeys sFilename & "~"
    On Error Resume Next
    Application.Dialogs(xlDialogImportTextFile).Show
    End If
    End If
    End Sub
    </pre>


    Save this new workbook in your XLSTART directory ( on my system:
    Cocuments and SettingsPieterseApplication DataMicrosoftExcelXLSTART)

    Now try to open a txt file by rightclicking as you described.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Text File forcing use of the wizard (97)

    Of course my solution only works when Excel has not been opened yet. If it needs to work in the case Excel is already open, it will need a class module that can handle Application events.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Text File forcing use of the wizard (97)

    I have attached a zipped workbook that uses an application level event. Like your workbook, it can be placed in the XLSTART folder.

    Notes:
    - The workbook is hidden, so you won't see it in Excel itself. You can inspect the code in the Visual Basic Editor.
    - If you start Excel by itself, it will not come up with a blank workbook any more.
    - XLSTART may be inside a hidden folder (Application Data) so make sure that you can see hidden files and folders in Windows Explorer.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Text File forcing use of the wizard (97)

    Darn, you beat me to it <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Text File forcing use of the wizard (97)

    I stole the core code from your post, so the credits go to you <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Open Text File forcing use of the wizard (97)

    Thanks guys, That looks like a good approach

    However my version of Excel ( 97 SR2) didn't seem to recognise the "xlDialogImportTextFile" Dialog name, so I got a "Variable not Defined" error whenever I opened Excel. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    After a quick search through the Microsoft knowledge base, I've found that using "xlDialogOpen" gives the desired result

    Many Thanks

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Open Text File forcing use of the wizard (97)

    I've amended the code after finding that opening any other spreadsheet on top of the open "*.txt" file asked me if
    I wanted to use the Wizard again.

    Edited to include error handler when opening Excel without an active workbook.
    <pre>Option Explicit

    Public oWkbk As Workbook
    Public clsApp As New clsEvent

    Function IsThereATextFile() As Boolean
    Dim bTxtfile As Boolean
    On Error GoTo ExitFunction

    Set oWkbk = ThisWorkbook.Application.ActiveWorkbook
    Select Case Right(oWkbk.Name, 4)
    Case ".prn"
    bTxtfile = True
    Case ".txt"
    bTxtfile = True
    Case ".csv"
    bTxtfile = True
    Case Else
    bTxtfile = False
    End Select
    ExitFunction:
    IsThereATextFile = bTxtfile
    End Function</pre>


    This now only asks for the file being opened.

    PS change the file attributes of the OpenText.xls to ReadOnly and you'll avoid the 'already in use'
    popup when opening another spreadsheet.

    Once again, many thanks for giving me 95% of a solution and inspiring me to play about with the code.

Posting Permissions

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