How to add password automatically to exported excel file

[Db-why-not]

I created a form with a button that automatically exports several querys into an excel file with each query being 1 sheet in the excel file. I want to automatically have the excel file with a password. How to I add code that would do that? I would I integrate code that would do that into my existing code?


See my existing code

Private Sub CommandTransfer_Click()
On Error GoTo SubError

strPath = "M:\Research\Lung Us\Data\Exporting_OHSU\OHSU BAMC Lung Point Clinical Database _" & "_" & Format(Date(),"yyyy_mm_dd") & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Demographics", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Admission_dx", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_pre_existing", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Qualifying_cond", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_New_Pulm", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_US_scans", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_CXR_CT", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Micro", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_DC_DX", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_US_Review", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Box_Uploads", strPath

MsgBox "File Exported successfully", vbInformation + vbOKOnly, "Export Success"


SubExit:
      Exit Sub

SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, "An error occured"
    GoTo SubExit

End Sub

Any help would be appreciated. Thank you.

 

[vba_php]

I want to automatically have the excel file with a password. How to I add code that would do that? I would I integrate code that would do that into my existing code?

you know what's great about excel? that macro recorder! so....open a blank book in xl, record all your steps of creating a password through clicking buttons in the GUI. then, add to your access vba code an instance of an excel object and copy that code that the macro recorder wrote right on over!

 

[Gasman]

I would open the workbook after all the transfers and then password protect it.?

https://exceloffthegrid.com/vba-code-password-protect-excel-file/

 

[Db-why-not]

I would open the workbook after all the transfers and then password protect it.?



That's what Im trying not to have to do. I would like to just be able to export the file and not have to modify it at all after.

 

[Gasman]

Well if you ever find out how to do it, please post back here, as perhaps someone else might want to do the same thing.

 

[vba_php]

db why not,

what's wrong with doing what I suggested? did you at least try it?

 

[isladogs]

I would open the workbook after all the transfers and then password protect it.?

That's what Im trying not to have to do. I would like to just be able to export the file and not have to modify it at all after.

There's no password argument in TransferSpreadsheet.
Suggest you do an online search for password protect Excel file vba.

Try this as a starting point https://exceloffthegrid.com/vba-code-password-protect-excel-file/

 

[Db-why-not]

db why not,

what's wrong with doing what I suggested? did you at least try it?

Adam,
Thanks, I will try what you suggested. I was referring to the other solution, I dont want to have to password protect the file every time I create it by hand. I may be exporting this file a lot.

 

[vba_php]

db,

I was curious about this so i ran a test. for me, recording a macro and then setting password produces no code. so much for my great idea there! then I ran code, and I produced exactly what you want, except that this code does not set an actual password for some strange reason. this is Colin's recommendation too:

Private Function example()
On Error GoTo SubError

strPath = "c:\test\test.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "(AWF SOLUTION) - ascii_extended_chars", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "(AWF SOLUTION) - copy_edited_record_to_new_record", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "(AWF SOLUTION) - get values from GETROWS() function", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "(AWF SOLUTION) - Save_Paths_From_Attachments_In_Outlook_Emails", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "(AWF SOLUTION) - sdivsub_tbl", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "(AWF SOLUTION) - see_saw_listbox_transfer", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "(AWF SOLUTION) - update query with form field parameter", strPath


Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Set xl = New Excel.Application
Set wkbk = xl.Workbooks.Open("c:\test\test.xlsx")
    xl.DisplayAlerts = False
    wkbk.SaveAs Password:="password"
    xl.DisplayAlerts = True
    wkbk.Close
    xl.Quit
Set wkbk = Nothing
Set xl = Nothing


MsgBox "File Exported successfully", vbInformation + vbOKOnly, "Export Success"


SubExit:
      Exit Function

SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, "An error occured"
    GoTo SubExit

End Function

obviously I ran the test using my knowledgebase of solutions given on this forum, but you can run the same thing using ur queries. The code makes all the changes without excel showing on the screen. So if you can figure that last step out, as to why the password is not actually registering, you should be all set. I'm not sure why mine is not doing it here.

 

[Deleted member 147267]

saw this somewhere, but it's a 7 year old post so maybe not...

wkbk.Password = "password"
wkbk.Save

 

[vba_php]

wkbk.Password = "password"
wkbk.Save

hey micron, that works! so DB-why-not, here's what works (you should be able to copy this verbatim and it should work for you):

Private Sub CommandTransfer_Click()
On Error GoTo SubError

strPath = "M:\Research\Lung Us\Data\Exporting_OHSU\OHSU BAMC Lung Point Clinical Database _" & "_" & Format(Date(),"yyyy_mm_dd") & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Demographics", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Admission_dx", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_pre_existing", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Qualifying_cond", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_New_Pulm", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_US_scans", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_CXR_CT", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Micro", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_DC_DX", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_US_Review", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OHSU_Box_Uploads", strPath

Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Set xl = New Excel.Application
Set wkbk = xl.Workbooks.Open(strPath)
    xl.DisplayAlerts = False
    wkbk.Password = "YOUR PASSWORD"
    wkbk.Save
    xl.DisplayAlerts = True
    wkbk.Close
    xl.Quit
Set wkbk = Nothing
Set xl = Nothing

MsgBox "File Exported successfully", vbInformation + vbOKOnly, "Export Success"


SubExit:
      Exit Sub

SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, "An error occured"
    GoTo SubExit

End Sub

 

[Gasman]

Adam,
Thanks, I will try what you suggested. I was referring to the other solution, I dont want to have to password protect the file every time I create it by hand. I may be exporting this file a lot.

I was not suggesting that you password protect it by hand/manually, but by vba.?:banghead:

That is what the link was for.?