vbshow bugs out if folder does not exist

[Jaye7]

I have the following code to show a folder upon opening my excel file, however, if the folder doesn't exist then it bugs out.

I have tried codes like on error resume next, on error resume line1" etc... and even if err.number= 75 and nothing will skip over the script if the folder doesn't exist, any help would be appreciated.

I have also tried If Dir("\\IBSSBS\Users\jtait\My Documents\Personal" & <> "" Then

the code that I want to work is.

SetAttr \\IBSSBS\Users\jtait\My Documents\Test1, vbShow

 

[mthompso]

On Error Resume Next
bFileExists = TRUE
Set File = \\IBSSBS\Users\jtait\My Documents\Test1
If Err.Number <> 0 Then bFileExists = FALSE
On Error Goto 0 

If bFileExists Then
SetAttr \\IBSSBS\Users\jtait\My Documents\Test1, vbShow
End If

 

[shg]

[PLAIN]    Const myDir As String = "\\IBSSBS\Users\jtait\My"]\\IBSSBS\Users\jtait\My Documents\Test1"
 
    If Len(Dir(myDir, vbDirectory)) Then
        SetAttr myDir, ???
    End If
[/PLAIN]

I have the ??? because I don't know what attribute you're trying to set; vbShow isn't one. See Help for the SetAttr statement.

 

[Jaye7]

Sorry people, but neither of these scripts work even after modifying the codes to remove errors in the scripts, if the file doesn't exist then if will bug out just like the example of vbshow etc... that I gave.

You need to try them in your own excel workbook as you will see what I mean, make sure that the file you want to show doesn't not exist, as when I use the file at home the directory does not exist and therefore bugs out.

 

[p45cal]

I've taken a peep at the help for SetAttr.
1. It says it will bug out if the file is open. Is it?
2. vbShow isn't one of the constants listed in the named arguments. So what value does vbShow have?
3. SetAttr doesn't "show a folder upon opening my excel file", it sets the attributes of a file.
4. The filename and path needs to be a string, so enclose in quotation marks.

or have I completely missed the point?

shg4421's code should work fine because it prevents the command even being executed if the directory doesn't exist.

 

[Jaye7]

Hi P45cal,

Thanks for your reply.

shg's code bugs out on a couple of lines.

Const myDir As String = "\\IBSSBS\Users\jtait\My"]\\IBSSBS\Users\jtait\My Documents\Test1"
 
and
 
SetAttr myDir, ???

vbshow works great if the file actually exists, which it does on my work computer, but the file doesn't exist on my home computer.

basically the vbshow, unhides a hidden folder and no error message statement is working if the file doesn't exist it still bugs out on the vbshow.

mthompso bugs out on

Set File = \\IBSSBS\Users\jtait\My Documents\Test1
 
and
 
SetAttr \\IBSSBS\Users\jtait\My Documents\Test1, vbShow

even if I change the file name to include quote marks it still will not work, as I said earlier, you need to try them in your own workbook, but the file name you use must not exist on your computer.
Just a note, I am using excel 2000 version.

So all I want to do is unhide a folder when I open my excel file, which again, if the folder exists then vbshow will work, try vbshow with a folder that exists and you will see what I mean (make sure that the folder is hidden first though).

Thanks

 

[p45cal]

Is Test1 a file or a folder?

 

[Jaye7]

Test1 is a folder, vbshow would unhide the folder if it exists, if it doesn't exist then it will work, if it doesn't exist then it will not work.

 

[p45cal]

This worked, fully tested in Excel 2003 against existent/non-existent folders, both hidden and visible:

Sub blah2()
Const myDir As String = "\\IBSSBS\Users\jtait\My Documents\Test1"
If Len(Dir(myDir, [COLOR=Red]18[/COLOR])) Then SetAttr myDir, vbNormal
End Sub

and the same:

Sub blah3()
Const myDir As String = "\\IBSSBS\Users\jtait\My Documents\Test1"
If Len(Dir(myDir, vbDirectory [COLOR=Red]+ vbHidden[/COLOR])) Then SetAttr myDir, vbNormal
End Sub

One problem was that Dir(myDir, vbDirectory) returns an empty string if the folder exists but is hidden! Dir(myDir, vbDirectory + vbHidden) [or Dir(myDir, 18)] returns a non-empty string.

I used vbNormal since this exists as a built-in constant whereas vbShow does not. However, if vbShow is set somewhere else in the code, then fine (assign vbHidden (or 2) to vbShow to hide the folder, and assign vbNormal (or 0) to vbShow to unhide it).

 

[Jaye7]

Thanks p45cal, I have tested the script and after adding in the on error resume next script it works great, but didn't seem to work properly on my computer without it.

Your help is appreciated.