Thread: Object variable or with block variable not set

Hello, i made a code to read data from specific sheets of multiple workbooks. The error i get is the "object variable or with block variable not set". I made a some changes in the code but it did not worked. So here is the code:

Option Explicit

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Dim wb4 As Workbook

Sub ActivateAustria()
    Workbooks.Open Filename:="C:\Users\red\Desktop\1st\excel1.xls"
    Windows("excel1.xls").Activate
    wb1 = "excel1.xls"
    Workbooks.Open Filename:="C:\Users\ref\Desktop\1st\excel2.xls"
    Windows("excel2.xls").Activate
    wb2 = "excel2.xls"
    Workbooks.Open Filename:="C:\Users\ref\Desktop\1st\excel3.xls"
    Windows("excel3.xls").Activate
    wb3 = "excel3.xls"
    Workbooks.Open Filename:="C:\Users\ref\Desktop\1st\excel4.xls"
    Windows("excel4.xls").Activate
    wb4 = "excel4.xls"
    ActiveWindow.Close
    ActiveWindow.Close
    ActiveWindow.Close
    ActiveWindow.Close
    MsgBox ("ok")
End Sub

Sub ActivateHDB()

    Workbooks(w1).Worksheets("En_mob").Range("G:G").Activate

End Sub

I get the error in the 9th line where i state "wb1 =". I changed the variables into public instead of dim and nothing happened. I also changed the subs and made them public subs, nothing happened. I also stated the workbook parameters as <Set wb1 = New Workbook> also nothing happened :|
Any suggestions?

You will need to set the values of the workbook e.g.

Set wb1 = ActiveWorkbook
Set wb2 = ActiveWorkbook
'etc

Because you have activated the workbooks all you need to do is use the SET command and they will all be = ActiveWorkbook
You do not need to Activate the workbook as they will be the active workbook so you can remove all the Windows("excelxx.xx").activate from your code

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

Or you could replace:

    Workbooks.Open Filename:="C:\Users\red\Desktop\1st\excel1.xls"
    Windows("excel1.xls").Activate
    wb1 = "excel1.xls"

with:

Set wb1 = Workbooks.Open(Filename:="C:\Users\red\Desktop\1st\excel1.xls")

etc ...


The problem is that you have defined wb1 as a range and "excel1.xls" is a text string.



Regards, TMS