problem with piecing 2 snippets of VBA code together

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112
I have 2 pieces of code I am trying to marry but without success. The first piece of code uses the GetOpenFilename to select a file. The second piece of code is from a recorded macro to import a text file.

Once the file is selected using the GetOpenFilename, I want the selected text file to be acted upon with the piece of code from the recorded macro.

Code:
Sub Import_Text_Report()
'
' Import_Text_Report Macro
'
    Dim Finfo As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FileName As Variant

'   Set up list of file filters
    Finfo = "Text Files (*.txt),*.txt," & _
            "Lotus Files (*.prn),*.prn," & _
            "Comma Separated Files (*.csv),*.csv," & _
            "ASCII Files (*.asc),*.asc," & _
            "All Files (*.*),*.*"

'   Display *.* by default
    FilterIndex = 5

'   Set the dialog box caption
    Title = "Select a File to Import"

'   Get the filename
    FileName = Application.GetOpenFilename(Finfo, _
       FilterIndex, Title)

'   Handle return info from dialog box
    If FileName = False Then
        MsgBox "No file was selected."
    Else
        MsgBox "You selected " & FileName
    End If

    Workbooks.OpenText FileName
    Origin :=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
        Array(5, 1), Array(11, 1), Array(30, 1), Array(37, 1), Array(46, 1), Array(61, 1)), _
        TrailingMinusNumbers:=True
End Sub

I need help with the following line:
Code:
Workbooks.OpenText FileName

The code following the above line is my recorded macro to import a text file but this is where my code breaks down. I tried unsuccessfully to alter the code from the recorded macro so that it would act upon the file selected using the GetOpenFilename.

Being a VBA beginner, I am not sure how to make this work. Any help would greatly appreciated.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Taht code looks OK but you need a comma and a line continuation characcter after FileName:

Code:
Workbooks.OpenText FileName, _ 
    Origin :=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _ 
        Array(5, 1), Array(11, 1), Array(30, 1), Array(37, 1), Array(46, 1), Array(61, 1)), _ 
        TrailingMinusNumbers:=True
 

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112
Andrew:
That was it! I knew it had to be something simple but I am still learning. Thank you sooooooo much for your help. I greatly appreciate it. Have a Great Day!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,862
Messages
5,544,726
Members
410,630
Latest member
JFORTH97
Top