Help with If statement based on cell value of Active Sheet

[kt_mr_excel]

I'm obviously very new to VBA in Access and need some help with a very simple If statement based on the value of a specified cell in the active Excel worksheet. My many attempts have included every type of declaration I could find in online examples and many different range expressions but always result in an "error 91 - Object variable or With block variable not set". I also tried using a With block without any luck. Here's my simple sub - I thought that this approach would not require declaration of any variables, but obviously I thought wrong. Any help would be appreciated. (Note: Excel 16.0 Object Library is referenced)

Sub MySub()
If Excel.Application.ActiveSheet.Range("A1").Value = "String to match" Then
MsgBox "Message string"
End If
End Sub

 

[rlv01]

The only way I've ever been able to make something like that work is to open the required Excel workbook.

Option Compare Database
Option Explicit


Sub MySub()
Dim xlWB As Excel.Workbook

Excel.Application.Visible = True

Set xlWB = Excel.Workbooks.Open(FileName:="D:\Users\MyID\Documents\TmpX.xlsm")

If Excel.ActiveSheet.Range("A1").Value = "String to match" Then
   MsgBox "Message string"
End If

xlWB.Close False
Excel.Application.Quit
End Sub

 

[kt_mr_excel]

Thanks very much, but I need to just use the active sheet in the active workbook. Declaring a filepath won't work for me.

 

[Micron]

Whether or not you use a file path has no bearing on this. You need to declare a variable for an Excel object or one of its subordinates then you have to put something in that object by using the Set statement. The posted code does that and should work. However I'm a bit confused by the post. If you're running Access code, how can Excel be the active object unless you are using Excel code to run Access code to work with a spreadsheet, which wouldn't make sense? Perhaps you mean an "open" but that is not the same thing as an "active" workbook.

 

[kt_mr_excel]

Thanks for your feedback. How would I set the object as the open spreadsheet in this case?

 

[welshgasman]

As per post #2 ?

 

[kt_mr_excel]

Perhaps I'm misunderstanding, but the post uses a specific file path and name to set the xlWB variable, whereas I need it to be the currently open workbook.

 

[rlv01]

I think you may be assuming that because you happen to have an open Excel workbook on your PC ,it means your that Access VBA code can automatically "see" its (Excel) objects. But that is not the case. The error message you are getting ( "error 91 - Object variable or With block variable not set") is telling you that "ActiveSheet" is an invalid object.

 

[Micron]

Are you saying that you don't know what that file path is because you might have arbitrarily opened any workbook and then decide to use that one? That can be solved, but then you have the issue where if you have 2 workbooks open I suspect you can't expect Access to just know which one to pick all by itself. That's where a path could come in.

Your initial post was incorrect in assuming you could avoid declaring any objects at all - when this code is in Access. In that case, you need to create that object in a non-native application so that app can work with it. If there is only 1 open, the following can work. If there is more than 1 open, it will 'retrieve' the last active workbook AFAIK, in which case it will likely error.

Sub testActiveWkb()
Dim xl As Excel.Application

Set xl = GetObject(, "Excel.Application")
If xl.Worksheets("sheet1").Range("A1")  "SomeStringHere" Then
MsgBox "Message string"
End If

End Sub

 

[kt_mr_excel]

I understand that the Workbook object needs to be set first but have been asking how to do that for the open workbook without referencing a file path.

After receiving a suggestion from another forum, I tried setting the workbook as shown below and now everything works fine.

Set wb = GetObject(, "Excel.Application").ActiveWorkbook