Activating a different workbook and performing functionality in that workbook

Question

I have 'Workbook1' which has command buttons to perform certain operations. When a button is clicked in this workbook, it downloads a different workbook from Outlook and opens that workbook with a variable name, which is 'Workbook2'. After this, I want to set a filter in that workbook. But I'm not able to do that. I am getting 'Object Variable or With Block Variable not set' error. Below is my code.

Dim EXCELApplication As Object Dim DefPath As Variant Dim wb As Workbook Dim wbName As String Dim col2 As Long Dim colNameF As Long Dim colNameF1 As Long Dim colNameF2 As Long ' Other Relevant Code Present Here' DoEvents Set EXCELApplication = CreateObject("Excel.Application") EXCELApplication.Workbooks.Open (DefPath & strExt & ".xlsb") EXCELApplication.Visible = True EXCELApplication.Sheets("Release Level View").Activate colNameF = Range("A8:DD8").Find(What:="Teams", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False).Column colNameF1 = Range("A8:DD8").Find(What:="Items", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False).Column colNameF2 = Range("A8:DD8").Find(What:="Domain", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False).Column ActiveSheet.Range("$A$8:$DD$9999").AutoFilter Field:=colNameF, Criteria1:="ST Test", Operator:=xlOr, Criteria2:="" ActiveSheet.Range("$A$8:$DD$9999").AutoFilter Field:=colNameF1, Criteria1:="Variance", Operator:=xlOr, Criteria2:="" ActiveSheet.Range("$A$8:$DD$9999").AutoFilter Field:=colNameF2, Criteria1:="9S", Operator:=xlOr, Criteria2:=""

I'm getting the error in this particular line.

colNameF = Range("A8:DD8").Find(What:="Teams", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False).Column

Even if i use ActiveSheet.Range.. I'm still getting the same error. Can someone tell me what the problem is?

Thanks BK201, even when I use Set, i'm still getting the same error. Here's the complete code for your understanding.

With targetSht Set aCell1 = EXCELApplication.Range("A8:DD8").Find(What:="Feb", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False) If Not aCell1 Is Nothing Then col2 = aCell1.Column SV1 = Split(Cells(col2).Address, "$")(1) lRow1 = .Range(SV1 & .Rows.Count).End(xlUp).Row End If colNameF = .Range("A8:DD8").Find(What:="Teams", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False).Column colNameF1 = .Range("A8:DD8").Find(What:="Items", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False).Column colNameF2 = .Range("A8:DD8").Find(What:="Domain", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False).Column .Range("$A$8:$DD$9999").AutoFilter Field:=colNameF, Criteria1:="ST Test", Operator:=xlOr, Criteria2:="" .Range("$A$8:$DD$9999").AutoFilter Field:=colNameF1, Criteria1:="Variance", Operator:=xlOr, Criteria2:="" .Range("$A$8:$DD$9999").AutoFilter Field:=colNameF2, Criteria1:="9S", Operator:=xlOr, Criteria2:="" .Cells(lRow1 + 1, SV1).Select Selection.NumberFormat = "0" SumV1 = SV1 & "9" SumW1 = SV1 & lRow1 .Cells(lRow1 + 1, SV1).Formula = "=SUBTOTAL(9," & SumV1 & ":" & SumW1 & ")" .Cells(lRow1 + 1, SV1).Select Selection.Copy End With Windows("DS.xlsx").Activate Set FindV = Range("A1:Z100").Find(What:="Dec Rel", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False) FindV.Offset(0, 4).NumberFormat = "0" FindV.Offset(0, 4).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Whereever the 'cells' method is encountered, I get the same error everywhere.

I don't think that's the problem. B'coz 'Teams' is a valid header. I think its not recognizing the active sheet. — RB49
– RB49, Commented Dec 5, 2013 at 8:05
in that case I think the answer below makes sense. Didn't realize you created another instance — sam092
– sam092, Commented Dec 5, 2013 at 8:13

WGS · Accepted Answer · 2013-12-05 10:59:42Z

Two things:

(1) Using ActiveSheet or just going straight to Range means you're running from the workbook that's calling the macro. Since you opened a new book, you have two workbooks active, but you are targeting the current workbook with the way your code is set up.

(2) This code block might cause some problem:

Set EXCELApplication = CreateObject("Excel.Application") EXCELApplication.Workbooks.Open (DefPath & strExt & ".xlsb") EXCELApplication.Visible = True EXCELApplication.Sheets("Release Level View").Activate

Notice: EXCELApplication.Sheets. If it works, it still is bad coding. Change it to something like this:

Dim targetBk as Workbook Set xlApp = CreateObject("Excel.Application") With xlApp Set targetBk = .Workbooks.Open (DefPath & strExt & ".xlsb") .Visible = True End With targetBk.Sheets("Release Level View").Activate

And even then, Activate and its ilk are bad things. Better to be more explicit and go with:

Dim targetBk as Workbook, targetSht As Worksheet Set xlApp = CreateObject("Excel.Application") With xlApp Set targetBk = .Workbooks.Open (DefPath & strExt & ".xlsb") .Visible = True End With Set targetSht = targetBk.Sheets("Release Level View") With targetSht colNameF = .Range("A8:DD8").Find(What:="Teams").Column colNameF1 = .Range("A8:DD8").Find(What:="Items").Column colNameF2 = .Range("A8:DD8").Find(What:="Domain").Column .Range("$A$8:$DD$9999").AutoFilter Field:=colNameF, Criteria1:="ST Test", Operator:=xlOr, Criteria2:="" .Range("$A$8:$DD$9999").AutoFilter Field:=colNameF1, Criteria1:="Variance", Operator:=xlOr, Criteria2:="" .Range("$A$8:$DD$9999").AutoFilter Field:=colNameF2, Criteria1:="9S", Operator:=xlOr, Criteria2:="" End With

Hope this helps!

EDIT: Regarding your comment, always remember that Find returns a Range by default. Which means that if you assign it to a variable without any other property, you are assigning a range to a variable. Needless to say, this needs Set to happen properly. See below:

Sub Test() Set aCell1 = Range("A1:DD8").Find(What:="Feb") col2 = aCell1.Column SV1 = Split(Cells(col2).Address, "$")(1) lRow1 = Range(SV1 & Rows.Count).End(xlUp).Row ActiveSheet.Cells(lRow1 + 1, SV1).NumberFormat = "0" End Sub

The above works now, because col2 can properly recognize aCell1 as a Range with a .Column property. Also, your formatting line (...NumberFormat = "0") is correct.

Let us know if this helps.

EDIT2: Your usage of Cells should always be qualified. If you're using With targetSht, then SV1 = Split(.Cells(col2).Address, "$")(1). Notice the . there in .Cells. Anyway, try changing the block of code to my modification below:

With targetSht Set aCell1 = .Range("A8:DD8").Find(What:="Feb") If Not aCell1 Is Nothing Then col2 = aCell1.Column SV1 = Split(.Cells(col2).Address, "$")(1) lRow1 = .Range(SV1 & .Rows.Count).End(xlUp).Row End If colNameF = .Range("A8:DD8").Find(What:="Teams").Column colNameF1 = .Range("A8:DD8").Find(What:="Items").Column colNameF2 = .Range("A8:DD8").Find(What:="Domain").Column With .Range("$A$8:$DD$9999") .AutoFilterMode = False .AutoFilter Field:=colNameF, Criteria1:="ST Test", Operator:=xlOr, Criteria2:="" .AutoFilter Field:=colNameF1, Criteria1:="Variance", Operator:=xlOr, Criteria2:="" .AutoFilter Field:=colNameF2, Criteria1:="9S", Operator:=xlOr, Criteria2:="" End With .Cells(lRow1 + 1, SV1).NumberFormat = "0" SumV1 = SV1 & "9" SumW1 = SV1 & lRow1 .Cells(lRow1 + 1, SV1).Formula = "=SUBTOTAL(9," & SumV1 & ":" & SumW1 & ")" .Cells(lRow1 + 1, SV1).Select Selection.Copy End With

Let us know if this helps.

Thanks a lot. That was of real help. :) .. But I'm still not able to execute one line in that. I have a line of code: .Cells(lRow1 + 1, SV1).NumberFormat = "0" This line is throwing an 'Application Defined or Object defined error.' Basically, I'm trying to set the format of the cell to Number. lRow1 and SV1 are values stored in variables. aCell1 = Range("A8:DD8").Find(What:="Feb", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False) col2 = aCell1.Column SV1 = Split(Cells(col2).Address, "$")(1) lRow1 = .Range(SV1 & Rows.Count).End(xlUp).Row
Even with the 'Set' word, its not helping. I'm getting the same error. Please see the complete code below posted as answer.

Kazimierz Jawor · Accepted Answer · 2013-12-05 08:07:08Z

You open you workbook in separate instance of Excel while you try to run find method in the instance of excel where is you code. Try this:

colNameF = EXCELApplication.Workbooks(DefPath & strExt & ".xlsb"). _ Sheets("Release Level View"). _ Range("A8:DD8").Find(What:="Teams", LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=False, _ SearchFormat:=False).Column

It would be much better if you set Object Variable to Sheets("Release Level View") and use it in your code further.

Collectives™ on Stack Overflow

Activating a different workbook and performing functionality in that workbook

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related