Bypass prompts in loop code

[dturgel]

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?

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/]

 

[Jeffrey Mahoney]

You can use: application.DisplayAlerts = False to get past the first one. I think. For the second one, if you know the password you can use something similar to this:

Dim WB As Workbook
  Set WB = Application.Workbooks.Open("C:\MyFiles\MrExcel\Password Protected.xlsx", Password:="Jeff")

Of course, the password would be visible to anyone looking at your VBA code, so you may want to protect the VBA project with a password.

Jeff

 

[dturgel]

Jeff,

Great, thanks!

Daniel

 

[dturgel]

Jeff,

The password code worked great. For some reason the display alerts line did not work so well - even when I tried placing it in several different parts of the code. So what I did was switch off the updating in excel options for the loop run:

https://support.microsoft.com/en-us/kb/826921

Daniel