Récupérer les données d'une appli dans Excel

Hi to ALL. One more trick with Excel and AC…
In case if you are not using Connect-It with Service Console tool or not familiar how to use it, please check next steps for automating reports in Excel format out of AC:

Step 1. Save your monthly report in Excel format on a shared drive…

Step 2. By using AC EXPORT tool, prepare select statement with fields and conditions like in your AC view and from PREVIEW tab of Query on table in AC Export copy entire SQL Select statement like [SELECT A1.AssetTag, A1.SerialNo, A1.Status FROM amAsset A1 WHERE A1.SerialNo is NOT NULL AND A1.Status = 'In Stock']…

Step 3. In your Excel report from DATA menu select IMPORT EXTERNAL DATA submenu and there click on NEW DATABASE QUERY submenu. From CHOOSE DATA SOURCE dialog box select AssetCenter Databases* and at the bottom of this dialog box uncheck option USE THE QUERY WIZARD… Click OK.

Step 4. From next dialog box CONNECTION TO DATABASE provide your AC login ID and password info and click OPEN.

Step 5. In the ADD TABLES dialog box of MICROSOFT QUERY click CLOSE…

Step 6. From the toolbar of MS QUERY click on SQL and paste there your SQL statement from the PREVIEW tab of AC Export tool. Click OK.

Step 7. Click on OK in the message box: SQL QUERY CAN’T BE REPRESENTED GRAPHICALLY…

Step 8. You will see QUERY FROM AC DB window with criteria, fields and data from AC at the bottom of the window. Save this query like: QUERY 001 FROM AC DB.dqy and from the toolbar click on EXIT MS QUERY button.

Step 9. In the IMPORT DATA dialog box select option EXISTING WORKSHEET with option: =$A$2 and click OK. Save this report and each time you open it and click on REFRESH ALL button in the toolbar, you will see up to the minute data from AC like in your view..

Best Regards, Ed K.

Cliquez pour agrandir...