Pull specific Excel cells into field

[Joe6Pack]

I have a database that needs to pull data from Excel.

Specifically, my Access field should go directly to a
cell in Excel and get that value.

I'm thinking that I use some kind of VBA code to open my
Excel file and assign the cell contents to a variable
which I then use in Access. I think I can find the cell
in Excel using a named range.

I need the exact code, though.

How do I do this?

Thx.

Joe

 

[Ken Snell]

This is a generic code for opening an EXCEL workbook in "read-only" mode,
reading the contents of a specific cell on a specific worksheet, saving that
value into a variable (VariableName), and then closing the workbook and
EXCEL down.

Dim xlsApp As Object, xlsWB As Object, xlsWS As Object, xlsRng As Object
Set xlsApp = CreateObject("Excel.Application")
Set xlsWB = xlsApp.Workbooks.Open("C:\FolderName\FileName.xls", , True)
Set xlsWS = xlsWB.Worksheets("WorkSheetName")
Set xlsRng = xlsWS.Range("A1")
VariableName = xlsRng.Value
Set xlsRng = Nothing
Set xlsWS = Nothing
xlsWB.Close False
Set xlsWB = Nothing
xlsApp.Quit
Set xlsApp = Nothing

 

[Joe6Pack]

Ken, thanks. that helps alot.

How can I pass that variable from Excel to Access?

Joe

-----Original Message-----
This is a generic code for opening an EXCEL workbook in "read-only" mode,
reading the contents of a specific cell on a specific worksheet, saving that
value into a variable (VariableName), and then closing the workbook and
EXCEL down.

Dim xlsApp As Object, xlsWB As Object, xlsWS As Object, xlsRng As Object
Set xlsApp = CreateObject("Excel.Application")
Set xlsWB = xlsApp.Workbooks.Open

("C:\FolderName\FileName.xls", , True)

 

[Ken Snell]

The code example that I posted would be run from inside ACCESS. Thus, the
variable already in in ACCESS.

 

[Joe6Pack]

Thank you. It works exactly how I want it.

Joe

-----Original Message-----
The code example that I posted would be run from inside ACCESS. Thus, the
variable already in in ACCESS.

--
Ken Snell
<MS ACCESS MVP>




.