1

I have an excel file which uses Salesforce connections (Salesforce Connector) to download the report file in csv format and updates the excel sheet with new data.

When i use the option Refresh All from Data, it automatically initiates the Salesforce.com connector to connect, download and update the new data.

I would like to refresh it every 5 minutes. (Initiate Salesforce Connector to connect, download and update the worksheet with latest data).

I have tried the following and unfortunately it did not work:

Refresh All --> Connection Properties --> Usage --> Refresh Every 5 minutes.

enter image description here

It refreshes but it does not initiate the salesforce connector to download and reflect the new data in worksheet.

I have tried creating the following macro to check if it initiates the Refresh All to connect, download and update:

Sub Macro1()

    ActiveWorkbook.RefreshAll

End Sub

How can I automatically initiate it every 5 minutes to reflect the latest data?

Tango
  • 386
  • 1
  • 6
  • 29
  • Is there a specific cell that will *change* when the update happens? Perhaps you can do a `Worksheet_Change()` event? – BruceWayne Mar 21 '17 at 21:04
  • @BruceWayne It would just add or remove couple of rows depending on the data of the report. I want to run the salesforce connector every 5 minutes. – Tango Mar 21 '17 at 21:08
  • Oh wait, you need `RefreshAll` to run, to redownload the data? I think [this thread](https://stackoverflow.com/questions/22772898/how-to-have-vba-execute-every-10-minutes) should help. – BruceWayne Mar 21 '17 at 21:23
  • @BruceWayne I am having issues initiating the RefreshAll (Redownload data and display in worksheet). Just to make sure it works, I would like to trigger it via macro. How could that be implemented? It only redownloads when I click on RefreshAll / Refresh. But when trying to initiate `ActiveWorkbook.RefreshAll` it does not redownload – Tango Mar 21 '17 at 21:29
  • Ohh okay. It's a little pedantic, but perhaps try `Workbooks(1).RefreshAll`? (from [here](https://msdn.microsoft.com/en-us/library/office/ff838648.aspx)), as maybe it is that specific (or perhaps `Workbooks(ThisWorkbook.Name).RefreshAll`)? Or does it technically refresh the sheet, just not re-downloading the data? Does [this thread](https://stackoverflow.com/questions/16222091/refresh-both-the-external-data-source-and-pivot-tables-together-within-a-time-sc) help? – BruceWayne Mar 21 '17 at 22:01
  • @BruceWayne It technically refreshes the sheet with the already downloaded data. However, it does not re-download the data. I tried that as well. Unfortunately, no luck. How can I automate the manual Data --> Refresh All? It launches the Salesforce connector which downloads and refreshes it. – Tango Mar 21 '17 at 23:23
  • I'm hesitating to say to look in to `SendKeys`. ...It may help but is notoriously erratic. Only ever use it if you don't need to use the computer otherwise. Personally, I'm interested in digging deeper how to get the data to redownload. (Perhaps change the title to reflect the actual issue, that auto refresh doesn't trigger a data connection download). – BruceWayne Mar 22 '17 at 01:52
  • @BruceWayne Thanks alot for the suggestion. I have managed to execute it using `SendKeys` However, I am facing issues executing it every 2 minutes. Check: http://stackoverflow.com/questions/42986634/excel-vba-macro-error-the-macro-may-not-be-available-auto-refresh – Tango Mar 23 '17 at 20:48

0 Answers0