Extract data from an open Excel File (1 Viewer)

twcaddell

Registered User.
Local time
Yesterday, 19:15
Joined
Apr 8, 2013
Messages
31
I receive an excel file about once a month where I need to extract only portions of the data and place in existing records of a table. I have no desire or need to keep the file so would like to open the file from Outlook and then retrieve the data from within access without having to save the file to a folder and then open it using VBA within Access. I cannot figure out how to create the instantiation to the file since it's already open. Would appreciate any help I get.

Thanks TC
 

twcaddell

Registered User.
Local time
Yesterday, 19:15
Joined
Apr 8, 2013
Messages
31
Posted my question too soon without enough research. For those interested, here is an extract of my solution
Code:
Dim xlApp As Excel.Application
Dim xlWrkBk As Excel.Workbook
Dim xlsht As Excel.Worksheet
Dim PONum As String

    Set xlApp = GetObject(, "Excel.Application")
    Set xlWrkBk = xlApp.ActiveWorkbook
    Set xlsht = xlWrkBk.ActiveSheet
    
    PONum = xlsht.Cells(1, 1).Value

The "GetObject" call returns the active workbook. If there are no workbooks open, then it will fail. The xlWrkBk and xlsht build off the returned object.

Thanks all who looked at my question

TC
 
Last edited:

Users who are viewing this thread

Top Bottom