T
Trish Smith
Hi everyone,
I found a previous message about browsing to a folder which was answered but
I've not been able to follow the steps that were taken.
http://www.microsoft.com/office/com...&p=1&tid=7fdb3cee-fee5-4a2e-8ac7-d241d7a138d4
Now, I'm copying this bit from the thread because I don't understand what's
going on, sorry ! I've taken out some bits where it says which code is Chip
Pearson's and Barb Reinhardt's
Function BrowseFolder(Optional Caption As String = "") As String
Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long
With BrowseInfo
..hOwner = 0
..pidlRoot = 0
..pszDisplayName = String$(MAX_PATH, vbNullChar)
..lpszINSTRUCTIONS = Caption
..ulFlags = BIF_RETURNONLYFSDIRS
..lpfn = 0
End With
FolderName = String$(MAX_PATH, vbNullChar)
ID = SHBrowseForFolderA(BrowseInfo)
If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If
End Function
Dim myWB As Workbook
Dim AutoSecurity As MsoAutomationSecurity
'Dim myPath As String
Dim myName As String
Dim Path As String
Dim Prompt As String
Dim Title As String
Path = BrowseFolder("Select A Folder")
If Path = "" Then
Prompt = "You didn't select a folder. The procedure has been
canceled."
Title = "Procedure Canceled"
MsgBox Prompt, vbCritical, Title
Else
Prompt = "You selected the following path:" & vbNewLine & Path
Title = "Procedure Completed"
MsgBox Prompt, vbInformation, Title
End If
myName = Dir(Path)
Do
Debug.Print myName
AutoSecurity = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityLow
Set myWB = Workbooks.Open(myName)
Call UnprotectWB(myWB)
Application.AutomationSecurity = AutoSecurity
On Error Resume Next
myName = Dir ' Get next entry.
Loop While myName <> ""
End Sub
Sub UnprotectWB(myWB As Workbook)
Dim myWS As Worksheet
'If worksheets are password protected, this won't work
myWB.Unprotect
For Each myWS In myWB.Worksheets
myWS.Unprotect
Next myWS
End Sub
Dave Peterson answered and said this
I'd do this:
after this line:
Path = BrowseFolder("Select A Folder")
if right(path,1) <> "\" then
path = path & "\"
end if
Then for the dir statement:
myname = dir(mypath & "*.xls")
and also to not use VBA variable names but although I've tried to make
changes not sure doing right thing
myPath = BrowseFolder("Select A Folder") 'myPath = BrowseFolder("Select A
Folder")
If Right(myPath, 1) <> "\" Then 'If myPath = "" Then
myFolderPath = myPath & "\"
Prompt = "You didn't select a folder. The procedure has been canceled."
Title = "Procedure Canceled"
MsgBox Prompt, vbCritical, Title
Else
Prompt = "You selected the following path:" & vbNewLine & myFolderPath
Title = "Procedure Completed"
MsgBox Prompt, vbInformation, Title
End If
myName = Dir(myFolderPath & "*.xls")
at the moment I get the messagebox saying I haven't selected a folder.
Can anyone help please
thank you
I found a previous message about browsing to a folder which was answered but
I've not been able to follow the steps that were taken.
http://www.microsoft.com/office/com...&p=1&tid=7fdb3cee-fee5-4a2e-8ac7-d241d7a138d4
Now, I'm copying this bit from the thread because I don't understand what's
going on, sorry ! I've taken out some bits where it says which code is Chip
Pearson's and Barb Reinhardt's
Function BrowseFolder(Optional Caption As String = "") As String
Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long
With BrowseInfo
..hOwner = 0
..pidlRoot = 0
..pszDisplayName = String$(MAX_PATH, vbNullChar)
..lpszINSTRUCTIONS = Caption
..ulFlags = BIF_RETURNONLYFSDIRS
..lpfn = 0
End With
FolderName = String$(MAX_PATH, vbNullChar)
ID = SHBrowseForFolderA(BrowseInfo)
If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If
End Function
Dim myWB As Workbook
Dim AutoSecurity As MsoAutomationSecurity
'Dim myPath As String
Dim myName As String
Dim Path As String
Dim Prompt As String
Dim Title As String
Path = BrowseFolder("Select A Folder")
If Path = "" Then
Prompt = "You didn't select a folder. The procedure has been
canceled."
Title = "Procedure Canceled"
MsgBox Prompt, vbCritical, Title
Else
Prompt = "You selected the following path:" & vbNewLine & Path
Title = "Procedure Completed"
MsgBox Prompt, vbInformation, Title
End If
myName = Dir(Path)
Do
Debug.Print myName
AutoSecurity = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityLow
Set myWB = Workbooks.Open(myName)
Call UnprotectWB(myWB)
Application.AutomationSecurity = AutoSecurity
On Error Resume Next
myName = Dir ' Get next entry.
Loop While myName <> ""
End Sub
Sub UnprotectWB(myWB As Workbook)
Dim myWS As Worksheet
'If worksheets are password protected, this won't work
myWB.Unprotect
For Each myWS In myWB.Worksheets
myWS.Unprotect
Next myWS
End Sub
Dave Peterson answered and said this
I'd do this:
after this line:
Path = BrowseFolder("Select A Folder")
if right(path,1) <> "\" then
path = path & "\"
end if
Then for the dir statement:
myname = dir(mypath & "*.xls")
and also to not use VBA variable names but although I've tried to make
changes not sure doing right thing
myPath = BrowseFolder("Select A Folder") 'myPath = BrowseFolder("Select A
Folder")
If Right(myPath, 1) <> "\" Then 'If myPath = "" Then
myFolderPath = myPath & "\"
Prompt = "You didn't select a folder. The procedure has been canceled."
Title = "Procedure Canceled"
MsgBox Prompt, vbCritical, Title
Else
Prompt = "You selected the following path:" & vbNewLine & myFolderPath
Title = "Procedure Completed"
MsgBox Prompt, vbInformation, Title
End If
myName = Dir(myFolderPath & "*.xls")
at the moment I get the messagebox saying I haven't selected a folder.
Can anyone help please
thank you