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
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
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