Question: OPEN return status

Question: OPEN return status

Post by Frederik Roman » Wed, 09 Jul 2003 20:40:57



Excel-97 (SR-2)

My Excel version 4 manual describes the obsolete form of Fopen, with
this syntax I hoped to perform

        [G15:FileNonExistent] = <within a macro> isNA( Fopen(
"C:\datafile.pdf", 2))

However the syntax in Excel-97 now means I have to use

        [G15:FileNonExistent] = <within a macro> open "C:\datafile.pdf" for
input as #123

Trouble is this does not return a status, but generates an error if
the file does not exist. How can the macro trap this error and convert
it to a return status?

When I actually tried this with a non-existent file, the cell invoking
the macro just displays #Value!, only if I step into the macro do I
get an error reported.

TIA (thanks in advance),
Fred.

 
 
 

Question: OPEN return status

Post by Steve Smallma » Wed, 09 Jul 2003 21:57:31


Frederick,

simplest method is to include error trapping in your macro.

after your variable declaration section enter a line similar to
on error goto errortrap

prior to end sub
insert a line that reads
exit sub

followed by
if err="your error number withou the quotes" then
    msgbox "your message"
else
    msgbox err &" " & error
end if
end sub

This will give you the basics of error handling and allow you to return a
value

steve

a

Quote:> Excel-97 (SR-2)

> My Excel version 4 manual describes the obsolete form of Fopen, with
> this syntax I hoped to perform

> [G15:FileNonExistent] = <within a macro> isNA( Fopen(
> "C:\datafile.pdf", 2))

> However the syntax in Excel-97 now means I have to use

> [G15:FileNonExistent] = <within a macro> open "C:\datafile.pdf" for
> input as #123

> Trouble is this does not return a status, but generates an error if
> the file does not exist. How can the macro trap this error and convert
> it to a return status?

> When I actually tried this with a non-existent file, the cell invoking
> the macro just displays #Value!, only if I step into the macro do I
> get an error reported.

> TIA (thanks in advance),
> Fred.


 
 
 

Question: OPEN return status

Post by Tom Ogilv » Wed, 09 Jul 2003 22:46:12


The simplest method would be to check if the file exists

If dir("C:\myfolder\myfile.xls") = "" then
   ' file doesn't exist
Else
    ' open the file
End if

but I don't know how successful opening a file will be from a worksheet
cell - I wouldn't think that would be an action that a UDF in a cell could
perform.  If you are doing low level file io, then perhaps it is acceptable.

Regards,
Tom Ogilvy


> Frederick,

> simplest method is to include error trapping in your macro.

> after your variable declaration section enter a line similar to
> on error goto errortrapth

> prior to end sub
> insert a line that reads
> exit sub

> followed by
> if err="your error number withou the quotes" then
>     msgbox "your message"
> else
>     msgbox err &" " & error
> end if
> end sub

> This will give you the basics of error handling and allow you to return a
> value

> steve

> a


> > Excel-97 (SR-2)

> > My Excel version 4 manual describes the obsolete form of Fopen, with
> > this syntax I hoped to perform

> > [G15:FileNonExistent] = <within a macro> isNA( Fopen(
> > "C:\datafile.pdf", 2))

> > However the syntax in Excel-97 now means I have to use

> > [G15:FileNonExistent] = <within a macro> open "C:\datafile.pdf" for
> > input as #123

> > Trouble is this does not return a status, but generates an error if
> > the file does not exist. How can the macro trap this error and convert
> > it to a return status?

> > When I actually tried this with a non-existent file, the cell invoking
> > the macro just displays #Value!, only if I step into the macro do I
> > get an error reported.

> > TIA (thanks in advance),
> > Fred.