I've been asked to do some work to automate a lengthy manual process. My client has to send some data every week, in a .xls file which is based on a template (unfortunate choice of term, it's not an Excel 'template') provided by the organization to which the data has to be returned.
This template contains macros, is password-protected (no, they won't tell me the password!) and the project is locked for viewing/editing.
I'm constrained to using VB6 to write my automation application. Some cells in the workbook are not locked, and my VB app is able to write data into those cells without any fuss. There are two data items which I can't write into cells directly. There are two command buttons on one of the sheets in the workbook, and clicking either of the buttons causes a dialog to be displayed, which allows me to enter one of those two data items. So, my plan is for the application to call the macro which displays the dialog, then use SendKeys to populate and dismiss the dialog. All OK so far. I know what the two macros are named, because if I open the workbook manually, I can see the list of macros, and I can run them the usual way.
My VB application contains the code
oExcelApp.Application.Run strWorkbookName & "!ChooseOrg_Click"
When this code executes, I get the error
Run-time error '1004':
Cannot run the macro 'RTT_PTL2_v1.1
20150201.xls!ChooseOrg_Click'. The macro may not be available in
this workbook or all macros may be disabled.
Well, I know that both of those suggestions are false, but I need to know if I can find a way to run the macro. I'd be grateful for any assistance.
Bookmarks