• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3497
  • Last Modified:

Access VBA -- Object variable or With Block variable not set

I am using Access VBA to open an Excel workbook, and read the workbooks contents into an array.

50% of the time I get the following error.  I have market the code line where the error occurs

Run-time error '91';
Object variable or With Block variable not set.

           Dim wbk As Excel.Workbook
            Set wbk = ExcelApp.Workbooks.Open(FileName:=CurrentProject.Path & "\" & strFile)
            '''''''''''''''''''''''''''''''''''''
            ' Get Control Tower sheet data
            '''''''''''''''''''''''''''''''''''''
            wbk.Sheets("Control Towers").Activate
            wbk.ActiveSheet.cells(1, 1).Select
            wbk.ActiveSheet.cells(1, 1).CurrentRegion.Select
            Dim rng As Excel.Range
            Set rng = Selection
            
            ColCount = rng.Columns.Count  <---   ERROR ON THIS LINE
            RowCount = rng.Rows.Count

Open in new window

0
rrhandle8
Asked:
rrhandle8
  • 5
  • 2
  • 2
1 Solution
 
MacroShadowCommented:
Do you have a selection when the error occurs?

The error indicates that the range wasn't defined, and since you populate the range with the selection, it would seem that when the code errs out there is no valid selection.
0
 
rrhandle8Author Commented:
Yes, I have a selection.  I have made the workbook visible, and I can see the current region is selected.
0
 
rrhandle8Author Commented:
I should have added that the selection in the code is saying it is nothing.  So I guess you are right, but why is the selection - nothing when I can see the area in the workbook is selected?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
MacroShadowCommented:
If selection.Address is nothing I really don't know what to say.
0
 
rrhandle8Author Commented:
The solution is below


            'Dim rng As Excel.Range
            'Set rng = Selection.Address

            ColCount = wbk.ActiveSheet.cells(1, 1).CurrentRegion.Columns.Count
            RowCount = wbk.ActiveSheet.cells(1, 1).CurrentRegion.Rows.Count

Open in new window

0
 
clarkscottCommented:
Dealing with Excel in VBA is sometimes challenging.  Whenever things don't seem to work, be as specific as possible.

original:
ColCount = rng.Columns.Count  

new:
ColCount = wbk.rng.Columns.Count  

Scott C
0
 
rrhandle8Author Commented:
Scott,

ColCount = wbk.rng.Columns.Count  still gives same error:



Run-time error '91';
Object variable or With Block variable not set.
0
 
rrhandle8Author Commented:
Scott,

Interesting find.

            Dim rng As Range
            Set rng = wbk.ActiveSheet.Range("testrange2")
            Debug.Print wbk.rng.Columns.Count

That gives an error, but this works

            Debug.Print wbk.ActiveSheet.Range("testrange2").Columns.Count

Am I doing something wrong with the way I am setting the range?
0
 
clarkscottCommented:
How about attaching it and let me see it.  Provide the Access and Excel files.

I'll figure it out for you.

Scott C
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now