Workbooks.OpenText Open File Dialog

breynolds0431

Board Regular
Joined
Feb 15, 2013
Messages
66
Hi. Trying to have files run the fixed-width automatically with Workbooks.opentext. The problem I'm running into is trying to get an open file dialog to open so the user can select the text file to convert. Below is what I have so far, but it stops running when the file opens to the import wizard.

Thanks for any help on this.

Code:
Sub CTConvert()

Dim myfile As String



Workbooks.OpenText filename:=myfile, _
        Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(1, 1), Array(14, 1), Array(24, 1), Array(33, 1), Array(41, 1), Array(55, _
        1), Array(65, 1), Array(66, 1), Array(74, 1), Array(82, 1), Array(102, 1), Array(112, 1), _
        Array(121, 1), Array(129, 1), Array(143, 1), Array(153, 1), Array(154, 1), Array(170, 1)) _
        , TrailingMinusNumbers:=True


myfile = GetFile(myfile)


Workbooks(myfile).Close
  
End Sub


Public Function GetFile(ByVal strPath As String) As String
    
    Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
    
End Function
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,308
This will prompt the user to select a text file. However, if the user clicks on Cancel, it exits the procedure.

Code:
Dim myfile As Variant

myfile = Application.GetOpenFilename( _
                                      FileFilter:="Text Files (*.txt), *.txt", _
                                      Title:="Select text file", _
                                      ButtonText:="Open")
                                      
If myfile = False Then Exit Sub

Note that you won't be able to close the workbook using Workbooks(myfile).Close since myfile contains the full path in addition to the filename. But if the workbook remains the active workbook after it has been opened, you can close it like this, assuming you don't want to save any changes that may have been made...

Code:
activeworkbook.Close SaveChanges:=False

Hope this helps!
 
Last edited:

breynolds0431

Board Regular
Joined
Feb 15, 2013
Messages
66
Hi Domenic. Thank you for the idea. However, after I added, I get a 1004 run-time for "Sorry, we couldn't find . Is it possible it was moved, renamed, or deleted?" Did I add your code right? See update below:

Code:
Sub CTConvert()

Dim myfile As Variant



Workbooks.OpenText filename:=myfile, _
        Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(1, 1), Array(14, 1), Array(24, 1), Array(33, 1), Array(41, 1), Array(55, _
        1), Array(65, 1), Array(66, 1), Array(74, 1), Array(82, 1), Array(102, 1), Array(112, 1), _
        Array(121, 1), Array(129, 1), Array(143, 1), Array(153, 1), Array(154, 1), Array(170, 1)) _
        , TrailingMinusNumbers:=True


myfile = Application.GetOpenFilename( _
                                      FileFilter:="Text Files (*.txt), *.txt", _
                                      Title:="Select text file", _
                                      ButtonText:="Open")
                                      
If myfile = False Then Exit Sub
  
End Sub
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,308
It should be the other way around...

Code:
Sub CTConvert()

    Dim myfile As Variant
    
    myfile = Application.GetOpenFilename( _
                                          FileFilter:="Text Files (*.txt), *.txt", _
                                          Title:="Select text file", _
                                          ButtonText:="Open")
                                          
    If myfile = False Then Exit Sub
      
    
    Workbooks.OpenText Filename:=myfile, _
            Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
            Array(0, 1), Array(1, 1), Array(14, 1), Array(24, 1), Array(33, 1), Array(41, 1), Array(55, _
            1), Array(65, 1), Array(66, 1), Array(74, 1), Array(82, 1), Array(102, 1), Array(112, 1), _
            Array(121, 1), Array(129, 1), Array(143, 1), Array(153, 1), Array(154, 1), Array(170, 1)) _
            , TrailingMinusNumbers:=True




End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,237
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top