ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Loop through files in a folder and unprotect sheets (https://www.excelbanter.com/excel-programming/415662-loop-through-files-folder-unprotect-sheets.html)

Diddy
Loop through files in a folder and unprotect sheets
 
Hi everyone,

Could anyone help me with code for this please.

What I would like to do is to browse to the folder and then loop though each
workbook and turn protection off.

The books I have at the moment all only have one sheet but would it be
possible to code for more than 1 no probs if not

I would be so grateful if anyone could help.

I've been looking at lots of examples online but due to acute inexperience I
haven't been able to put anything together although I have learnt a lot
trying and trying and.....
Many thanks
--
Deirdre

Barb Reinhardt
Loop through files in a folder and unprotect sheets
 
You could try something like this to browse for the folder

http://www.vbaexpress.com/kb/getarticle.php?kb_id=284

You could then use something like this with the path hardcoded. Get the
path from the article above if you want.

Option Explicit
Sub Test()
' Display the names in C:\ that represent directories.
Dim myWB As Workbook
Dim AutoSecurity As MsoAutomationSecurity
Dim myPath As String
Dim myName As String

myPath = "Z:/myFolderPath/*.xls" ' Set the path.
' Retrieve the first entry.
myName = Dir(myPath)
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
--
HTH,
Barb Reinhardt



"Diddy" wrote:

Hi everyone,

Could anyone help me with code for this please.

What I would like to do is to browse to the folder and then loop though each
workbook and turn protection off.

The books I have at the moment all only have one sheet but would it be
possible to code for more than 1 no probs if not

I would be so grateful if anyone could help.

I've been looking at lots of examples online but due to acute inexperience I
haven't been able to put anything together although I have learnt a lot
trying and trying and.....
Many thanks
--
Deirdre

Diddy
Loop through files in a folder and unprotect sheets
 
Hi Barb,

Thanks for replying, I really appreciate your help :-)

I'm really fumbling around in the dark with this. I've tried altering your
code and get a run time error 1004 could not find at this line

Set myWB = Workbooks.Open(myName)

from the following mangled up bit of code
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

'|||||||||||||||||||||||||| END OF CHIP PEARSON'S CODE
||||||||||||||||||||||||||||||||
'Barb Reinhardt's Code altered by me

Sub FingersCrossed()

'http://www.microsoft.com/office/community/en-us/default.mspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=7 fdb3cee-fee5-4a2e-8ac7-d241d7a138d4&mid=20ef6639-91a7-4da7-8152-754bb57f77b2Sub Test()
' Display the names in C:\ that represent directories.
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
'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
'myPath = "Z:/myFolderPath/*.xls" ' Set the path.
' Retrieve the first entry.
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

I think this is where I have been having problems previously when trying to
join up bits of code. I don't really understand where the code is holding the
information about the browsed for folder.

I'm learning lots trying this out but I don't seem to be getting anywhere -
Help!

Sorry about the long post - I couldn't think how to describe what I had done

Many thanks

--
Deirdre

Barb Reinhardt
Loop through files in a folder and unprotect sheets
 
Put

Debug.print myName before the error and see what it gives you. It should
show the full path. If it doesn't, (and I may have forgotten that), you'll
need to concatenate the path name the you selected to the myName value.
--
HTH,
Barb Reinhardt



"Diddy" wrote:

Hi Barb,

Thanks for replying, I really appreciate your help :-)

I'm really fumbling around in the dark with this. I've tried altering your
code and get a run time error 1004 could not find at this line

Set myWB = Workbooks.Open(myName)

from the following mangled up bit of code
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

'|||||||||||||||||||||||||| END OF CHIP PEARSON'S CODE
||||||||||||||||||||||||||||||||
'Barb Reinhardt's Code altered by me

Sub FingersCrossed()

'http://www.microsoft.com/office/community/en-us/default.mspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=7 fdb3cee-fee5-4a2e-8ac7-d241d7a138d4&mid=20ef6639-91a7-4da7-8152-754bb57f77b2Sub Test()
' Display the names in C:\ that represent directories.
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
'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
'myPath = "Z:/myFolderPath/*.xls" ' Set the path.
' Retrieve the first entry.
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

I think this is where I have been having problems previously when trying to
join up bits of code. I don't really understand where the code is holding the
information about the browsed for folder.

I'm learning lots trying this out but I don't seem to be getting anywhere -
Help!

Sorry about the long post - I couldn't think how to describe what I had done

Many thanks

--
Deirdre

Diddy
Loop through files in a folder and unprotect sheets
 
Hi Barb,

Where should I see myname once I have put that line in?
Here are Immediate Window Contents when Set myWB = Workbooks.Open(myName) is
in yellow after the runtime error.
[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs]
[PickPlatform] <
[PickPlatform]
[VerifyOpen] <
[VerifyOpen] 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs]
[auto_open]

I'm going to have to google immediate window 'cos i don't know what it is.

Would it help to know that myName ="" when you hover over it in the code and
that myWB = nothing? Path has the expected pathname.

I'm not sure I understand what you mean by "concatenate the path name the
you selected to the myName value" (never mind not sure, call a spade a spade,
I just don't understand :-))
--
Deirdre (finding this exciting and frustrating in equal measure!)


"Barb Reinhardt" wrote:

Put

Debug.print myName before the error and see what it gives you. It should
show the full path. If it doesn't, (and I may have forgotten that), you'll
need to concatenate the path name the you selected to the myName value.
--
HTH,
Barb Reinhardt


Barb Reinhardt
Loop through files in a folder and unprotect sheets
 
Type CTRL G in the VBE to see the Immediate window.
--
HTH,
Barb Reinhardt



"Diddy" wrote:

Hi Barb,

Where should I see myname once I have put that line in?
Here are Immediate Window Contents when Set myWB = Workbooks.Open(myName) is
in yellow after the runtime error.
[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs]
[PickPlatform] <
[PickPlatform]
[VerifyOpen] <
[VerifyOpen] 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs]
[auto_open]

I'm going to have to google immediate window 'cos i don't know what it is.

Would it help to know that myName ="" when you hover over it in the code and
that myWB = nothing? Path has the expected pathname.

I'm not sure I understand what you mean by "concatenate the path name the
you selected to the myName value" (never mind not sure, call a spade a spade,
I just don't understand :-))
--
Deirdre (finding this exciting and frustrating in equal measure!)


"Barb Reinhardt" wrote:

Put

Debug.print myName before the error and see what it gives you. It should
show the full path. If it doesn't, (and I may have forgotten that), you'll
need to concatenate the path name the you selected to the myName value.
--
HTH,
Barb Reinhardt


Barb Reinhardt
Loop through files in a folder and unprotect sheets
 
Change

myName = DIR(path) t
myName = DIR(path & "*.xls")

Barb Reinhardt



"Diddy" wrote:

Hi Barb,

Thanks for replying, I really appreciate your help :-)

I'm really fumbling around in the dark with this. I've tried altering your
code and get a run time error 1004 could not find at this line

Set myWB = Workbooks.Open(myName)

from the following mangled up bit of code
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

'|||||||||||||||||||||||||| END OF CHIP PEARSON'S CODE
||||||||||||||||||||||||||||||||
'Barb Reinhardt's Code altered by me

Sub FingersCrossed()

'http://www.microsoft.com/office/community/en-us/default.mspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=7 fdb3cee-fee5-4a2e-8ac7-d241d7a138d4&mid=20ef6639-91a7-4da7-8152-754bb57f77b2Sub Test()
' Display the names in C:\ that represent directories.
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
'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
'myPath = "Z:/myFolderPath/*.xls" ' Set the path.
' Retrieve the first entry.
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

I think this is where I have been having problems previously when trying to
join up bits of code. I don't really understand where the code is holding the
information about the browsed for folder.

I'm learning lots trying this out but I don't seem to be getting anywhere -
Help!

Sorry about the long post - I couldn't think how to describe what I had done

Many thanks

--
Deirdre

Dave Peterson
Loop through files in a folder and unprotect sheets
 
If you're seeing those lines (extra junk from a bunch of debug.print lines in
the analysis toolpak addin), you're looking in the right spot.

If you don't see anything else under those lines, then your code isn't running
your "debug.print" lines--Or myName is blank!

I'd change those lines from:
Debug.Print myName
to
Debug.Print "MyName=" & myName

Just to see something.

Diddy wrote:

Hi Barb,

Where should I see myname once I have put that line in?
Here are Immediate Window Contents when Set myWB = Workbooks.Open(myName) is
in yellow after the runtime error.
[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs]
[PickPlatform] <
[PickPlatform]
[VerifyOpen] <
[VerifyOpen] 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs]
[auto_open]

I'm going to have to google immediate window 'cos i don't know what it is.

Would it help to know that myName ="" when you hover over it in the code and
that myWB = nothing? Path has the expected pathname.

I'm not sure I understand what you mean by "concatenate the path name the
you selected to the myName value" (never mind not sure, call a spade a spade,
I just don't understand :-))
--
Deirdre (finding this exciting and frustrating in equal measure!)

"Barb Reinhardt" wrote:

Put

Debug.print myName before the error and see what it gives you. It should
show the full path. If it doesn't, (and I may have forgotten that), you'll
need to concatenate the path name the you selected to the myName value.
--
HTH,
Barb Reinhardt

--

Dave Peterson

Diddy
Loop through files in a folder and unprotect sheets
 
Hi Barb,

I was hoping to be out of your hair by now !

I've changed myname etc to myName = Dir(Path & "*.xls") and I'm still
getting the same runtime error in the same place.

Any ideas?

Thank you
--
Deirdre


"Barb Reinhardt" wrote:

Change

myName = DIR(path) t
myName = DIR(path & "*.xls")

Barb Reinhardt



"Diddy" wrote:

Hi Barb,

Thanks for replying, I really appreciate your help :-)

I'm really fumbling around in the dark with this. I've tried altering your
code and get a run time error 1004 could not find at this line

Set myWB = Workbooks.Open(myName)

from the following mangled up bit of code
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

'|||||||||||||||||||||||||| END OF CHIP PEARSON'S CODE
||||||||||||||||||||||||||||||||
'Barb Reinhardt's Code altered by me

Sub FingersCrossed()

'http://www.microsoft.com/office/community/en-us/default.mspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=7 fdb3cee-fee5-4a2e-8ac7-d241d7a138d4&mid=20ef6639-91a7-4da7-8152-754bb57f77b2Sub Test()
' Display the names in C:\ that represent directories.
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
'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
'myPath = "Z:/myFolderPath/*.xls" ' Set the path.
' Retrieve the first entry.
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

I think this is where I have been having problems previously when trying to
join up bits of code. I don't really understand where the code is holding the
information about the browsed for folder.

I'm learning lots trying this out but I don't seem to be getting anywhere -
Help!

Sorry about the long post - I couldn't think how to describe what I had done

Many thanks

--
Deirdre

Diddy
Loop through files in a folder and unprotect sheets
 
Hi Dave,

Thanks for replying :-)

I got myname= in the immediate window. I'm assuming that means that myname
is empty.

Does myName="" mean that myName is empty or does it mean that it's a string?

--
Deirdre


"Dave Peterson" wrote:

If you're seeing those lines (extra junk from a bunch of debug.print lines in
the analysis toolpak addin), you're looking in the right spot.

If you don't see anything else under those lines, then your code isn't running
your "debug.print" lines--Or myName is blank!

I'd change those lines from:
Debug.Print myName
to
Debug.Print "MyName=" & myName

Just to see something.

Diddy wrote:

Hi Barb,

Where should I see myname once I have put that line in?
Here are Immediate Window Contents when Set myWB = Workbooks.Open(myName) is
in yellow after the runtime error.
[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs]
[PickPlatform] <
[PickPlatform]
[VerifyOpen] <
[VerifyOpen] 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs]
[auto_open]

I'm going to have to google immediate window 'cos i don't know what it is.

Would it help to know that myName ="" when you hover over it in the code and
that myWB = nothing? Path has the expected pathname.

I'm not sure I understand what you mean by "concatenate the path name the
you selected to the myName value" (never mind not sure, call a spade a spade,
I just don't understand :-))
--
Deirdre (finding this exciting and frustrating in equal measure!)

"Barb Reinhardt" wrote:

Put

Debug.print myName before the error and see what it gives you. It should
show the full path. If it doesn't, (and I may have forgotten that), you'll
need to concatenate the path name the you selected to the myName value.
--
HTH,
Barb Reinhardt

--

Dave Peterson

Barb Reinhardt
Loop through files in a folder and unprotect sheets
 
1) What is the value of myName when it has the error?
2) In the open command, it needs to include the entire path. I'm guessing
the path isn't there. What is it that you are passing to the open command?

I'd put breakpoints in at several places and step through the code line by
line using F8, or press F5 to go to the next breakpoint to debug.
--
HTH,
Barb Reinhardt



"Diddy" wrote:

Hi Barb,

I was hoping to be out of your hair by now !

I've changed myname etc to myName = Dir(Path & "*.xls") and I'm still
getting the same runtime error in the same place.

Any ideas?

Thank you
--
Deirdre


"Barb Reinhardt" wrote:

Change

myName = DIR(path) t
myName = DIR(path & "*.xls")

Barb Reinhardt



"Diddy" wrote:

Hi Barb,

Thanks for replying, I really appreciate your help :-)

I'm really fumbling around in the dark with this. I've tried altering your
code and get a run time error 1004 could not find at this line

Set myWB = Workbooks.Open(myName)

from the following mangled up bit of code
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

'|||||||||||||||||||||||||| END OF CHIP PEARSON'S CODE
||||||||||||||||||||||||||||||||
'Barb Reinhardt's Code altered by me

Sub FingersCrossed()

'http://www.microsoft.com/office/community/en-us/default.mspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=7 fdb3cee-fee5-4a2e-8ac7-d241d7a138d4&mid=20ef6639-91a7-4da7-8152-754bb57f77b2Sub Test()
' Display the names in C:\ that represent directories.
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
'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
'myPath = "Z:/myFolderPath/*.xls" ' Set the path.
' Retrieve the first entry.
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

I think this is where I have been having problems previously when trying to
join up bits of code. I don't really understand where the code is holding the
information about the browsed for folder.

I'm learning lots trying this out but I don't seem to be getting anywhere -
Help!

Sorry about the long post - I couldn't think how to describe what I had done

Many thanks

--
Deirdre

Dave Peterson
Loop through files in a folder and unprotect sheets
 
If the dir() fails to find a file, then myName will be an empty string.

After you selected the folder, you got a message back using this:
Prompt = "You selected the following path:" & vbNewLine & Path

In that msgbox, you could see that the path variable doesn't end with a
backslash.

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 one thing I don't like is to use variables that are also used by VBA.

Msgbox thisworkbook.path
would return the path where thisworkbook was saved.

I'd change all your Path variables to myPath:

dim myPath as string
mypath = browseforfolder(...)
if right(mypath,1) ...

myname = dir(mypath & "*.xls")
....

Using those keywords may not confuse excel, but they can confuse me!



Diddy wrote:

Hi Dave,

Thanks for replying :-)

I got myname= in the immediate window. I'm assuming that means that myname
is empty.

Does myName="" mean that myName is empty or does it mean that it's a string?

--
Deirdre

"Dave Peterson" wrote:

If you're seeing those lines (extra junk from a bunch of debug.print lines in
the analysis toolpak addin), you're looking in the right spot.

If you don't see anything else under those lines, then your code isn't running
your "debug.print" lines--Or myName is blank!

I'd change those lines from:
Debug.Print myName
to
Debug.Print "MyName=" & myName

Just to see something.

Diddy wrote:

Hi Barb,

Where should I see myname once I have put that line in?
Here are Immediate Window Contents when Set myWB = Workbooks.Open(myName) is
in yellow after the runtime error.
[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs]
[PickPlatform] <
[PickPlatform]
[VerifyOpen] <
[VerifyOpen] 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs]
[auto_open]

I'm going to have to google immediate window 'cos i don't know what it is.

Would it help to know that myName ="" when you hover over it in the code and
that myWB = nothing? Path has the expected pathname.

I'm not sure I understand what you mean by "concatenate the path name the
you selected to the myName value" (never mind not sure, call a spade a spade,
I just don't understand :-))
--
Deirdre (finding this exciting and frustrating in equal measure!)

"Barb Reinhardt" wrote:

Put

Debug.print myName before the error and see what it gives you. It should
show the full path. If it doesn't, (and I may have forgotten that), you'll
need to concatenate the path name the you selected to the myName value.
--
HTH,
Barb Reinhardt

--

Dave Peterson


--

Dave Peterson

Diddy
Loop through files in a folder and unprotect sheets
 
Hi Dave,

That did the trick :-)

Barb's original code had the slash,it's when I tried to add the Browse for
Folder bit from Chip Pearson's code that I missed it out.

Thanks so much for all your help.

Isn't it funny that you learn more from mistakes and trying to work it out
than from all the other elegant code that just zooms through.
--
Deirdre

Diddy
Loop through files in a folder and unprotect sheets
 
Hi Barb,

It was the slash that I missed out from your original code when I tried to
alter it to incoporate Chip Pearson's Browse for Folder code.

Thanks for your original idea and code and for sticking with me to sort out
my errors. It's been a fantastic learning experience for me, I know it
doesn't seem like much but I've learned such a lot that I didn't know before
and debugging has really made me look at what the code is doing and helped me
to understand it more.
Thank you once more
--
Deirdre


"Diddy" wrote:

Hi Barb,

I was hoping to be out of your hair by now !

I've changed myname etc to myName = Dir(Path & "*.xls") and I'm still
getting the same runtime error in the same place.

Any ideas?

Thank you
--
Deirdre


"Barb Reinhardt" wrote:

Change

myName = DIR(path) t
myName = DIR(path & "*.xls")

Barb Reinhardt



"Diddy" wrote:

Hi Barb,

Thanks for replying, I really appreciate your help :-)

I'm really fumbling around in the dark with this. I've tried altering your
code and get a run time error 1004 could not find at this line

Set myWB = Workbooks.Open(myName)

from the following mangled up bit of code
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

'|||||||||||||||||||||||||| END OF CHIP PEARSON'S CODE
||||||||||||||||||||||||||||||||
'Barb Reinhardt's Code altered by me

Sub FingersCrossed()

'http://www.microsoft.com/office/community/en-us/default.mspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=7 fdb3cee-fee5-4a2e-8ac7-d241d7a138d4&mid=20ef6639-91a7-4da7-8152-754bb57f77b2Sub Test()
' Display the names in C:\ that represent directories.
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
'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
'myPath = "Z:/myFolderPath/*.xls" ' Set the path.
' Retrieve the first entry.
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

I think this is where I have been having problems previously when trying to
join up bits of code. I don't really understand where the code is holding the
information about the browsed for folder.

I'm learning lots trying this out but I don't seem to be getting anywhere -
Help!

Sorry about the long post - I couldn't think how to describe what I had done

Many thanks

--
Deirdre


All times are GMT +1. The time now is 08:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com