How to add password automatically to exported excel file (1 Viewer)

D

Db-why-not

Registered User.
Local time
Today, 17:54
Joined
Sep 17, 2019
Messages
159
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

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

vba_php

Forum Troll
Local time
Today, 17:54
Joined
Oct 6, 2019
Messages
2,884
Db-why-not said:
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?
Click to expand...
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!
 
D

Db-why-not

Registered User.
Local time
Today, 17:54
Joined
Sep 17, 2019
Messages
159
Gasman said:
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.
Click to expand...
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 23:54
Joined
Sep 21, 2011
Messages
14,218
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

vba_php

Forum Troll
Local time
Today, 17:54
Joined
Oct 6, 2019
Messages
2,884
db why not,

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

isladogs

MVP / VIP
Local time
Today, 23:54
Joined
Jan 14, 2017
Messages
18,209
D

Db-why-not

Registered User.
Local time
Today, 17:54
Joined
Sep 17, 2019
Messages
159
vba_php said:
db why not,

what's wrong with doing what I suggested? did you at least try it?
Click to expand...

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

vba_php

Forum Troll
Local time
Today, 17:54
Joined
Oct 6, 2019
Messages
2,884
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:
Code: 
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.
 
M

Micron

AWF VIP
Local time
Today, 18:54
Joined
Oct 20, 2018
Messages
3,478
saw this somewhere, but it's a 7 year old post so maybe not...

wkbk.Password = "password"
wkbk.Save
 
vba_php

vba_php

Forum Troll
Local time
Today, 17:54
Joined
Oct 6, 2019
Messages
2,884
Micron said:
wkbk.Password = "password"
wkbk.Save
Click to expand...
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):
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

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

Gasman

Enthusiastic Amateur
Local time
Today, 23:54
Joined
Sep 21, 2011
Messages
14,218
Db-why-not said:
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.
Click to expand...

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

That is what the link was for.?:confused:
 
You must log in or register to reply here.

Similar threads

T
Solved Append table to excel file
Replies
8
Views
789
ebs17
E
D
Export to Excel not working correctly
Replies
3
Views
725
Pat Hartman
P
D
Solved Lopping through a query to export separate Excel files
Replies
19
Views
940
561414
5
S
Solved How to export Excel file to wanted directory?
Replies
18
Views
1,157
sharonng
S
A
Solved Problem with Excel operations (search / replace / save) with VBA
Replies
3
Views
1,108
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom