function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
LoobLoob 

Using REST to export pre-defined Salesforce Report

I cannot find the answer to my question in the boards, and I am very new to SF, so please be gentle with me!

 

I have used REST before, but not with SF, and I am just starting. I already have some reports defined in SF, and I want to run an Excel VBA script to download the results of these four reports into Excel (I will be doing this once a week).

 

The documentation I have been able to understand keeps talking about SQL. I understand SQL, but since the report is already defined to SF, surely there is a way to specify that's what I want? Or do I simply have to rewrite the extract using SQL?

 

Sorry again if this has already been asked, but I could not find it.

 

Thanks for listening.

Best Answer chosen by Admin (Salesforce Developers) 
vbsvbs
Loob - There are multiple ways to achieve this:
1. Schedule your report and email to users as required. This can be Excel formatted or CSV.
2. Use Office Connect tool as an Add-in in Excel to run queries and download data directly to Excel sheets.
3. Use Apex data loader to export data if required in CSV format and using plain SOQL (SQL like native query language for Salesforce platform)

Based on your expertise in Salesforce options 1&2 are the easiest to use.

All Answers

vbsvbs
Loob - There are multiple ways to achieve this:
1. Schedule your report and email to users as required. This can be Excel formatted or CSV.
2. Use Office Connect tool as an Add-in in Excel to run queries and download data directly to Excel sheets.
3. Use Apex data loader to export data if required in CSV format and using plain SOQL (SQL like native query language for Salesforce platform)

Based on your expertise in Salesforce options 1&2 are the easiest to use.
This was selected as the best answer
LoobLoob

Thanks very much for this. Option 1 is not suitable -- I need to import into Excel and then manipulate the data. Option 3 prob the same.

 

Option 2 might be a goer, I'll look into it -- but I need to be able to allow someone to simply press a single button in Excel, and then VBA comminucates (somehow) with Salesforce to run the reports and import the results into Excel. In the past I have used REST to download data from various other systems in just this manner. Can I use VBA to control what the Office Connect tool does?

 

The four reports I have are fixed, so I don't mind if I kick them off manually the first time and from then on simply refresh using VBA somehow. However I am not sure whether authentication will work how I want -- I would prefer if, after the users click the single button, a dialogue box pops up and asks for login details. But I can live with asking the user to login separately somehow with the Office Connect tool.

 

I'll look into Office Connect and come back here to let you know how I get on.

 

Loob

LoobLoob

vbs, thanks again for this. It looks like this will do the job, even though it does not quite meet my requirements -- instead of the user clicking one button, they will have to use the add-in to login, then click on "Refresh All", then press the button.

 

When I have used the REST API to communicate on other systems, I never had to install an add-in -- I just used an HTTP call with appropriate authentication. But this non-REST approach will almost certainly satisfy my client, so thanks very much.

BCDBSABCDBSA
Hi Loob,

Have you found out any solutions yet?

Our company is looking for the same functionality--one button in Excel to refresh all the reports re-built in SF Reports. I have done a lot of researches, but I could not find one so far.
Dory OwenDory Owen
FYI: Current versions of Excel now have "Get & Transform" aka "Power Query" which has a Salesforce Report connector built in.
You can set up the import of the SF report and even do any housekeeping to tidy up the data, headers, etc.
User just hits the Refresh button.