Inserting values into Link Table

[chrish47]

I have created a database that opens my excel file as a linked table, but now I need to insert values into two "cells" in the table (row 5, col 3 and row 6, col 3). These values will come from another table that holds the month date and year of the current data. I then need to insert the entire contents of another table into the linked table starting at row 10 (I can't overwrite the existing info so I need to insert rows equal to the lengeth of the table either as the data is inserted or before inserting the data)

I am new to VBA for Access and Excel, but have extensive experience with Access and Excel themselves. I have tried doing this with Access Macros, and I can select the cell I need to insert the first value into, but I can't get the setvalue function to work properly.

This is a little confusing but basically I am trying to update a Custom Excel Application by inserting the data values directly from Access rather than having to export the table to Excel and doing a manual copy and paste to get the data into the table. Any help would be greatly appreciated. I guess basic tips on working with linked tables would be helpful.

It would be much easier if I knew how to access the Access objects directly (like the cells function in VBA for Excel) but none of the ADO help items work in my copy of Access. Guidance on accessing tables directly (via row and column or some other means) would be greatly appreciated.

 

[PingPongBoy]

Here are some hints for you to do the job - not saying it is the most elegant but it is good for a one-timer

In Excel you can use ADO to connect to the Access database:

dim con as New ADODB.Connection
dim rTable as ADODB.Recordset
dim sSqlTable as String

con.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & cmndlg.FileName & ";DefaultDir=;UID=;PWD=;"
sSqlTable = "select * from Table"
set rTable = con.Execute(sSqlTable)
Worksheets.Item("Sheet 1").Cells.Item(5,3).Value = rTable("Field")