Password & WriteResPassword provided in VBA code but workbook still asking for password on open for some users

Jake Peralta

New Member
Joined
Jun 9, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
I have a macro that opens a separate workbook ("Master File Test.xlsm") and pastes data into it, saves it, then closes it. in the General Options sections under Save As, the Master File has a Password to modify, does NOT have a password to open, and is NOT set to Read-only recommended. Simple enough, works fine for me, and works for 2 of the 4 people I've sent it to for testing.. For the other 2 though, they still get a password prompt i.e. ("Master File Test.xlsm is reserved by Jake Peralta Enter password for write access, or open read only.") which prevents the macro from proceeding, despite the fact that the code (below) clearly specifies the password.. I've even gone overboard and tried including the Password and WriteResPassword to no avail. Has anyone ever encountered this, or better yet, does anyone know why this is happening and how to resolve it?!

Thanks in advance!

VBA Code:
If bIsBookOpen_RB("Master File Test.xlsm") Then
        Set DestWB = Workbooks("Master File Test.xlsm")
    Else
        Set DestWB = Workbooks.Open(ThisWorkbook.Path & "\Master File Test.xlsm", , , , "Per@lta99", "Per@lta99")
    End If
   
    'For the next bit, make sure that in the Import Export tab that the export date format is set to GENERAL. Otherwise VBA will send dates that are ambiguous e.g. 07/12/2020 to the Master File as 12/07/2020 even if you have all your date formatting set to Australian/UK. Also make sure that Transition Formula Entry is not selected under File>Options>Advanced otherwise this will parse the data and convert it to a string that Excel won't recognise as a date when you try to filter.
Set SourceRange = ThisWorkbook.Sheets("Import Export").Range("B10:HO10")
With ThisWorkbook.Sheets("Import Export")

    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    varData = .Range("B10:HO10" & LRow)
   
 
End With
   
With DestWB.Sheets("Master Data")
    For i = LBound(varData) To UBound(varData)
   
        Set c = .Range("B:B").Find(varData(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
        If c Is Nothing Then
            .Cells(Rows.Count, 2).End(xlUp)(2).Resize(1, UBound(varData, 2)) _
                = Application.Index(varData, i)
        Else
            .Cells(c.Row, 2).Resize(1, UBound(varData, 2)) _
                = Application.Index(varData, i)
        End If
    Next
End With
DestWB.Close savechanges:=True

    With Application
        .ScreenUpdating = False
        .EnableEvents = True
    End With
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,119,021
Messages
5,575,616
Members
412,679
Latest member
TSpan
Top