Is it possible to import active Excel worksheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use the TransferSpreadsheet method of the DoCmd object to
import the worksheet that a user currently has open, so that as he/she tabs
from one sheet to the next it is possible to bring in whatever sheet they
are currently on (all have the same layout, are just for different days'
data). Is that possible? Thanks.
 
Hi Mary,

Try something like this (which is something between pseudocode and air
code).

The first thing to do is to create an Excel workbook object that points
to the workbook. This is easy if there's only one instance of Excel
running:
Set oXLApp = GetObject(, "Excel.Application")
Set oXLBook = oXLApp.ActiveWorkbook
or if you know the name and path of the workbook
Set oXLBook = GetObject("D:\My folder\My workbook.xls")

You need to know or get the name of the workbook for the import anyway,
e.g. with
oXLBook.FullName
and you can get the worksheet name with
oXLBook.ActiveSheet.Name

You may also need to ensure the workbook is saved before importing, to
get any changes the user has made.

After running the import, be sure to release any references you have to
the workbook and/or the Excel applicaiton object, e.g.
Set oXLBook = Nothing
Set oXLApp = Nothing
 
Back
Top