Hi,
I've got really good code from AlphaFrog below. Every file this tries to loop through gives me the same two prompts each time:
1. This workbook contains links to one or more external sources that could be unsafe. If you trust the links, update them to get the latest date. Otherwise, you can keep working with the data you have.
2. 'xyz.docx' is protected. Password:
My question is - how can I get around these two prompts? I imagine getting around the first one may negate the second one - is that correct? If so, how do you do it?
I've got really good code from AlphaFrog below. Every file this tries to loop through gives me the same two prompts each time:
1. This workbook contains links to one or more external sources that could be unsafe. If you trust the links, update them to get the latest date. Otherwise, you can keep working with the data you have.
2. 'xyz.docx' is protected. Password:
My question is - how can I get around these two prompts? I imagine getting around the first one may negate the second one - is that correct? If so, how do you do it?
Code:
Sub Accounts_PricingLoopNorthDakota()
Dim FSO As Object, fsoFile As Object, fsoSubfolder As Object
Dim strPath As String
MsgBox "Please choose the Main Accounts folder."
Application.DisplayAlerts = False
ChDrive "G"
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "G:\"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
strPath = .SelectedItems(1) & "\"
End With
Application.ScreenUpdating = False
Set FSO = CreateObject("Scripting.FileSystemObject")
'Loop through each subfolder in main path
For Each fsoSubfolder In FSO.GetFolder(strPath).Subfolders
'Test if "Pricing" subfolder exists
If FSO.FolderExists(fsoSubfolder.Path & "\Pricing\") Then
'Loop through each Excel file in subfolder\Pricing\
For Each fsoFile In FSO.GetFolder(fsoSubfolder.Path & "\Pricing\").Files
'Open workbook
With Workbooks.Open(Filename:=fsoFile.Path)
'Change First Worksheet Mkt Curve Cell Q63 to 320M (North Dakota Premium)
.Worksheets(1).Range("Q63") = 320000000
'Save and Close Workbook
.Close SaveChanges:=True
End With
Next fsoFile
End If
Next fsoSubfolder
Application.ScreenUpdating = True
Set FSO = Nothing
End Sub
[end code/]