Send keys stops working half way

[a.phillips]

Hi, I am using send keys to password protect an excel document.

Here is the code once the spreadsheet is open:

With oXL
.Visible = True
.Workbooks.Open (sFullPath)
SendKeys "(%F)(a)(%l)"
SendKeys "g"
SendKeys "password"
SendKeys "{ENTER}"
SendKeys "password"
SendKeys "{ENTER}"
This is where it stops working
SendKeys "{ENTER}"
SendKeys "y"
' .Workbooks.Close (sFullPath)
End With

I just need it to hit enter and y to save it and over-right the current file.

Anyone know why it stops working here, does it need a pause?

Thanks

 

[DJkarl]

Don't use sendkeys for this, one of the optional arguments for the workbook object is password, if you assign the password there using the SaveAs method, it will create a version with the password you want.

 

[a.phillips]

Don't use sendkeys for this, one of the optional arguments for the workbook object is password, if you assign the password there using the SaveAs method, it will create a version with the password you want.

Hi, I am using this code to export the spreadsheet, and cannot put a password on is this way:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "filepath", "filename"

 

[DJkarl]

Well your first example is showing Excel automation code, the workbooks.open code returns a workbook object, use that to Save the file with a password rather than try to use SendKeys. Any expert on here will tell you SendKeys is horribly unreliable.

 

[a.phillips]

Well your first example is showing Excel automation code, the workbooks.open code returns a workbook object, use that to Save the file with a password rather than try to use SendKeys. Any expert on here will tell you SendKeys is horribly unreliable.

Thanks, I've already experianced that send keys is very unreliable! The workbook already opens and saves just with no password on, that is what the sendkeys before, I would prefer to password protect it without, if anyone could give me some example code this would be much appriacted.

Thanks

 

[DJkarl]

Here is some air code (untested) that should do what you want.

Sub ExcelPwd(strFullPath As String, pwd As String)
Dim oXL As Excel.Application
Dim oWBS As Excel.Workbooks
Dim oWB As Excel.Workbook

Set oXL = New Excel.Application
Set oWBS = Excel.Workbooks
Set oWB = oWBS.Open(strFullPath)
oXL.DisplayAlerts = False
oWB.SaveAs strFullPath, , pwd
oWB.Close
Set oWB = Nothing
Set oWBS = Nothing
Set oXL = Nothing
End Sub

 

[a.phillips]

Thank you, I will try this and get back to you to let you know if it worked or not

 

[a.phillips]

Hi, I have put your code within mine, and it is brining up an error saying

Compiled error

User-defined type not defined

and is highlighting
'oXL As Excel.Application'
on the 'dim oXL As Excel.Application' line

would you like me to copy all of my code accross?

Thanks

 

[DJkarl]

You need to reference Excel in the application or change this to a late binding model. To reference Excel in the VBA window goto Tools->References find the Microsoft Excel references and add it to your project.

 

[a.phillips]

Hi, I have selected MSExcelAddin 1.0 Type Library

Is this the right one? As the code isnt still working

Thanks

 

[a.phillips]

Sorry, I have added another refrence to the library, and has stopped that error comming up on that section, however, where the password is it is saying varible not defined.

 

[jdraw]

Seems like you're missing a Dim....