Jake Peralta
New Member
- Joined
- Jun 9, 2020
- Messages
- 9
- Office Version
- 2010
- Platform
- 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!
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