verify a path exists

[Guest]

This is probably so simple that I am overlooking it. I want to write code as
an If...Then method to verify that a certain path exists. Let's say
"C:\Windows|Media".
I tried the Exists( What ) method but could not find an object that VBA
liked. Make me feel stupid and show me the code. Thanks.

 

[Dave Peterson]

I do this:

Dim TestStr as string

teststr = ""
on error resume next
teststr = dir("c:\windows\media\nul")
on error goto 0

if teststr = "" then
'doesn't exist
else
'it's there
end if

====
But this'll work, too:

If CreateObject("Scripting.FileSystemobject") _
.folderexists("C:\windows\media") = True Then
MsgBox "Yep"
Else
MsgBox "nope"
End If

 

[Guest]

Thanks Dave, I knew it was not that hard, but my 71 year old brain don't
function too well sometimes.

 

[Dave Peterson]

Man, I hope you're not 80.

The first 9 years would have been tough!

 

[Guest]

Nope just 71 in Oct. I used the Dir() function but I found that the back
slash and a file name are required to make it work right.

exmpl: teststr = Dir("C:\Windows\Media\*.mid") . This gives me specific
info to make the program run right.

Thanks again.

 

[Dave Peterson]

That will return something if there is a file with the extension of .mid.

But it's really not a good check to see if a folder exists (just to stress a
very minor point).

Nope just 71 in Oct. I used the Dir() function but I found that the back
slash and a file name are required to make it work right.

exmpl: teststr = Dir("C:\Windows\Media\*.mid") . This gives me specific
info to make the program run right.

Thanks again.

 

[Guest]

The purpose of the check is to see if the file exists on someone elses
computer. I have a novelty program that I have written using Excel that
plays part of a midi file and if the recipient does not have the same
location, I can avoid an error message by using the If ... Then method to
first check for the path. It does not make any difference what the midi file
is, as long as there is one at that location. So what you pointed me to is
what I needed. My only other alternative would be to figure out how to
incorporate the sound into the workbook and that is over my head.

 

[Dave Peterson]

I could use Insert|object and browse to my MIDI file to insert it into a
worksheet (and hide that worksheet later).

Then I could use:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Set OLEObj = Worksheets("sheet1").OLEObjects("object 1")
OLEObj.Verb

End Sub

But in xl2003, I was prompted with security questions to allow this potentially
harmful program to play. And then it opened up in my default MIDI player
(WinAmp for me).

I'm not sure you've seen this, but John Walkenbach can help:
http://www.j-walk.com/ss/excel/tips/tip59.htm

Stolen from his site:

Example: Playing a MIDI File
If the sound file is a MIDI file, you'll need to use a different API call. The
PlayMIDI subroutine starts playing a MIDI file. Executing the StopMIDI
subroutine will stop playing the MIDI file.

Private Declare Function mciExecute Lib "winmm.dll" _
(ByVal lpstrCommand As String) As Long
Sub PlayMIDI()
MIDIFile = "xfiles.mid"
MIDIFile = ThisWorkbook.Path & "\" & MIDIFile
mciExecute ("play " & MIDIFile)
End Sub

Sub StopMIDI()
MIDIFile = "xfiles.mid"
MIDIFile = ThisWorkbook.Path & "\" & MIDIFile
mciExecute ("stop " & MIDIFile)
End Sub

The purpose of the check is to see if the file exists on someone elses
computer. I have a novelty program that I have written using Excel that
plays part of a midi file and if the recipient does not have the same
location, I can avoid an error message by using the If ... Then method to
first check for the path. It does not make any difference what the midi file
is, as long as there is one at that location. So what you pointed me to is
what I needed. My only other alternative would be to figure out how to
incorporate the sound into the workbook and that is over my head.

 

[Guest]

Thanks Dave, I'll check Walkenbach out. I already had it playing on my
system, I just wanted to be sure the pogram didn't crash when I sent it to
someone else to run. I have both .wav and .mid files that I call at various
points. The .wav files I can bypass with an On Error statement if the
receiving system doesn't have the files but the .mid was a little bit harder
to handle because of the way I have it in the lineup. Maybe I can get some
ideas from Walkenbach. Thanks.

 

[Guest]

I checked Walkenbach and the Insert > Object method. Nothing there that will
do more than what I have now. The Insert > Object still ties the sound to
the path on the originating computer. If I E-mail the workbook as an
attachment, which is the standard method, then any path references in the VBA
code has to be compatible to the receiving computer as well as the Excel
software. Otherwise the whole project is an exercise in futility. Like I
said, the only other way is to somehow attach the sound file as part of the
workbook or send it with the workbook as a separate attachment and somehow
call it from the receiving user's files, but again the path is the problem.
Where will it be stored on the other user's system? I think what I have will
have to do. Thanks for your time.

Thanks Dave, I'll check Walkenbach out. I already had it playing on my
system, I just wanted to be sure the pogram didn't crash when I sent it to
someone else to run. I have both .wav and .mid files that I call at various
points. The .wav files I can bypass with an On Error statement if the
receiving system doesn't have the files but the .mid was a little bit harder
to handle because of the way I have it in the lineup. Maybe I can get some
ideas from Walkenbach. Thanks.

 

[Dave Peterson]

The Insert|Object actually embeds the sound file into the workbook--but it has
those troublesome security prompts in them. (At least for me in xl2003.)

If you included the sound files as one of the files in your email attachment and
explain to the recipient that all the .xls and .mid/.wav/whatever have to be
stored in the same folder, then you could use John's code to get that folder.

MIDIFile = ThisWorkbook.Path & "\" & MIDIFile

ThisWorkbook.path will pick up where the .xls file was stored.

You could check to see if the sound file is there when you open the xl file. If
you don't find it/them, you just pop up a message box and tell them to read the
instructions to store all the files in the same folder and shut down workbook.

(I'd zip all the .xls files and the sound files -- but that can be a problem for
people who've never used zip files, too.)

====

And if you don't care what sound file you get, you could look under the Windows
folder (C:\windows or C:\winnt usually) for the \media subfolder. If you don't
find that folder, look for a sound file in the windows folder. (I don't recall
how WinNT stores files, but if I recall correctly, win98 put all those sound
files in c:\windows.)

I think that this will return the windows drive/folder in all systems--It works
in winXP Home:

msgbox Environ("windir")

 

[Jon Peltier]

Here are two functions I use all the time, as alternatives to Dir. I got
them from VB6 MVP Karl Peterson.

''============================================================================

Function FileExists(ByVal FileSpec As String) As Boolean

' Karl Peterson MS VB MVP

Dim Attr As Long

' Guard against bad FileSpec by ignoring errors

' retrieving its attributes.

On Error Resume Next

Attr = GetAttr(FileSpec)

If Err.Number = 0 Then

' No error, so something was found.

' If Directory attribute set, then not a file.

FileExists = Not ((Attr And vbDirectory) = vbDirectory)

Else

m_ErrorText = Err.Description

End If

End Function

''============================================================================

Function DirExists(ByVal FileSpec As String) As Boolean

' Karl Peterson MS VB MVP

Dim Attr As Long

' Guard against bad FileSpec by ignoring errors

' retrieving its attributes.

On Error Resume Next

Attr = GetAttr(FileSpec)

If Err.Number = 0 Then

' No error, so something was found.

' If Directory attribute set, then not a file.

DirExists = (Attr And vbDirectory) = vbDirectory

Else

m_ErrorText = Err.Description

End If

End Function

''============================================================================


- Jon

 

[Guest]

Most of the people that I send to use their computer like a TV set. They
turn it on and if there is something on the monitor they will read it. They
don't do a lot of complicated stuff like setting up spread sheets or
composing letter styles or making power point slides. If they can't click go
and have everything happen, they don't bother with it. However, I do usually
send the attachments zipped. These would normally open in their default
folders which means that I would need to know what versions of Windows/Office
or Excel each one is running. It gets too complicated, so I will just fix it
so that if they have the proper version they get sound and if they don't have
the proper version they get the silent treatment.

 

[Dave Peterson]

Just to add another option...

What version of excel are you and your friends using?

If everyone is using xl2002+, you can have the pc talk to them.

Application.Speech.Speak Text:="It's finished. Get back to work.", _
speakasync:=False

Most of the people that I send to use their computer like a TV set. They
turn it on and if there is something on the monitor they will read it. They
don't do a lot of complicated stuff like setting up spread sheets or
composing letter styles or making power point slides. If they can't click go
and have everything happen, they don't bother with it. However, I do usually
send the attachments zipped. These would normally open in their default
folders which means that I would need to know what versions of Windows/Office
or Excel each one is running. It gets too complicated, so I will just fix it
so that if they have the proper version they get sound and if they don't have
the proper version they get the silent treatment.