Getting data from an open Excel file into Access

[A]

Hi,

I wasn't certain where to post this so you may come across
it under "Importing,Exporting, Linking".

I need to get data from an open Excel file into Access
using automation. I declare Application, Woorkbood, and
Worksheet objects and an integer variable in which to get
the data like this:

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim intExcelValue As Integer

I then set the Application object to an existing instance
of Excel like this:

Set objExcel = Excel.Application

At this point I can't figure out or find how to identify
the specific workbook file from which I need to get the
data. I have tried:

With objExcel
Set objWorkbook = objExcel.ActiveWorkbook
Set objWorksheet = objExcel.ActiveWorksheet
End With

but when a check the values of these object they read "Nothing"

Since I want to get to the following:

intExcelValue = objWorksheet.Cells(3, 8).Value

I need a way to let the code know the specific open file
"C:\Data.xls".

This should be simple but I haven't found a way to do it.

Any help or alternatives, much appreciated.

A

 

[Casey]

You can use the TransferSpreadsheet action in a macro
to import your excel data. In the criteria for that
action you can enter the Transfer Type, Spreadsheet Type,
Table Name to be imported to, File Name to be imported
from and other specifications.

This is alternative to using VBA coding.

I hoped I helped.

Casey