Microsoft Office Tutorials and References
In Depth Information
After the space for the variables has been reserved, the entire worksheet is cleared by select
ing all the cells on the worksheet ( Cells.Select ) and then clearing the selection with the
ClearContents method. Then the variable i is set to 1, which points at the first row in the
Next a loop is set up that iterates through each row in the Recordset object. This loop contin
ues until the EOF method returns Tr ue , meaning that the current record pointer has moved
beyond the last row contained in the Recordset .
Inside the loop, j is set to 1, which points to the first column in the worksheet. A For…Each
loop is used with the variable f to retrieve each Field object from the recordset’s Fields collec
tion. The value of the field is copied directly from the Va flue property to the appropriate cell
pointed by i and j. After a field is copied, j is incremented to point to the next column.
Once all the fields for a particular row have been copied, the MoveNext method is used to
reposition the current record pointer to the next record. Also i is incremented to point to the
new row in the worksheet.
Using the Excel Query Program
Here’s a short example that uses the Excel Query Program to retrieve a list of customers from
the Northwind sample database that is installed with Microsoft Access. This database is usu
ally kept in the OFFICE11\SAMPLES directory, which might look like the following path if
you installed Microsoft Office in its default location:
c:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb
Note If you can’t find the Northwind sample database in the Microsoft Office\OFFICE11
\SAMPLES directory, verify that you installed the sample databases for Access by rerunning
the Office 11 Setup program.