How to use VBA in automating a reporting tool installed in windows OS?

[mine0103]

Hello All,

I recently joined this group. My name is Mithilesh. I was looking for experts advice on automating a reporting tool through VBA.
The reporting tool is an application installed in windows OS. I am looking for a way to automate the downloading of reports from this tool.
Any help would be highly appreciated.
Please let me know if more information is needed on this.

Regards
Mithilesh

[mainemojo]

Without much specifics, I would think you won't want to use VBA for this as it is for automation within MS Office - if anything, try PowerShell or VBS/HTA.

[mine0103]

Hi Mainemojo,

Thank you for replying. So you are saying that it is not possible through VBA. Is it?

I am not sure about Powershell or VBS/HTA. Do you need more information about the reporting tool? Can you help me with this?

Regards
Mithilesh

[mainemojo]

You can use VBA. Problem with using only VBA is that you will need to open one of the Microsoft Office products in order for your script/macro to run. This relies on you opening up your application to have the script run.

Personally, I'd prefer to have a script that I can 'set it and forget it'! With Powershell or VBS/HTA, you can. If you have Windows 7 or newer, both are included as part of the OS. VBS is like VBA but can run straight from Windows. HTA is more of an HTML based version of VBS.

Why don't you elaborate on the reporting tool?

[mine0103]

Hi Mainemojo,

The tool is IBM's On-Demand reporting tool. We have reports in this tool and would require to download the report from this tool which is a time consuming task. So I was thinking to automate this job.
I don't have much clue on VBS/HTA. Can you help me with this?

[mainemojo]

So here's what you can do. Not sure what version of On-Demand you have, but you can use macros in On-Demand. Depends on how automated you want this, you can use Excel to connect using On-Demand to your database (look at this link from IBM on Host On-Demand Macro Programming Guide. The link will help you with creating an ODBC connection from Excel to your database. Then you can use an On-Demand macro to download the specific data you want, and import it into Excel. You would then use VBA to manipulate the data in Excel to how you want it to look.

You'll have to figure out the On-Demand macro and ODBC connection yourself as I don't have access to your system or data. Once you get the data into Excel, myself or others in this group could help you with VBA to manipulate the data how you want it to look.