Get/Link a specific field in Access to a specific cell in Excel

[Sciron]

Hello everybody,

I've tried to find a solution how to get information from Excel to Access. As I'm new to this forum I haven't read every thread but so far i haven't found what i think I'm looking for.

I work in a CAD software that uses an Access-database for it's Parts Database Manager. This db contains a lot of information for the specific part, like size, price, manufacturer and so on.
As a standard we receive prices on parts in an Excel file. What I'd like to do, if it's at all possible, is to link to the parts price-field in the Access-db from the cell in the Excel-file that contains the price information.
As a basic idea I think of the way you can, in Excel, link one cell in one file to another cell in another file and get a dynamic update.

The basic reason is that some of the people that update the prices has NO knowledge about CAD or Access and therefor is this the best idea I can come up with.
A type of lookup-function that gets the numbers in the Excel-file when the Access-db starts could be another solution.



I'm not looking to import the whole file or create a new db or anything like that. But it has to be on a cell-level as it's not all cells thats going to be updated.

Can this be done????

Please help!

Sincerely
Thomas

[ranman256]

Yes but involves some vba coding...
usage: txtbox = GetXlCellValue("c:\myfile.xls","sheet1","A14")

'REMEMBER YOU MUST ADD EXCEL IN VBE menu:  tools, references, Microsoft Excel xxx.x  object library

Public Function GetXlCellValue(byval pvFile, ByVal pvSheet, ByVal pvCell)
Dim XL As Excel.Application
On Error Resume Next
Set XL = CreateObject("excel.application")
With XL
    .Visible = False
    .Workbooks.Open pvFile
    .Worksheets(pvSheet).Activate
     GetXlCellValue = Range(pvCell).Value
    
    .Quit
End With
End Function