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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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!
 

Forum statistics

Threads
1,140,941
Messages
5,703,299
Members
421,289
Latest member
java

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top