Web Reports Data From Excel

Reporting
1

(imported topic written by hmkjr91)

Hello,

I have created an Excel Pivot Table using an export from the BigFix Web Reports. This spreadsheet is posted in a document library and shared with the organization. The problem is that I have do an export from Web Reports and paste it into the Excel spreadsheet daily.

What are options for pulling fresh data directly from within the Excel spreadsheet using VBA? I’ve explored connecting directly to SQL; however, our policies won’t allow all company machines to have access to the BF SQL Server. I’ve researched using SOAP from with Excel 2007 and it looks like it has to be done via Visual Studio 2008.

Thoughts?

Thanks much

Buddy

2

(imported comment written by BenKus)

Hey Buddy,

I think the Excel Connector is exactly what you are looking for:

http://support.bigfix.com/labs/excelconnect.html

Ben

3

(imported comment written by hmkjr91)

Thank you for the response Ben.

I’ve looked at the Excel Connector; however, here are a couple of questions:

  1. How do I ‘distribute’ the Excel Connector? When I post the spreadsheet to SharePoint for other’s to view, I need the BigFix menu item to be visible to other users without them having to install the Excel Connector.

  2. How can I cause the data I have imported using Excel Connector to refresh automatically when the spreadsheet is opened.

Thanks!

4

(imported comment written by hmkjr91)

Actually, the previous questions I just posted are necessarily accurate. My real question I think is - how can I pull data from BigFix via VBA in Excel 2007?

Thanks!

5

(imported comment written by Lee Wei)

hmkjr

Thank you for the response Ben.

I’ve looked at the Excel Connector; however, here are a couple of questions:

  1. How do I ‘distribute’ the Excel Connector? When I post the spreadsheet to SharePoint for other’s to view, I need the BigFix menu item to be visible to other users without them having to install the Excel Connector.
  2. How can I cause the data I have imported using Excel Connector to refresh automatically when the spreadsheet is opened.

Thanks!

hmkrj,

  1. The BigFix Excel Connector needs to be installed for the individual computers in order to have the capability to connect to BigFix.

  2. This is not currently supported. The only way to refresh the data is to go through the Wizard and click Execute towards the end.

Lee Wei

6

(imported comment written by Lee Wei)

hmkjr

Actually, the previous questions I just posted are necessarily accurate. My real question I think is - how can I pull data from BigFix via VBA in Excel 2007?

Thanks!

I don’t remembering seeing an example of calling the BigFix specific SOAP API from VBA.

Essentially, you need to try to call Web Services (SOAP) from VBA by looking at generic examples.

A quick web search turns up a few such as this one:

http://msdn.microsoft.com/en-us/library/aa140260(office.10).aspx

Lee Wei

7

(imported comment written by hmkjr91)

Thanks Lee.

I had seen that information; however, from what I can tell, that method is no longer available in Excel 2007 and you are “encouraged” to use Visual Studio 2008 to develop an MS Office project. I was hoping for a work around that didn’t include VS.

Thanks again!