Excel VBA: Browse For Folder - Cancel Button

[Rob]

Dear All

I'm afraid I have another question on this highly active topic. I am
using some code that I found here on Google Groups, a bit of John
Walkenbach's work I think. Any way, the code results in a Browse for
folder dialogue box with three buttons... Make New Folder, OK and
Cancel. My problem lies with the Cancel button, when pressed the macro
hangs, most likely for the following reason...

I use the string (folder path) to open three files in that directory.
When the macro is debugged and I pause the mouse pointer over the
string variable, it shows a row of squares too long to fit in the tool
tips box (presumably, the font isn't available to show what it really
is). Hence, it is trying to open a non-existent file path.

What I would like to do is add a line saying "If (string) = (whatever
'Cancel' returns) Then GoTo Cancelled:" before the string is
used.

I have tried the following remedies (none of which has worked)
- Putting the string in a cell so I can read it (it comes out blank).
- Putting If (string) = "" ...
- Putting If (string) = Null ...
- Etc etc...

For full details of the code, just ask (I'd like to resist cluttering
up the screen for as long as possible, and I barely understand the code
I have copied anyway!)

Many thanks

Rob

 

[Mike Fogleman]

How about something like:

If fileToOpen = "False" Then Exit Sub

Mike F

 

[Rob]

Hi Mike

Thanks for the suggestion but that doesn't work. I tried it woth out
the quotes too and just got a type mismatch error as it's a string not
boolean. Here's the code, maybe that'll shed some light.

Const BIF_RETURNONLYFSDIRS = 1
Const BIF_NEWDIALOGSTYLE = &H40
Const MAX_PATH = 260


Type BrowseInfo
hWndOwner As Long
pidlRoot As Long
pszDisplayName As Long
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Integer
End Type


Declare Function GetActiveWindow Lib "user32" () As Long
Declare Function SHBrowseForFolder Lib "shell32" _
(pBrInfo As BrowseInfo) As Long
Declare Function SHGetPathFromIDList Lib "shell32" _
(ByVal pidList As Long, _
ByVal lpBuffer As String) As Long
Declare Sub CoTaskMemFree Lib "ole32.dll" _
(ByVal pMem As Long)


Option Explicit

Sub Open_Files()

Dim filepath As String
filepath = SelectFolder("Please Choose the location of the .txt files")

Workbooks.OpenText Filename:= _
filepath + "\file1.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
1), _
TrailingMinusNumbers:=True
'repeat open for files 2 and 3

End Sub

Public Function SelectFolder(sTitle) As String
Dim nPos As Long
Dim pidList As Long
Dim nResult As Long
Dim sPath As String
Dim pBInfo As BrowseInfo

sPath = String(MAX_PATH, Chr(0))
sTitle = sTitle & Chr(0)

With pBInfo
'Set the owner window (current active Window)
.hWndOwner = GetActiveWindow()
.lpszTitle = sTitle
.ulFlags = BIF_RETURNONLYFSDIRS + BIF_NEWDIALOGSTYLE
End With

pidList = SHBrowseForFolder(pBInfo)

If pidList <> 0 Then
SHGetPathFromIDList pidList, sPath
CoTaskMemFree pidList
nPos = InStr(sPath, Chr(0))
If nPos > 0 Then
sPath = Left(sPath, nPos - 1)
End If
End If

SelectFolder = sPath

End Function


Alls you have to do to get this to work is copy the code to a new
worksheet/module as normal. Then copy the following into notepad and
save it as 'file1.txt', then run the macro and navigate to wherever you
saved it.

Time Volt drop Battery current
40 0.005 19.8
180 0.002 9.9

Thanks again

Rob

 

[Steve Rindsberg]

After this line:

filepath = SelectFolder("Please Choose the location of the .txt files")

Add this:

Dim x As Long
Dim sTemp As String
For x = 1 To Len(filepath)
If Mid$(filepath, x, 1) <> Chr$(0) Then
sTemp = sTemp & Mid$(filepath, x, 1)
End If
Next

' let's see what we got:
If Len(sTemp) > 0 Then
Debug.Print sTemp
Else
Debug.Print "User canceled"
exit sub
End If

' Then resume with the rest of the code

 

[Jonathan West]

Hi Rob,

Which version of Office are you using? If XP or later, you can use the
FileDialog object to browse for a folder.

--
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

 

[Rob]

Thanks Steve, it worked! Pressing cancel now exits the sub (actually,
I have used a Msgbox to give the user the option to repeat or exit).

However, still being quite green in VBA, I have no idea HOW this works.
As far as I can tell the for/next loop goes through all the characters
in "filepath" and adds each valid character to the end of
"sTemp" (which starts empty), i.e. if there are no valid
characters, the output is empty or "". It then looks at the length
of sTemp and if zero, takes that as a 'cancel'. Is that right?

Also some other things that I don't understand, why are there $ signs
after some of the functions?
And what does Debug.Print do? I am running Office 2003 (Excel Version
11.6355.6360 SP1) if that helps.

Sorry to reward your efforts with another question, but I have built
the majority of my macro using codes from Google Groups, I want to make
sure I'm understanding it all.

Thanks again

Rob

PS For those (like me) who don't know what any of the commands do,
type them in VBA, highlight and press F1, it should help to make the
code clearer.

 

[Steve Rindsberg]

Thanks Steve, it worked! Pressing cancel now exits the sub (actually,
I have used a Msgbox to give the user the option to repeat or exit).

However, still being quite green in VBA, I have no idea HOW this works.
As far as I can tell the for/next loop goes through all the characters
in "filepath" and adds each valid character to the end of
"sTemp" (which starts empty), i.e. if there are no valid
characters, the output is empty or "". It then looks at the length
of sTemp and if zero, takes that as a 'cancel'. Is that right?

Got it in one, sir.

And if you dig further into the code that calls the browse dialog, you'll see
why this is necessary: it starts with a fixed length string padded with nulls
(necessary for the api call, as I understand it) so a string full of nulls is
what you get back when the user cancels.

I'm betting that somewhere else in the code this originally came from there's
some sort of "StripNulls" function or subroutine that got left out.

Also some other things that I don't understand, why are there $ signs
after some of the functions?

Left, Mid, Right return Variants (that happen to contain strings)
Left$, Mid$, Right$ return Strings

We want a string here, so why muddy the water with more complex data types that
eat more memory?

And what does Debug.Print do? I am running Office 2003 (Excel Version
11.6355.6360 SP1) if that helps.

While in the IDE, press Ctrl+G then run the code.
Debug.Print puts messages in the Immediate window but never shows them to the
user. Very handy.

Sorry to reward your efforts with another question, but I have built
the majority of my macro using codes from Google Groups, I want to make
sure I'm understanding it all.

No problem. I'm no whiz at this by a long shot, but most of what I know has
been absorbed here and in other groups like it. The wealth of knowledge and
the willingness to share it is astonishing.

Still, if you intend to take this very far, it'd be wise to find a book on VB
that suits your taste and get a handle on the fundamentals.

PS For those (like me) who don't know what any of the commands do,
type them in VBA, highlight and press F1, it should help to make the
code clearer.

And if the Help in these products were up to the standards set in, say, VB5 and
Office 97, you could actually learn to code this way. ;-)

 

[Rob]

Thanks a lot Steve, as always I have leared more from a (fairly) simple
question than just the answer.

On the subject of books, I have "Excel VBA Macro Programming" by
Richard Shepherd but I think I'm already too advanced for it. The
problem I have is that everything you look up builds upon the previous
chapter, so it's no good if you don't read it from the start (with gems
such as "what is a variable?"). I will read it from the start one day
(when I have the time.) but in the interim, can anyone suggest a good
reference book? One that assumes a reader with a good
mathematics/technical background and experience in say, Qbasic? I
myself have a degree in Mechanical Engineering and have
programmed/dabbled in Qbasic, CNC and (so far) managed to create (or
rather copy, then modify) about 200k of VBA code for a macro in Excel.

Many thanks again to you all

Rob

 

[Karl E. Peterson]

After this line:

filepath = SelectFolder("Please Choose the location of the .txt
files")

Add this:

Dim x As Long
Dim sTemp As String
For x = 1 To Len(filepath)
If Mid$(filepath, x, 1) <> Chr$(0) Then
sTemp = sTemp & Mid$(filepath, x, 1)
End If
Next

I'd suggest, instead, that the original SelectFolder function be corrected, so this
isn't necessary...

There already is TrimNull functionality there, but it only happens *if* the function
succeeds. If the user cancels, or the function fails for any other reason, the full
buffer of vbNullChar's is returned. This could be easily fixed by altering that
algorithm like this:

pidList = SHBrowseForFolder(pBInfo)
If pidList Then
SHGetPathFromIDList pidList, sPath
CoTaskMemFree pidList
nPos = InStr(sPath, Chr(0))
If nPos > 0 Then
sPath = Left(sPath, nPos - 1)
End If
Else
sPath = ""
End If
SelectFolder = sPath

' let's see what we got:
If Len(sTemp) > 0 Then
Debug.Print sTemp
Else
Debug.Print "User canceled"
exit sub
End If

' Then resume with the rest of the code

Still need that, either way.

Later... Karl

 

[Steve Rindsberg]

Thanks a lot Steve, as always I have leared more from a (fairly) simple
question than just the answer.

Happy to be part of it!

As to books, I'd start with a VB book. There are so many of them out there
that it's hard to make suggestions. The ones I started with are long out of
print, and given your experience, you'd want something that moves along a
little faster than I needed starting out.

Very generally, VB is the nuts and bolts of the language, VBA adds application
specific features. There's very little you can learn about VB that won't stand
you in good stead when moving to VBA; that's why I'd suggest it first.

 

[Tushar Mehta]

I use the string (folder path) to open three files in that directory.

I realize there are times when it would be useful to get the name of a
folder. I also realize that the code you got from Walkenbach's site,
appropriately corrected by various people, can be valuable.

However, the task facing you could have been solved with XL's
GetOpenFileName method. Ask the user to pick one of the files you
anyway would eventually open and you would be all set.

dim x as variant
x=application.getopenfilename(...)
if typename(x)="Boolean" then
'user canceled
else
'file selected
end if

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[Steve Rindsberg]

Hey Rob?

VB Lesson One: Listen to this Peterson guy.