How do you ignor File Not Found Dialog Popup ?

Workbooks("Master.xls").Worksheets("UpdatedList").Cells(rw, 2).FormulaR1C1 = 
"='[" & FileName & "]PointCount'!R2C5"

The above is my line of code that inserts a formula into a cell in my 
Master.xls workbook the formula links to a value in the worksheet called 
PointCount from multiple different workbooks. the problem is that if the 
program encounters a workbook that does not have a worksheet named 
"PointCount" I get a pop up FileNotFound window asking me to browse to the 
file that has the worksheet "PointCount" I do not want it to do this. Is 
there a way to have my macro skip over these files or suppres the "File Not 
Found" Pop up from poping up ?
0
Utf
1/11/2010 8:43:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
497 Views

Similar Articles

[PageSpeed] 6

Hi Dan

You can test it with a Excel4 macro like I do here
http://www.rondebruin.nl/summary2.htm


-- 

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dan Thompson" <DanThompson@discussions.microsoft.com> wrote in message news:BEE95227-FBDB-43CD-883A-FCF96B7DAB0F@microsoft.com...
> Workbooks("Master.xls").Worksheets("UpdatedList").Cells(rw, 2).FormulaR1C1 = 
> "='[" & FileName & "]PointCount'!R2C5"
> 
> The above is my line of code that inserts a formula into a cell in my 
> Master.xls workbook the formula links to a value in the worksheet called 
> PointCount from multiple different workbooks. the problem is that if the 
> program encounters a workbook that does not have a worksheet named 
> "PointCount" I get a pop up FileNotFound window asking me to browse to the 
> file that has the worksheet "PointCount" I do not want it to do this. Is 
> there a way to have my macro skip over these files or suppres the "File Not 
> Found" Pop up from poping up ?
0
Ron
1/11/2010 8:50:08 PM
I think I'd use the FileExists method to determine if the file did exist 
before entering the formula in a cell.

http://msdn.microsoft.com/en-us/library/x23stk5t(VS.85).aspx

when I use it, I enter the full path in filespec.  I've tweaked the code to 
make the function return a boolean TRUE/FALSE and execute other actions based 
on that result.
-- 
HTH,

Barb Reinhardt



"Dan Thompson" wrote:

> Workbooks("Master.xls").Worksheets("UpdatedList").Cells(rw, 2).FormulaR1C1 = 
> "='[" & FileName & "]PointCount'!R2C5"
> 
> The above is my line of code that inserts a formula into a cell in my 
> Master.xls workbook the formula links to a value in the worksheet called 
> PointCount from multiple different workbooks. the problem is that if the 
> program encounters a workbook that does not have a worksheet named 
> "PointCount" I get a pop up FileNotFound window asking me to browse to the 
> file that has the worksheet "PointCount" I do not want it to do this. Is 
> there a way to have my macro skip over these files or suppres the "File Not 
> Found" Pop up from poping up ?
0
Utf
1/11/2010 9:04:01 PM
Hi Barb

Dan want to test the worksheet name, not the workbook name

-- 

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message 
news:4538A665-6537-4F02-A795-271899E1EE20@microsoft.com...
>I think I'd use the FileExists method to determine if the file did exist
> before entering the formula in a cell.
>
> http://msdn.microsoft.com/en-us/library/x23stk5t(VS.85).aspx
>
> when I use it, I enter the full path in filespec.  I've tweaked the code to
> make the function return a boolean TRUE/FALSE and execute other actions based
> on that result.
> -- 
> HTH,
>
> Barb Reinhardt
>
>
>
> "Dan Thompson" wrote:
>
>> Workbooks("Master.xls").Worksheets("UpdatedList").Cells(rw, 2).FormulaR1C1 =
>> "='[" & FileName & "]PointCount'!R2C5"
>>
>> The above is my line of code that inserts a formula into a cell in my
>> Master.xls workbook the formula links to a value in the worksheet called
>> PointCount from multiple different workbooks. the problem is that if the
>> program encounters a workbook that does not have a worksheet named
>> "PointCount" I get a pop up FileNotFound window asking me to browse to the
>> file that has the worksheet "PointCount" I do not want it to do this. Is
>> there a way to have my macro skip over these files or suppres the "File Not
>> Found" Pop up from poping up ? 

0
Ron
1/11/2010 9:22:49 PM
Reply:

Similar Artilces: