Open Text File forcing use of the wizard (97)

Topic

New Reply

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.

Reply | Quote

Replies

You might do it like this:

– in a new workbook:

Open the Thisworkbook module and paste in this code:

Option Explicit

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

In a normal module, paste this code:

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

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

Now try to open a txt file by rightclicking as you described.

Reply | Quote

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.

After a quick search through the Microsoft knowledge base, I’ve found that using “xlDialogOpen” gives the desired result

Many Thanks

Reply | Quote

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.

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

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.

Reply | Quote

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.

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

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.

Reply | Quote

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.

After a quick search through the Microsoft knowledge base, I’ve found that using “xlDialogOpen” gives the desired result

Many Thanks

Reply | Quote

You might do it like this:

– in a new workbook:

Open the Thisworkbook module and paste in this code:

Option Explicit

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

In a normal module, paste this code:

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

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

Now try to open a txt file by rightclicking as you described.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Darn, you beat me to it

Reply | Quote

I stole the core code from your post, so the credits go to you

Reply | Quote

I stole the core code from your post, so the credits go to you

Reply | Quote

Darn, you beat me to it

Reply | Quote

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.

Reply | Quote