Writing an Excel macro to place Access fields in specific cells.

Hi,

I am trying to write an excel macro that takes specific fields in an Access table and places them in cells located in different parts of the spreadsheet. The logic of what I am trying to do is:

SELECT tblContracts* from tblContracts

tblContracts.ContractNumber = Cells.("a1")
tblCotracts.ContractName = Cells.(b12)

Any help would be appreciated.

Hi

The following will connect to the database, select all the items from the table, and put the entire output on the sheet starting in A1.

Please Login or Register  to view this content.

This is pretty generic, and if you don't want it all blocked together like this, then there are ways to get either individual columns from the selected data, or individual records from columns to go to specific places.

Need more detail on your specifics to go further.


rylo

Rylo,

Thanks for the code. It will definitely put me on the right track. What I am tring to do is bind several fields in the Contracts table to cells in different locations on a spreadsheet. For example:

binding field Contract Name to cell E12.
binding field Bill To Company to cell B8.
binding field Bill To Address1 to cell B9.
binding field Quote# to cell E8.

Does it make sense to create a recordset for each field in the Contracts table, and bind them to their respective cells?

thanks,

Mike

Mike

How many records are likely to be returned from your query? If there is only going to be 1 record, then pasting them to individual cells isn't a problem.

If there are multiple results, how do you want to handle? Paste them in 1 record at a time in a loop, then force some processing?


rylo

Rylo,

I have an Access form with a subform inside it. The form is linked to the Contracts table, and the subform is linked to the Orders table. The Orders table has a one-to-many relationship with the Contracts table. I need to pull data from both tables into an Excel doc that looks exactly the same. Here is a rough example of the layout:

(Form)
Contract: 123456 Date: 11/22/07
Address: 1223 Oak Phone: 111-22-3333

(Subform)
Order: Product
1 widget
2 gadget
3 widget
4 gadget

Thanks,

Mike

Mike

This sort of slipped away from me.

I think you will have to build 2 recordsets. The first will contain the 4 fields from tblContracts which match the contract number. This should bring back 1 record (if I read things correctly) with 4 fields. The individual fields can be sent to the different cells

range("A1").value = rs.fields(0)
range("B16").value = rs.fields(1)
etc.

The second recordset brings back (2???) fields from the orders table. I'd use the copyfromrecordset approach to put it to the output location as per the example I originally gave you.


HTH

rylo