Is it possible for a Recorded Macro to click on Form Control Buttons?

[theoutsider]

Hi everyone,

We've recently started to receive a proprietary excel file from one of our market research partners. Sheets in these excel files are protected, so I have to go around a few things in order to extract the data from the sheet.

Currently there are a couple of tables with about 30 rows, but it only shows the initial 10 rows of the table. In order to see further, you have to click the down arrow in the form control button on the left side of the table, which will scroll down by 2 more rows for each click.

I'm trying to copy all 30 rows of this data without manually clicking things every time.

So far I've tried recording a macro that copies the first 10 rows, clicks the format control button, and copies the next two rows.

However the issue is that when I'm recording the Macro, it is not registering the button clicks I make to the form control button. It just copies the same exact rows without clicking the "scroll down" button on the table.

How can I make sure my excel macro is clicking on the form control button that is used to scroll down? I'm not able to reach the data source, so only way to get the data is clicking the form control buttons and scrolling down from there.

[TMS]

If the data is present in the table, you don't (shouldn't) need to display it in order to copy it.

For example, if you create a new sheet and put this in cell A1, you should be able to view the data: =Source!A1:Z30 should display 26 columns by 30 rows of data in the sheet Source. Adjust as required.

You might need to format it appropriately and, with this simple formula, blank cells will show up as zero.

[theoutsider]

If the data is present in the table, you don't (shouldn't) need to display it in order to copy it.

For example, if you create a new sheet and put this in cell A1, you should be able to view the data: =Source!A1:Z30 should display 26 columns by 30 rows of data in the sheet Source. Adjust as required.

You might need to format it appropriately and, with this simple formula, blank cells will show up as zero.

Unfortunately data isn't that clearly put. There are a lot of operations going behind the scenes to figure out the data the table in excel sheet is going to serve.

For example, a single cell in the table refers to a very hidden sheet with a lot of index matching:

=IFERROR(INDEX(prepData!AX10:BL40,MATCH(SMALL(prepData!N10:N40,'KPI Overview'!C12:C22+1),prepData!N10:N40,0),COLUMN()-4),"")

When you unhide the prepData sheet, you find that it references further 8 other very hidden sheets depending on selections from a dropdown menu.

It would basically take me hours upon hours to figure out where exactly the original data is, and what kind of formulas are being used to generate them. Not to mention this will be made difficult since the sheets are all protected.

If I can't find a way to click buttons natively in excel, my next step is probably going to be using autohotkey + pulover's macro creator.

[TMS]

Maybe this will help: https://stackoverflow.com/questions/...sheet#33354552