dialog box to retrieve a file

[TKW]

I am trying to insert in my Macro a dialog box that will prompt my user to
choose a file (within a given directory).

Does anyone have a solution for me, I have found 794 dialog boxes in the VBA
help files.

Which one should I be using?

 

[Shaun M. Angley]

TKW -
A little more info would be appreciated. Are you trying to get them to
choose a file or open a file? I don't which office program or version you
are using, but this may help if you're looking to open files with a dialog
with possibly a filter on the types of files it shows.

fname = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls),
*.xls")
ActiveWorkbook.ExclusiveAccess Filename:=fname

If that's not what you're looking for then provide some more info to help us.

Shaun

 

[TKW]

hi Shaun,

I am trying to get the macro to prompt a dialog box which my user would
input a file name (.tab) and then have my macro continue its work.

Here is what I have done so far (see macro below) but it will only open file
123123.tab. The file name is always in the same directory but the name
changes every day.

Hope you can help, thanks


Sub george()
'
' george Macro
' Macro recorded 3/30/2005 by marie
'

'
Application.Goto Reference:="R1C105"
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=G:\COSTING\LOBLAWS\TAB FILES;DefaultDir=G:
\COSTING\LOBLAWS\TAB FILES;Driver={Driver da Microsoft para arquivos texto
(*.txt" _
), Array( _
";
*.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
_
)), Destination:=Range("DA1"))
.CommandText = Array("SELECT * FROM `123123.tab`")
.Name = "LOBLAWS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Program Files\Common Files\ODBC\Data Sources\LOBLAWS.dsn"
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Shaun M. Angley]

TKW -
Does the user know the name of the file? If so you may want to try an input
box where the user can just type in the name of the file. Try this:

dim path as string, filename as string, fileaddy as string
path = "c:\somepathhere\subdirectory\"
filename = InputBox("Please type the name of the file.", , "*.tab")
fileaddy = path & filename

Now the filename variable is the complete address of the file.

Hope that helped.

 

[Perry]

If Office XP (or 2003/later version), use ApplicationFileDialog
More info (mind wordwrapping: URL runs from http:// throught to .asp)
http://msdn.microsoft.com/library/d...en-us/vbaof10/html/ofobjFileDialogFilters.asp

While having taken care of the *.tab Filefilter, it's better to let users
choose from a list with alternative filenames as opposed
to having users input a single "random" or almost random (daily generated)
filename.

Krgrds,
Perry