Test

[DocAElstein]

Thanks Molly, I'll take a look...Later. x

---

This is post https://www.excelfox.com/forum/showt...3399#post23399
https://www.excelfox.com/forum/showthread.php/2419-Test?p=23399#post23399

---

Lets take a look at the coding in the OPs file – here it is: https://www.excelfox.com/forum/showt...3400#post23400
But now let’s simplify it a bit to look at the aspect of protection,- in particular at the SaveAs stage, as I am guessing that this could be a good start point. My guess is based on all stuff associated with the Workbook.SaveAs method (Excel) : , ( https://learn.microsoft.com/en-us/of...orkbook.saveas )
We seem to have an awful lot of possible arguments to use, most likely all optional I expect, since I rarely see most of them ever used.
So let's simplify the OPs coding just to get to the SaveAs bit
This is all we are interested for the time being

 Sub GenerateWorkbooksPerNameSimplified()   '    https://www.excelfox.com/forum/showthread.php/2419-Test?p=23399#post23399
Dim swb As Workbook: Set swb = ThisWorkbook
Dim sws As Worksheet: Set sws = swb.Worksheets("CROSSACT")
 sws.Copy    '   This has the effect of producing a new workbook showing in the current instance of Excel,with one worksheet, named  CROSSACT      But this file does not really exist yet, not until we save
Dim dwb As Workbook
 Set dwb = Workbooks(Workbooks.Count)   '   The  Workbooks  collection object has all the workbooks we have open in this instance of Excel. The one with the highest index number ( which will be the same number as the total Count of workbooks ) , will be that one we just effectively added
 Set dwb = ActiveWorkbook               '   This is an alternative to do the same as the last line, because the workbook we just added will be currentlly active
Dim dFolderPath As String
 Let dFolderPath = swb.Path & Application.PathSeparator
Dim dFilePath As String, dName As String
 Let dName = "TestSaveAsFile"
 Let dFilePath = dFolderPath & dName & ".xlsx"
Dim FleFmat  As String
 Let FleFmat = "xlOpenXMLWorkbook"      '   https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat
 Let Application.DisplayAlerts = False
 dwb.SaveAs Filename:=dFilePath, FileFormat:=xlOpenXMLWorkbook   '   https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
 
 dwb.Close
 Let Application.DisplayAlerts = True
End Sub

That macro will make a Excel file with name TestSaveAsFile.xlsx which anyone can open and make changes to. https://postimg.cc/bddRDYGf

So Far so good. Now let's look at the password issue. At the current Microsoft documentation for SaveAs ,
https://learn.microsoft.com/en-us/of...orkbook.saveas ( https://learn.microsoft.com/en-us/of...l.xlfileformat )
, these two optional arguments look like the things to do with passwords.

Most likely Password:= is to prevent opening a file without the correct password
The WriteResPassword:= looks like what we need to look at. So the last coding simply would appear to need an extra WriteResPassword:="123" at the SaveAs line

  Sub GenerateWorkbooksPerNameSimplified1()   '    https://www.excelfox.com/forum/showthread.php/2419-Test?p=23399#post23399
Dim swb As Workbook: Set swb = ThisWorkbook
Dim sws As Worksheet: Set sws = swb.Worksheets("CROSSACT")
 sws.Copy    '   This has the effect of producing a new workbook showing in the current instance of Excel,with one worksheet, named  CROSSACT      But this file does not really exist yet, not until we save
Dim dwb As Workbook
 Set dwb = Workbooks(Workbooks.Count)   '   The  Workbooks  collection object has all the workbooks we have open in this instance of Excel. The one with the highest index number ( which will be the same number as the total Count of workbooks ) , will be that one we just effectively added
 Set dwb = ActiveWorkbook               '   This is an alternative to do the same as the last line, because the workbook we just added will be currentlly active
Dim dFolderPath As String
 Let dFolderPath = swb.Path & Application.PathSeparator
Dim dFilePath As String, dName As String
 Let dName = "TestSaveAsFile"
 Let dFilePath = dFolderPath & dName & ".xlsx"
Dim FleFmat  As Long
 Let FleFmat = 51      '   https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat
 Let Application.DisplayAlerts = False
 dwb.SaveAs Filename:=dFilePath, FileFormat:=FleFmat, WriteResPassword:="123"   '   https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
 
 dwb.Close
 Let Application.DisplayAlerts = True
End Sub

That initially seems to be doing something close to what may be wanted: On attempting to open , we get this pop up to which we can either give the password or open in read only.


If you choose to open in read only, you do still seem to be able to make changes, but then if you attempt to save them, you are told you must save under a different name.
Conclusions so far
It would appear that so far, the simple answer is just to add the extra WriteResPassword:="123" at the SaveAs line. ( Of course you can choose any password you like in place of the 123 )

---

One minor point, about optional arguments:
I prefer the named argunents like this ,
Filename:=dFilePath, FileFormat:=FleFmat, WriteResPassword:="123"
, but we can do it as in the original OP coding just by puting the options in order. By coincidence the OPs original equivalent code line was in the correct order.
dFilePath, xlOpenXMLWorkbook
The order there is

           .SaveAs  Filename:=dFilePath   , FileFormat:=xlOpenXMLWorkbook

The full correct order if using other options is given here https://learn.microsoft.com/en-us/of...orkbook.saveas

           .SaveAs  Filename:=                , FileFormat:=        , Password:=         , WriteResPassword:=          , ReadOnlyRecommended:=                 , CreateBackup:=           , AccessMode:=                 , ConflictResolution:=            , AddToMru:=                  , TextCodepage:=                           , TextVisualLayout:=                         , Local:=

We want just Filename , FileFormat , and WriteResPassword. We don’t want Password
The syntax to allow us to do that is to put an extra comma , which is sometimes referred to as place holder.
Something like this
dwb.SaveAs dFilePath, FleFmat, , "123"
This would be the full coding , just with the SaveAs line changed

 Sub GenerateWorkbooksPerNameSimplified2()   '    https://www.excelfox.com/forum/showthread.php/2419-Test?p=23399&viewfull=1#post23399
Dim swb As Workbook: Set swb = ThisWorkbook
Dim sws As Worksheet: Set sws = swb.Worksheets("CROSSACT")
 sws.Copy    '   This has the effect of producing a new workbook showing in the current instance of Excel,with one worksheet, named  CROSSACT      But this file does not really exist yet, not until we save
Dim dwb As Workbook
 Set dwb = Workbooks(Workbooks.Count)   '   The  Workbooks  collection object has all the workbooks we have open in this instance of Excel. The one with the highest index number ( which will be the same number as the total Count of workbooks ) , will be that one we just effectively added
 Set dwb = ActiveWorkbook               '   This is an alternative to do the same as the last line, because the workbook we just added will be currentlly active
Dim dFolderPath As String
 Let dFolderPath = swb.Path & Application.PathSeparator
Dim dFilePath As String, dName As String
 Let dName = "TestSaveAsFile2"
 Let dFilePath = dFolderPath & dName & ".xlsx"
Dim FleFmat  As Long
 Let FleFmat = 51      '   https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat
 Let Application.DisplayAlerts = False
 dwb.SaveAs dFilePath, FleFmat, , "123"      '   https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
 
 dwb.Close
 Let Application.DisplayAlerts = True
End Sub