• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3411
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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