Send Keys in Excel

C

CamiCat

Member
Hello @sivagelli ,

I need to Auto refresh pivot table on internal data change in Excel, for updating the Excel of Input of the bot.
Do you know through VBA?
Thank you so much,
Camilla :)
 
S

Shweta

Active Member
Hi Camicat,

You can refresh the pivot table using below send key combinations also-

1. Activate any cell in your pivot table, say A1
2. Send keys: "<{SHIFT}{F10}>{SHIFT}"
3. Send keys: "{DOWN}{DOWN}{DOWN}{ENTER}" //Down key will take you to refresh button and then Enter.

Please let me know, if it resolves your issue.

Thanks!
 
A

Anton Hosang

Member
Almost everything that you can do on Excel can be done through code actions. Excel exposes its functionality through COM.

You can loop through pivot tables and/or refresh individually.
Look at the VBA docs(better for reference/explanation) and such code can usually be translated into VB.Net quite easily.

e.g. this code would refresh pivot tables on a given Worksheet. Easily extended to refresh all in a workbook.
Code: 
Dim ws As Object
ws = GetWorksheet(handle, wbName, sheetName)

Dim xTable As Object
For Each xTable In ws.PivotTables
    xTable.RefreshTable()
Next

Then you don't have to worry about spying.
 
C

CamiCat

Member
Hello @Anton Hosang,

I tried your solution but i get the following error while performing the Code step.
Could not execute code stage because exception thrown by code stage: The given key was not present in the dictionary.
Can you please help me kindly?
Thank you
cami
 
A

Anton Hosang

Member
This is because you don't have en Excel instance with that handle you are passing. Nothing to do with my code.

Look into how the Excel VBO works.
Sorry
 
A

AnnieB

New Member
Not sure where to go on this. We are spy on a web site. ON a field for an input box. We made the value as a Wildcard. Then in the VBO we have a wait to check if that input box exist, based the finding it would perform some sort of action. However we are getting this error " Expression for parameter in Row 1 is blank". Where can I go from here.
 
L

lazloturbine

New Member
Anton Hosang said:
Almost everything that you can do on Excel can be done through code actions. Excel exposes its functionality through COM.

You can loop through pivot tables and/or refresh individually.
Look at the VBA docs(better for reference/explanation) and such code can usually be translated into VB.Net quite easily.

e.g. this code would refresh pivot tables on a given Worksheet. Easily extended to refresh all in a workbook.
Code: 
Dim ws As Object
ws = GetWorksheet(handle, wbName, sheetName)

Dim xTable As Object
For Each xTable In ws.PivotTables
    xTable.RefreshTable()
Next

Then you don't have to worry about spying.
Click to expand...

When I try to use your code I get a "GetWorksheet is not declared" type of issue. Do I need to add the GetWorksheet function to the global code? If so, can you share the global code you're using?

Thanks!
 
S

Sanjiv

New Member
To refresh pivot table according to me better use vb code.(working)
First, SAVE utility MS EXCEL VBO AS MS EXCEL VBO EXTENTED OR TEST.
Then follow the steps of attached file.
 

Attachments

  • Capture.PNG
    14.9 KB · Views: 95
  • Capture1.PNG
    14.9 KB · Views: 91
  • Capture2.PNG
    19.3 KB · Views: 93
You must log in or register to reply here.
Top