I am trying to avoid using sendkeys in one of the application. The code is simple and I am just trying to copy page setup from one worksheet to second using a hack. It selects 2 worksheets and activates the worksheet from which I want to copy the page setup and sends the keys to the page setup dialog box which is going to appear immediately. The code as follows.
Sub copyPgSetup()
With workbooks("abc.xls")
' Select 2 worksheets
.Worksheets(Array("Sheet1", "Sheet2")).Select
' I want copy page setup from sheet1 to sheet 2 so activate that worksheet
.Worksheets("Sheet1").Activate
' Dialog is modal so sendkeys and then show the dialog.
SendKeys "{Enter}"
Application.Dialogs(xlDialogPageSetup).Show
.Worksheets("Sheet1").Select
End With
End Sub
The problem : This is run automatically at a scheduled time on a different pc while the desktop may be locked so the sendkeys never goes to the Page Setup window (and my code halts and waits for user input). Also I believe it is not the best practice to use sendkeys anyways. Is there any way we can do this by either some API function using sendmessage wm_close, vk_enter or sending {enter} etc to the active window?
Page setup Dialog is a modal window so my code for sendmessage does not run when the window is active so I tried creating an instance which opens the same file and tries to show the dialog. The excel still halts my code though the page setup of another instance of excel is open. I wont mind some advanced technique to be used like postmessage, sendmessage, on time or callbacks etc.
I did face some issues while copying the page setup property by property and I will use that as a last resort.
Thanks