asked on

Excel VBA error opening workbook

Hi. I am getting the following error on the line marked with ** in the code below.

"excel vba object variable or with block variable not set"

The strange thing is that it opens the workbook

Sub Main()

    On Error GoTo EH

    MYDOC_DIR = Environ("userprofile") & "\Desktop"
    Dim oWb As Workbook
    oWb = Application.Workbooks.Open(MYDOC_DIR & "\" & "FILE FROM ITS.xlsm") '**
    Windows("FILE FROM ITS.xlsm").Activate
    oWb.Sheets(1).Cells.Copy
    ThisWorkbook.Sheets(1).Cells.Paste
    
    Exit Sub
EH:
    MsgBox Err.Description

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sjklein42

Do you need to declare MYDOC_DIR as a local variable?

Dim MYDOC_DIR As String

Open in new window

sjklein42

chris_bottomley: Good thinking, but "set" is not needed in VB assignment statements.

http://msdn.microsoft.com/en-us/library/z2wkh0tk(v=vs.80).aspx

Quite a mystery.

Murray Brown

ASKER

Hi. The "Set" worked - the declaration of the variable made no difference.
Thanks

sjklein42

chris_bottomley, any idea why the Set is needed on that line but not on the previous line that sets the value of MYDOC_DIR? Or why it is needed at all?

Great you solved the problem but it is still a question why it worked.

Chris Bottomley

Simply put objects need to be set, variables do not ... therefore a reference to an application, workbook or worksheet are objects and therefore are always set.

Chris

sjklein42

Thanks!