Use VBA to open Windows Explorer to a particular folder

[Marceepoo]

When I save a file in MS Word, I right click on a file, and use the "Send to"
menu to send the file to a vbscript that opens Windows Explorer to the folder
where the file is located. The script is below.

I have made a vba macro that stores the fulname (path + filename) to a
strVar and pastes the strVar into the Windows clipboard.

I'd like to create a vba macro that would either:
1. Perform something in Vba like the following part of the vbscript
below (which opens Windows explorer to the path stored
in "txtFolderToOpen"):

Set SH = WScript.CreateObject("Shell.Application")
txtFolderToOpen = s
SH.Explore txtFolderToOpen
Set SH = Nothing

or

2. Call a vbscript (that I'll compose, once I know how to get vba to call
it).

Thanks, marceepoo

 

[Doug Robbins - Word MVP]

With a reference set to Microsoft Shell Controls and Automation, you can
use:

Dim SH As Shell32.Shell
Dim Fldr As Shell32.Folder
Dim txtFldrPath As String
Set SH = New Shell32.Shell
Set Fldr = SH.BrowseForFolder(0, "Select folder that contains the orders",
&H400)
If Not Fldr Is Nothing Then
txtFldrPath = Fldr.Items.Item.Path & "\"
End If
Set Fldr = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Marceepoo]

Dear Doug: 1. Thanks for the reply.
2. I pasted the coding below into a new macro (reproduced below), and it
generated the following error message: "Compile error: User-defined type not
defined". The following text on the first line was highlighted: "SH As
Shell32.Shell"

Any suggestions?
Where can I find material on the web that'll teach me about what you sent me?

Thanks again,
marceepoo

Here's the macro:....

Sub OpnWindoSasseme()
Dim SH As Shell32.Shell
Dim Fldr As Shell32.Folder
Dim txtFldrPath As String
Set SH = New Shell32.Shell
Set Fldr = SH.BrowseForFolder(0, "Select folder that contains the
orders", &H400)
If Not Fldr Is Nothing Then
txtFldrPath = Fldr.Items.Item.Path & "\"
End If
Set Fldr = Nothing
End Sub

 

[Jay Freedman]

You blew right past Doug's first half-sentence, and that's what caused your
error:

Translating this into step-by-step instructions:

1. In the VBA editor, click Tools > References.
2. Scroll down the list (after the first few items it's alphabetical) to
find "Microsoft Shell Controls and Automation". Click the check box next to
it to check it.
3. Click OK.
4. Run the macro.

What this does: There's a file that comes with Windows, at
C:\WinNt\System32\shell32.dll, that contains some code the macro needs to
use. The reference tells VBA that "when the macro asks for something that
starts with 'Shell32.' you can get it from this dll file." Since most macros
don't use that code, the reference isn't provided by default; it has to be
checked manually. The reference will be saved with the macro in your
template, though, so you only have to check it once.

For more information about what's in the Shell32 object, see
http://msdn.microsoft.com/library/e...basics/shell_basics_programming/objectmap.asp

 

[Marceepoo]

Dear Jay and Doug: Thanks for the explanation. I'll do as you instructed,
and thanks again for telling me where I can learn why and how it works.

But I can't help wondering (if you guys could help me to figure out) how I
could get vba to trigger call a vbscript, if I wanted to trigger the vbscript
that I have already programmed. I'm sure the approach Doug and you showed me
is the best way to achieve my objective. But I would like to know how to
achieve it (if I wanted to do so) by triggering/calling a vbscript, and
passing info to it. I'm sure that there will be other occasions when I'll
want to call a pre-programmed script, rather than start all over in VBA.

Thanks again, Marceepoo

 

[Jonathan West]

OK, there are two possible approaches here.

1. Import the VBScript into your VBA code. The languages are sufficiently
similar that it should not be that hard to do.

2. Use the ShellExecute API to call the VBScript. Paste the following into a
separate module


Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Public Function OpenDoc(ByVal DocFile As String) As Long
OpenDoc = ShellExecute(0&, vbNullString, DocFile, vbNullString,
vbNullString, vbNormalFocus)
End Function

Then all you need do is pass the full pathname of the file to OpenDoc, and
it will open the file in its default application. This isn't limited to
VBScripts, you can pass *any* file and it will be opened in its native
application.

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org