Open Dialog
 

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

Open Dialog
 

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)



--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
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

Open Dialog
 

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

"Rob" wrote in message
...
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

Open Dialog
 

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))

"Tom Ogilvy" wrote in message
...
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)



--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
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

Open Dialog
 

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))

--
Regards,
Tom Ogilvy



"Rob" wrote in message
...
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))

"Tom Ogilvy" wrote in message
...
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)



--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
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

Open Dialog
 

Thanks Tom and Ed, used the code once I understood there was Open and
OpenText.

Thanks again. Rob
"Ed" wrote in message
...
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

"Rob" wrote in message
...
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