1.     Open MS Excel.

2.     Click on Data Tab > From Other Sources > From Microsoft Query

3.     Select ‘SQLBase’ from the list of databases shown and click OK

4.     The ODBC will now established a connection with the database and the following screen will appear. There is no need to enter any details, Click OK

5.     You will now be prompted to the MS Query Wizard.

6.     A list of Tables from the Zavanti database will appear. Locate the required table you require and click the plus (+) sign.

In this example we will use the SUPPLER Master table, CREDHDR.

7.     A list of the Columns in this table will appear. To select a column (and thus have the appropriate data appear on your Excel spreadsheet) highlight the column and press the arrow pointing to the right (>) to shift the column into the window on the right.

In this example we will select the Company Code, Supplier Code, Supplier Name, and ABN

8.     Once you have selected all the appropriate columns, press ‘Next’.

9.     If you would like to filter data, you can select the column and enter the restrictions here. Otherwise, click ‘Next’.

In this example we will select only those records where the Company is 100 i.e. COM_CODE = ‘100’

10.  If you would like the information sorted in a particular order, enter the ‘SORT BY’ criteria here. Otherwise, click ‘Next’.

In this case we will select to sort by Supplier Name, i.e. ‘LAST_NAME’

11.  If you would like to save the query for future use press Save Query. This is helpful when doing a query that will be used several times, or when you want to use the same query on another excel extract. Select an appropriate location and press save.

12.  You will be returned to the final step of the Query Wizard.

13.  Select the option to return the Data to Microsoft Excel (unless you are an Advanced User and would like to continue editing the query in Microsoft Query).

14.  Press Finish.

15.  Select the Cell in the spreadsheet where the information is to appear, and press OK. For advanced users, you can also select if you wish to create a Pivot table from the data you are extracting

16.  The driver connect window will appear again, leave all details as they appear and press OK.

17.  The requested data will appear in your spreadsheet

18.  You can now EDIT your data if you wish, and save the spreadsheet if required.

19.  Once you have saved your spreadsheet, the query will be saved in the excel spreadsheet also

20.  You can REFRESH the data (select again from the database) by opening the spreadsheet and clicking the Data TAB > REFRESH ALL option