Help with If statement based on cell value of Active Sheet

kt_mr_excel

New Member
Joined
Sep 24, 2021
Messages
15
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
The only way I've ever been able to make something like that work is to open the required Excel workbook.

VBA Code:
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
 
Upvote 0
Thanks very much, but I need to just use the active sheet in the active workbook. Declaring a filepath won't work for me.
 
Upvote 0
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.
 
Upvote 0
Thanks for your feedback. How would I set the object as the open spreadsheet in this case?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top