Open Dialog

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Excel 2000

I have recorded a macro to open a txt file and then carry out some
formatting, my need is however to run this on several files each with a
different name. How can I display the open file dialog window, select the
file and then run my macro? If I cancel the open dialog window I won't want
to run the macro.

Hopefully this makes sense!

Thanks, Rob
 
use application.GetOpenFileName

this returns the fully qualified path/name of the selected file. You can
then use Open or OpenText to open the file. If the user cancels, it returns
false

Dim wkbk as Workbook
dim fName as Variant
fName = Application.GetOpenFilename( args)
if typename(fName) = "Boolean" then
msgbox "You hit cancel"
exit sub
End If
set wkbk = workbooks.Open( fname)
 
Hi, Rob. Check out the VBA Help for Dialogs Collection Object, Dialog
Object, and Show Method and Show Method Example. One important fact is that
the Show Method returns True if a file is opened and False if the user hits
Cancel.

HTH
Ed
 
Tom,

Thanks for your code, this opens the file but I need to set criteria as to
how the file is opened i.e. what the delimiter is. I guess I need to select
the file, not open but capture the file name and pass it somehow into the
following.

Any pointers will be most welcome. Rob

Workbooks.OpenText Filename:= _
"D:\200040227.msd", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=True, OtherChar:="^", FieldInfo:=
_
Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 4), Array(5,
1), Array(6, 1), Array(7 _
, 9), Array(8, 1))
 
I said you could use OpenText

Dim wkbk as Workbook
dim fName as Variant
fName = Application.GetOpenFilename( args)
if typename(fName) = "Boolean" then
msgbox "You hit cancel"
exit sub
End If
set wkbk = workbooks.OpenText _
Filename:=fname, _
Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited,TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=True, _
OtherChar:="^", FieldInfo:=Array(Array(1, 9), Array(2, 9), _
Array(3, 1), Array(4, 4), Array(5,1), Array(6, 1), Array(7 _
, 9), Array(8, 1))
 
Thanks Tom and Ed, used the code once I understood there was Open and
OpenText.

Thanks again. Rob
 
Back
Top