Include Browse Button in InputBox?

[ADeYoung]

I have VBA that goes out and opens a txt file and imports the data into Excel. The file name changes every month and in place of updating my code everytime I need to import the file, can I create an input box with a browse button to select the file?? This is what I have:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;I:\Recons\Inv Recon\2011 Inventory Recon\03-11\Uninvoiced_Receipts_Report_030411.txt" _
, Destination:=Range("$A$1"))
.Name = "Uninvoiced_Receipts_Report_030411"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 1, 9, 1, 1)
.TextFileFixedColumnWidths = Array(65, 20, 5, 20)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Thanks!

 

[VoG]

Try Application.GetOpenFilename

 

[ADeYoung]

Where would I enter that into my code? I tried to replace the "TEXT;...." with the GetOpenFilename...but it doesn't work.

Thanks again!

 

[ADeYoung]

Bump to top...does anyone know the answer to this?

 

[Ruddles]

At its simplest, you declare a string variable - say sFilename - then do something like this:-

sFilename = Application.GetOpenFilename
If sFilename = "False" Then Exit Sub

That puts the file name you select in sFilename (complete with full path), or if you cancelled out of the dialog box sFilename would just contain the word "False". (In this example I use it to leave the subroutine immediately but you could display a MsgBox, loop back round and insist the user selects a file, or whatever else.)

Then you'd use sFilename in the query as follows: instead of:-

Connection:= "TEXT;I:\Recons\Inv Recon\2011 Inventory Recon\03-11\Uninvoiced_Receipts_Report_030411.txt"

you'd code:-

Connection:= "TEXT;" & sFilename

Try that.

 

[ADeYoung]

That's exactly what I needed. I figured it out about 30 seconds ago...LOL
Thanks for the reply!

 

[Ruddles]

Application.GetOpenFilename has some useful options, so once you've got the above code working, try:-

sFilename = Application.GetOpenFilename( _
       FileFilter:="Text files (*.txt), *.txt, CSV files (*.csv), *.csv, All files (*.*), *.*", _
       FilterIndex:=1, Title:="Ade Young's Program: please select a file to open:-")

(There's an option which allows you to select multiple files and places all the selected filenames in an array, but you need to be a Level 2 Excel Wiz before you're allowed to use it.)

 

[ADeYoung]

LOL...thanks

 

[ADeYoung]

This is actually what I used before I got your answer:

Dim FileToImport

FileToImport = Application.GetOpenFilename(, , "Choose text file to import...", , False)
If FileToImport = False Then
Exit Sub
End If

Works great