stuck in loop
Since you're passing myWB to that subroutine, you don't need to rely on the
activeworkbook. But I don't see anything in the snippet of code that would
cause a loop.
This worked fine for me:
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 CopyTemplates(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 CopyTemplates(myWB As Workbook)
'just for testing
MsgBox myWB.FullName
' ThisWorkbook.Worksheets(Array("Datasheet1", _
' "Datasheet2", _
' "Datasheet3", _
' "Charts1", _
' "Charts2", _
' "Charts3", _
' "Summary")).Copy _
' Befo=myWB.Worksheets(1)
'
myWB.Close savechanges:=True
End Sub
If you see the names correctly, then delete that line and uncomment the .copy
line(s).
Trish Smith wrote:
Hi all,
I've altered the code for browse and protect that Dave Peterson helped me
with and now it gets stuck in a loop.
The original code looped though all workbooks in browsed for folder and then
looped through all worksheets with a for each loop.
I altered the code to do a different job. What I wanted to do was copy an
array of sheets from the workbook with the code to all the workbooks in the
folder.
Here's the bits of code
This is the end of the browse for filename code
Set myWB = Workbooks.Open(myPath & myName)
Call CopyTemplates(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
Next
End Sub
This is the bit that replaced the for each unprotect
Sub CopyTemplates (myWB As Workbook)
Set myWB = ActiveWorkbook
ThisWorkbook.Worksheets(Array("Datasheet1", "Datasheet2", "Datasheet3", _
"Charts1", "Charts2", "Charts3", "Summary")).Copy _
Befo=myWB.Worksheets(1)
myWB.Close savechanges:=True
End Sub
If anyone could tell me what's missing please, I'd be more than grateful.
Thank you
--
Trish
--
Dave Peterson
|