Linking specific Excel cells to Access

[Guest]

Hi
Is there any way I can link specific cells from various excel spreadsheets
into one column in Access?

Basically I have a column in my Access table called "original budget" and I
would like to extract the "Budget" cell in numerous excel spreadsheets and
put them into Access, and have Access automatically update when I update the
excel spreadsheets.

I can't seem to find any instructions anywhere on how to do this.

Thanks

Katie

 

[Ken Snell [MVP]]

You can link to a worksheet, but not to individual cells in the worksheet...
nor to different cells on different worksheets as a single linked "table".

You may need to look at linking to all the worksheets, and then running a
query to extract your desired data into a "single display table".

However, before digging into that, perhaps it'll be better to step back and
think about what you're trying to do...there may be a better way to
accomplish what you seek. Give us more information and details and let's
see.

 

[Guest]

Ken,

I agree that his design may need some reflection. What he wants to do is
possible, but will require some advanced coding skills in interacting with
Excel.
He could open each spreadsheet as an object, read the value from the cells
he needs, and write those to his table.

 

[Ken Snell [MVP]]

Yes, this would be another approach... completely depends upon what is
desired for using/seeing/interacting with the data.

 

[Guest]

Thank you both for your replies.

The data once imported to Access would be for viewing only. Any updates to
the values are calculated from within excel. The value in the cell in Excel
is calculate through a formula - no doubt complicating this task further!

I am quite new to Access so my coding skills are quite basic...

Katie

 

[Ken Snell [MVP]]

Without more details about the EXCEL files/worksheets/cells, and the ACCESS
tables/queries, it's difficult to provide any specific information. However,
here is some generic code that allows you to open an EXCEL file and go from
there.

This code opens an EXCEL workbook and reads data from it and writes the data
into a recordset. You can modify this as needed to do things directly on the
spreadsheet, etc.


Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing

--

Ken Snell
<MS ACCESS MVP>