excel and access integration

Posted by Bruce Mencer on August 11, 2001 5:01 PM

Here is my problem and I searched the whole board and could not find an answer.
I have set up a data base in access of contacts listing various fields as contact, company etc.
I have excel spreadsheets with product info and pricing.
I have excel spreadsheets that I use for quotes.

Is there a way to set up the quote forms and have different cells pull info from either the pricing spreadsheets or the customer info fields in access?

I am using Office 97.

I always thought I was pretty computer literate until I found this board :-)

Thanks much

Bruce.

If it's real detailed, please email to me at brucemm@sssnet.com

Posted by Damon Ostrander on August 12, 2001 9:08 PM

Hi Bruce,

Yes, it's possible to go either way. Here's a simple example of writing data from Excel cells directly to an Access table from a macro in Excel. You can push or pull Access data in Excel, and you can do the same with Excel data in Access. This example just happens to be push from Excel. This example assumes that appropriate Access database is already open, but would only require a slight change to the GetOpen to open Access and the database.

I hope this helps.

Damon

Sub ToAccess()

Dim axsApp As Object
'Assume Access already open with desired database
Set axsApp = GetObject(, "Access.Application")

Set cdb = axsApp.currentdb
Set rst = cdb.openrecordset("Employees")

Dim iRow As Long 'Excel worksheet row number

'Loop thru fuirst 8 rows on active worksheet and put data from
'first two columns into Access employees table

For iRow = 1 To 8

With rst
.addnew
' read the names from Excel into Access table
!LastName = Cells(iRow, 2)
!FirstName = Cells(iRow, 1)
.Update
End With

Next iRow

End Sub