Solved Reading data from an open spreadsheet (1 Viewer)

RJM24

New member
Local time
Today, 06:50
Joined
May 3, 2021
Messages
6
Hi

I am sure this has been covered but I cannot find it.

I have an Access DB where I create Spreadsheets based on queries and that works well, I also open Spreadsheets to read data into a table, again, it works well.

Where I am struggling is to read from an already open Spreadsheet. I have searched and managed to find a few examples but I cannot get them to work, this is what I have (Just a small test Sub to get the basics working but it always errors on the "Set WB = XL.Workbooks("Test.xlsx")" line with "Subscript out of range" error

Code:
Private Sub Test()
    Dim XL As Object
    Dim WB As Object
    Dim sStr As String
    
    Set XL = VBA.CreateObject("Excel.Application")
    
    Set WB = XL.Workbooks("Test.xlsx")
    
    sStr = XL.ActiveCell.Value

   WB.Close
   XL.Quit

End Sub

I have what I need to do working well if I open the Spreadsheet but in practice it will nearly always already be open so would really like to get this working if I can

Any help or pointers would be greatly appreciated

Thanks

RJM24
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:50
Joined
Mar 14, 2017
Messages
8,738
Not that I recommend what you're doing at all, really, but here are 2 things that might help you accomplish it the way you're doing it

1) use GetObject instead of CreateObject to Set XL
2) link to the spreadsheet inside Access. Access will probably only "see" the most updated changes however recently the following 2 things have occurred: a) the spreadsheet has been saved, b) your Access code/query has re-opened, or been re-queried in some manner
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:50
Joined
Feb 19, 2002
Messages
42,981
I agree with Isaac. Access does not play well with others. At some point you will run into sharing issues if people are actively updating the linked sheet.

Do some testing. Does Access "see" live updates or does Access "see" what existed when it first opened or when the link was first established.

Rather than linking to the workbook, I would probably import it to a temporary database just to minimize Access' hold on the workbook. Since this will obviously cause bloat, I would use the template database method where I define a database with a temp table defined to hold the spreadsheet. With the table empty, compact and repair and save it in a master folder. Then each time you want to work with the spreadsheet, delete any existing version of the template table in the local folder and then copy the template from the master folder into the folder with the FE. As long as you have linked to this database in the past and its name hasn't changed, the links will still work. If anything has changed regarding the name or structure, you will need to relink the table.
 

conception_native_0123

Well-known member
Local time
Today, 01:50
Joined
Mar 13, 2021
Messages
1,826
You should declare Excel object as new object. That may be one way to solve problem. Another way to solve problem might be to use index number of open workbook to get it instead of actual name of workbook as in file name. Also the create object statement don't need a qualifier I don't think.

Code:
Private Sub Test()
    Dim XL As Object
    Dim WB As Object
    
    Set XL = CreateObject("Excel.Application")
    
    Set WB = XL.Workbooks(1)
    
    sStr = XL.ActiveCell.Value
End Sub
Or maybe this may work
Code:
Private Sub Test()
    Dim XL As excel.application
    Dim WB As excel.workbook
    Dim sStr As String
    
    Set XL = new Excel.Application
    
    Set WB = XL.openWorkbooks(file path in this part)
    
    sStr = XL.ActiveCell.Value

   WB.Close
   XL.Quit

End Sub
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:50
Joined
Mar 14, 2017
Messages
8,738
As to the first block of code:
If you create a new instance of the Excel application, that Excel application won't have any Workbooks belonging to it at all - including not the one in question for the OP.

As to the second block of code:
I think you meant .Workbooks.Open rather than .OpenWorkbooks
But, I understood the OP needed to "do" something to an already-open workbook.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:50
Joined
Feb 28, 2001
Messages
27,001
OK, it has been a while since I've played with this, but doesn't EXCEL complain vehemently when you try sharing an already-open workbook that was opened "normally"? I.e. it was NOT opened with the ReadOnly option? At least some versions of Excel don't play AT ALL well with others when the topic is opening for shared write.

I think the acid test here is whether you can open that already open workbook with ANOTHER instance of Excel. If you can't open it twice with Excel then you won't be able to open it with Excel once and an Excel App Object via Access as the second opening.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:50
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub test()
    'replace ExcelNameHere with the correct name of excel file
    Const WB_NAME   As String = "ExcelNameHere.xlsx"
    Dim XL          As Object
    Dim WB          As Object
    Dim sName       As String
    Dim sStr        As String
On Error Goto Err_Handler
    Set XL = GetObject(, "Excel.Application")
    For Each WB In XL.workbooks
        If WB.Name = WB_NAME Then
            sName = WB_NAME
            Exit For
        End If
    Next
    If Len(sName) <> 0 Then
        sStr = XL.activecell.Value
    End If
    Debug.Print sStr
Err_Handler:
    Set WB = Nothing
    Set XL = Nothing
End Sub
 

RJM24

New member
Local time
Today, 06:50
Joined
May 3, 2021
Messages
6
Thanks for the responses and ideas.

The program is a single user, I download data from the web and add to an existing spreadsheet, currently I manually enter the data into a form in Access based on several criteria. I have automated this in the past by loading into a datasheet from a temp table and then make any corrections needed before importing into the main data table. While it worked , I was missing some records as programmatically it is very difficult to select the correct records.
What I am doing here is with the open spreadsheet, select a cell on the row I want to (ActiveCell) gets me the row and then I can read the columns for that row, populate the form and then save if I am happy with it. I do this now but only if I open the spreadsheet in Access. Once benefit to this is I can have Access on one scree and Access on the other which makes it easy to see the 'whole' picture as I work through the records that do need to be imported. There are no sharing issues with other users, just between Excel and Access and the data will not change while I have the Spreadsheet open.

If I don't add the 'VBA' qualifier it get an undefined object error

There are a few good suggestions, so I will have a look at what has been suggested and see if I can get it working.

If not, then I am considering the following

Open my Access Form
Open the Excel Workbook in the OpenForm sub
Then for each record, select a cell in Excel, and then press a button in Access that has the code to read the data from the Row the ActiveCell is in, I can the repeat this for the number or rows I need to import (typically 5 or in a session) and the close and quit Excel in the CloseForm sub

Thanks again
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:50
Joined
Mar 14, 2017
Messages
8,738
I see no reason why you can't use GetObject to get the open excel instance and set it to a variable meant for the excel application
then use that application object's Workbooks() collection to refer to the open workbook
declare & set object variables to the appropriate worksheet if needed.

you really should never write code that depends on Active or Activate or Select or Selection in excel vba, but if you must, the above method should work....until it doesn't because (for example), some user who has 5 workbooks open at the same time runs it, and then Active Cell won't be reliable.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:50
Joined
Feb 19, 2002
Messages
42,981
Which version of the data is the master? Is Excel the master or is Access the master? Updating data in two different files independently leaves you with serious reconcilliation problems.

If Excel is the master, then either always replace the old data with what is in the current version of the spreadsheet or never import it at all. Just link to the spreadsheet.

How are you determining what records need to be imported? Updated?
 

RJM24

New member
Local time
Today, 06:50
Joined
May 3, 2021
Messages
6
The Data I am reading is down loaded from a portal in an Excel file, at the moment I copy paste records into an Access form, as the process is manual and on a single PC, once I have the spreadsheet open, I will place the cursor on the row I want to read, using ActiveCell.Row I can then read across the columns to get the value from each cell on that row I need an write them to the form.
Using a linked table does not give me the flexibility, I have that functionality now. In theory it is the right way to go but in practice in causes issues as the record has many fields and it is easier to look at it manually in Excel to decide which records need importing

Thanks again for the points, I have not been able to try the above suggestions yet, am hoping to get some time to look at it over the weekend and will let you know how I get on
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:50
Joined
Mar 14, 2017
Messages
8,738
Thanks for the extra information. I respect your opinion on this, and I do understand a bit better, now, 'how' you came to arrive at that conclusion, but I have to admit I still think it's not the best way to go. It would be better to automate the import process according to criteria by linking and SQL/query logic - even if you have to present some 'summary' information to an end user on a screen to tick-mark the record they want 'permanently' imported from the 'temp' table, which can be done.

The way you are doing it by using ActiveCell in Excel is not the best way to go long term IMHO. Good luck with your project
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:50
Joined
Feb 19, 2002
Messages
42,981
I'm with Isaac on this. Somehow, you can look at a row in Excel and determine that it needs to be imported. If you can codify that for us, we can help you to automate the import. Using a query requires only a single line of code and has to be simpler than using OLE automation to loop through the spreadsheet.
 

RJM24

New member
Local time
Today, 06:50
Joined
May 3, 2021
Messages
6
Code:
Private Sub test()
    'replace ExcelNameHere with the correct name of excel file
    Const WB_NAME   As String = "ExcelNameHere.xlsx"
    Dim XL          As Object
    Dim WB          As Object
    Dim sName       As String
    Dim sStr        As String
On Error Goto Err_Handler
    Set XL = GetObject(, "Excel.Application")
    For Each WB In XL.workbooks
        If WB.Name = WB_NAME Then
            sName = WB_NAME
            Exit For
        End If
    Next
    If Len(sName) <> 0 Then
        sStr = XL.activecell.Value
    End If
    Debug.Print sStr
Err_Handler:
    Set WB = Nothing
    Set XL = Nothing
End Sub

Finally got some time to try this over the weekend and your code does exactly what I wanted it to do so I guess the key was using 'GetObject' rather than the 'CreateObject' I was using

I have expanded this a bit and the next issue I have hit is getting the Worksheet object, this is the code I have added into this test routine

Code:
If Len(sName) <> 0 Then
        sStr = XL.activecell.Value  ' this returns the value in the cell that cursor is in correctly
        i = XL.activecell.Row ' This returns the active row correctly
        
        Set WS = XL.WorkSheets("Data")
        sText = XL.Cells(7, i)   ' sText is always empty even though there is data in column 7 on the active row
        
    End If

There is only 1 Workbook open (Test.xlsx) and it has only 1 tab called "Data", I tried
Code:
Set WS = WB.Worksheets("Data")
and I get the same empty string returned

I also tried
Code:
Set WS = XL.WorkSheets(1)
with the same result

What am I doing wrong

Thanks
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:50
Joined
Mar 14, 2017
Messages
8,738
the key was using 'GetObject' rather than the 'CreateObject' I was using
(y)

For the rest of it, can you please post the full code procedure? Just want to make sure I'm not getting confused. Then I'll comment on why I think the setting of the worksheet variable isn't working.

Generally speaking:
1. once the excel application variable is created, set the workbook variable as: Set WorkbookVariable=ApplicationVariable.Workbooks("name.xlsx")
2. once that is done, set the worksheet variable as: Set WorksheetVariable=WorkbookVariable.Worksheets("Name") (or...(#))

In order for that to work, you MUST do either 1 of the following:
1) use late binding, and have the variables declared as Objects--including the app variable, workbook variable, and worksheet variable
2) use early binding, and have the variables declared as Excel.Application, Excel.Workbook, and Excel.Worksheet, respectively
Sometimes you see code that mixes and matches the 2, but that's confusing at best and really makes no sense. What you want is to understand the binding choice, make a decision that seems best for your case, and stick to it. (IMO)
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:50
Joined
Feb 28, 2001
Messages
27,001
While there are those people who say "consistency is the hobgoblin of little minds" - and I agree with them often - there is this to consider: App objects have no mind at all. For them, ONLY consistency will work.

When I was doing some things with Excel to make a family tree diagram, I had to be EXTREMELY consistent in always using the same type of binding - in my case, LATE binding - to keep things going. If I was careless, I would get Excel error 1004, which is their dreaded "catch-all" message. In effect it says "you screwed buddy but I'm damned if I can explain it to you." (OK, it REALLY is "Application-defined Error" - but given the lack of further explanation, it might as well be what I said first.)
 

RJM24

New member
Local time
Today, 06:50
Joined
May 3, 2021
Messages
6
I have found the issue, my additions were working but I have the row and column around the wrong way on my call to XL.Cells to get the cell data from that cell

Thanks to you all for the help, I now have what I was missing to get this to function as I wanted

just for the record, this is the test function I am using that returns the data in the 7th column on the row the cursor is on

Code:
Private Sub btnTestConnetion_Click()

    'replace ExcelNameHere with the correct name of excel file
    Const WB_NAME   As String = "Test.xlsx"
    Dim XL          As Object
    Dim WB          As Object
    Dim WS          As Object
    Dim sName       As String
    Dim sStr        As String
    Dim sText       As String
    Dim i           As Integer

    On Error GoTo Err_Handler
    
    Set XL = GetObject(, "Excel.Application")
    
    For Each WB In XL.workbooks
        If WB.Name = WB_NAME Then
            sName = WB_NAME
            Exit For
        End If
    Next
    
    If Len(sName) <> 0 Then
        sStr = XL.activecell.Value  ' this returns the value in the cell that cursor is in
        i = XL.activecell.Row ' This returns the active row #
        
        Set WS = XL.WorkSheets("Data")  
        sText = WS.Cells(i, 7)    ' returns the data in the 7th column on the selected row
        
    End If
    
    Debug.Print sText

Err_Handler:
    Set WB = Nothing
    Set XL = Nothing
    Set WS = Nothing

End Sub

Thanks again

RJM24
 

Users who are viewing this thread

Top Bottom