Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browse for folder - not selecting folder
That thread could be difficult to follow.
Option Compare Text Option Explicit Private Const BIF_RETURNONLYFSDIRS As Long = &H1 Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2 Private Const BIF_RETURNFSANCESTORS As Long = &H8 Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000 Private Const BIF_BROWSEFORPRINTER As Long = &H2000 Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000 Private Const MAX_PATH As Long = 260 Type BrowseInfo hOwner As Long pidlRoot As Long pszDisplayName As String lpszINSTRUCTIONS As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type Type SHFILEOPSTRUCT hwnd As Long wFunc As Long pFrom As String pTo As String fFlags As Integer fAnyOperationsAborted As Boolean hNameMappings As Long lpszProgressTitle As String End Type Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _ ByVal pidl As Long, _ ByVal pszBuffer As String) As Long Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _ lpBrowseInfo As BrowseInfo) As Long 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 Sub test() Dim myWB As Workbook Dim AutoSecurity As MsoAutomationSecurity 'Dim mymyPath As String Dim myName As String Dim myPath As String Dim Prompt As String Dim Title As String myPath = BrowseFolder("Select A Folder") If myPath = "" 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 myPath:" & vbNewLine & myPath Title = "Procedure Completed" MsgBox Prompt, vbInformation, Title If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If myName = Dir(myPath & "*.xls") If myName < "" Then Do Debug.Print myName AutoSecurity = Application.AutomationSecurity Application.AutomationSecurity = msoAutomationSecurityLow Set myWB = Workbooks.Open(myPath & myName) Call UnprotectWB(myWB) Application.AutomationSecurity = AutoSecurity On Error Resume Next myName = Dir ' Get next entry. If myName = "" Then Exit Do End If Loop End If End If 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 Trish Smith wrote: 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/comm...7-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 -- Trish -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browse for folder - not selecting folder
Hi Dave,
Definitely difficult to follow:-) This worked Yaayy thank you -- Trish "Dave Peterson" wrote: That thread could be difficult to follow. Option Compare Text Option Explicit Private Const BIF_RETURNONLYFSDIRS As Long = &H1 Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2 Private Const BIF_RETURNFSANCESTORS As Long = &H8 Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000 Private Const BIF_BROWSEFORPRINTER As Long = &H2000 Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000 Private Const MAX_PATH As Long = 260 Type BrowseInfo hOwner As Long pidlRoot As Long pszDisplayName As String lpszINSTRUCTIONS As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type Type SHFILEOPSTRUCT hwnd As Long wFunc As Long pFrom As String pTo As String fFlags As Integer fAnyOperationsAborted As Boolean hNameMappings As Long lpszProgressTitle As String End Type Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _ ByVal pidl As Long, _ ByVal pszBuffer As String) As Long Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _ lpBrowseInfo As BrowseInfo) As Long 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 Sub test() Dim myWB As Workbook Dim AutoSecurity As MsoAutomationSecurity 'Dim mymyPath As String Dim myName As String Dim myPath As String Dim Prompt As String Dim Title As String myPath = BrowseFolder("Select A Folder") If myPath = "" 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 myPath:" & vbNewLine & myPath Title = "Procedure Completed" MsgBox Prompt, vbInformation, Title If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If myName = Dir(myPath & "*.xls") If myName < "" Then Do Debug.Print myName AutoSecurity = Application.AutomationSecurity Application.AutomationSecurity = msoAutomationSecurityLow Set myWB = Workbooks.Open(myPath & myName) Call UnprotectWB(myWB) Application.AutomationSecurity = AutoSecurity On Error Resume Next myName = Dir ' Get next entry. If myName = "" Then Exit Do End If Loop End If End If 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 Trish Smith wrote: 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/comm...7-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 -- Trish -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Browse for folder - not selecting folder | Excel Programming | |||
Browse for folder | Excel Programming | |||
Browse for folder - Jim Rech's | Excel Programming | |||
Jim Rech's Browse for Folder | Excel Programming | |||
Browse for folder | Excel Programming |