• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

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

0
Murray Brown
Asked:
Murray Brown
  • 4
  • 2
1 Solution
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Try:

Set oWb = Application.Workbooks.Open(MYDOC_DIR & "\" & "FILE FROM ITS.xlsm") '**
instead of
oWb = Application.Workbooks.Open(MYDOC_DIR & "\" & "FILE FROM ITS.xlsm") '**

Chris
0
 
sjklein42Commented:
Do you need to declare MYDOC_DIR as a local variable?

Dim MYDOC_DIR As String

Open in new window

0
 
sjklein42Commented:

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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. The "Set" worked - the declaration of the variable made no difference.
Thanks
0
 
sjklein42Commented:
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.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
 
sjklein42Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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