test if object var is set to nothing - also looping through excel sheets (1 Viewer)

[SamDeMan]

hi

i have two questions. first is regarding my errors. i am trying to close the
excel spreadsheet if sub errs. I am not sure how to test the variable
MyWorkbook or MyExcelSpreadsheet (something like MyWorkbook = null, or IsNull(MyWorkbook)).

Exit_GenerateInvoice:
    Exit Sub

Err_GenerateInvoice:
    MsgBox Err.Description
    If (here is my question) MyWorkbook Then
        MyWorkbook.Close SaveChanges:=False
        MyExcelInstance.Quit
    End If
    Resume Exit_GenerateInvoice
End Sub

second question, (unrelated, this is more of an excel question) how do i
loop through sheets of a spreadsheet. this is my script, but it gives me
errors.

    For Each sSheet In MyWorkbook
        Debug.Print sSheet.Name
    Next sSheet

 

[Brianwarnock]

The answer to the second question is replace myworkbook by Worksheets
I think

Brian

 

[SamDeMan]

thanks brian, i just tried adding MyWorkbook.worksheets and it worked!

sam

 

[Brianwarnock]

I guess you are not working in the Workbook myworkbook.

I think you may need to use Workbooks("Myworkbook").close, you need to point at the "collection" or something technical like that Its a case of the one eyed man leading the blind here as I've never done any of this.

Brian

 

[SamDeMan]

the problem is that Myworkbook = nothing, which means what?? i don't know. what is the value of variable when it is pointing to nothing. if i write "if myworkbook = nothing then etc" i get an error, basically saying "nothing" can only be used in the context of "set". so i am trying to figure out how to state "nothing" without using the keyword "nothing".

thanks for your help,

Sam

 

[Brianwarnock]

You have to use Workbooks eg If Workbooks("myworkbook") =

I took a peep at Excell help look up Workbooks in there it shows how to close and open workbooks

Brian