+ Reply to Thread
Results 1 to 4 of 4

Can't seem to SET a workbook to a Variable name

  1. #1
    Registered User
    Join Date
    08-17-2012
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    35

    Unhappy Can't seem to SET a workbook to a Variable name

    Hello,

    I need help. I'm trying to set 3 different files as "workbook objects" and switch between the 3 in the code. A few times it worked (can't remember what I did) and more times than not it's doing weird things. My working with workbook objects has been very limited.

    I think my initial problem is where I'm prompting the user to open a file and it never gets "set" to the workbook object name. Maybe if that would work, the rest of the code would? (Note: I'm only working with 2 workbooks here, I have 3 more to add later in the code so I need to get this right in my head now.)

    In addition, I am including code below that works up until a point (only in Excel 2010, not 365) when I want to close the one workbook I'm done with. When I run those two lines of code to .activate and then .close, it closes my Add-in! (named Piledriver.xlsm which is never referenced in the code) Why??

    And can you tell me when you would use "Close workbookname" rather than "workbook.close" ?

    Thank you so much for your help!
    PatB

    Please Login or Register  to view this content.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Can't seem to SET a workbook to a Variable name

    ThisWorkbook refers to the workbook with the code in it. If you are trying to assign the workbook you open to a variable, replace this:

    Please Login or Register  to view this content.
    with this:

    Please Login or Register  to view this content.
    Rory

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Can't seem to SET a workbook to a Variable name

    Quote Originally Posted by patb View Post
    And can you tell me when you would use "Close workbookname" rather than "workbook.close" ?
    Never, since that's not valid code.

  4. #4
    Registered User
    Join Date
    08-17-2012
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    35

    Re: Can't seem to SET a workbook to a Variable name

    Thanks!!!

    You have no idea how many different versions I tried to get that "Set " statement in the same line of code as opening the file! And duh, I knew it wasn't valid code or else I wouldn't have asked

    Now that it's working I thought I should mention to anyone who may later reading this post for help that I was still getting an error when trying to copy the two columns to the other workbook. The macro recorder recorded Paste to COL A:A, the correct syntax should be COL A:B since two columns were being pasted.

    And your dog looks a lot like mine!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 09-24-2019, 10:00 PM
  2. Replies: 4
    Last Post: 03-06-2014, 07:49 PM
  3. using a variable workbook's name to define another variable
    By lordterrin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-30-2013, 05:57 PM
  4. [SOLVED] Assign entire row to variable, how do you paste variable values in a new workbook?
    By tv69 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2013, 08:01 PM
  5. Copy variable range from variable workbook to variable range on current workbook
    By Locster79 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2013, 10:37 AM
  6. [SOLVED] TypeName limitation: How to distinguish Worksheet Variable from Workbook variable
    By bagullo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-02-2013, 07:35 AM
  7. [SOLVED] Copy/paste data from one workbook to another but workbook name variable
    By Jiptastic in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-11-2013, 12:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1