+ Reply to Thread
Results 1 to 8 of 8

Need code to switch between workbooks which unstable name

  1. #1
    Registered User
    Join Date
    06-09-2012
    Location
    Fort Worth, TX
    MS-Off Ver
    Excel 2007
    Posts
    5

    Need code to switch between workbooks which unstable name

    Hi, I am trying to write a code to switch between 2 workbooks. Should be easy with the Workbook.Activate. The problem is: the name of one of the workbook is not always the same (it is data that are imported from another application, and there is a number always added to the name e.g. "Exported Data [1].xls", "Exported Data [3].xls" even though it is the same data that is imported. I had Workbook ("Exported Data [1]"). Activate, but since the number inside [] would change, I get and error when running the code, if the number is not [1]. Any suggestion how to get around?

    Thank you for your help.

  2. #2
    Registered User
    Join Date
    06-09-2012
    Location
    Fort Worth, TX
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need code to switch between workbooks which unstable name

    I wanted to add that I don't want to use other steps such as "save as" to get a fixed name. My code once started, gather the proper data from the proper cells, send it to the main workbook where it is computed, and automatically send the result to Outlook to be emailed. Everything works perfectly when the number [] in the code matches the one on the imported file's name.

  3. #3
    Forum Contributor
    Join Date
    02-12-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    275

    Re: Need code to switch between workbooks which unstable name

    I had a similar problem recently and here is my version of an answer

    Please Login or Register  to view this content.
    In my case I also changed the spreadsheet by searching for the first instance of a specific value within the header column and selecting it. This value is fixed in
    Please Login or Register  to view this content.
    when this code runs the variables I used stands for
    T_WBook = Target (start) workbook
    C_WSheet = Current worksheet in start workbook - I used it to return to the same place once the code is done
    S_File = The full path and file name of the second workbook
    S_WBook = The friendly name of the second workbok
    S_WSheet = The Sheet in the second workbook with the data on


    Hope this helps you as much as it helped me.

  4. #4
    Registered User
    Join Date
    06-09-2012
    Location
    Fort Worth, TX
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need code to switch between workbooks which unstable name

    Evertjvr,
    Thanks for your reply. Unfortunately the code you sent does not address my problem.
    I want to be able to switch between workbooks. Typically, the code below would work:

    Sub Switching()

    Workbook("File Name 1").Activate 'the main file where the code runs
    ....

    Workbook("File Name 2").Activate 'activate the source file where data will be extracted
    ....

    end sub


    The problem is, "File Name 2" is a temporary excel file that is created when exporting data from another application, and the name is like "Export[1]", "Export[2]", "Export[2]", etc, depending on how many time you've been exporting the data during the session.

    I want to write a vb code that would say:

    "Make the other opened excel document active (regardless of the name)" or
    "Make the document which name contains the word "Export" active.

    Any suggestion?

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need code to switch between workbooks which unstable name

    Hi claudyrain,

    Have you tried Alt - Tab?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  6. #6
    Registered User
    Join Date
    06-09-2012
    Location
    Fort Worth, TX
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need code to switch between workbooks which unstable name

    Hi xladept!

    lol...I am trying run a report with just one click of a button. The button is on the main excel document, which contains 5 worksheets.
    Sheet1 contains a Table where records from a temporary file are added.
    My program works pretty well, except for when the temp file's name does not match the one I've put in the vb code.

    Here are the steps to run the report, manually:

    1-run a report from Microsoft CRM, which will collect orders that shipped that day, and those which did not
    2-export from CRM to Excel. A temporary excel file opens with a name, for e.g. "Report[1]". All data is in text format
    3-manually change the format of the data in each column (Date, currency, number, etc)
    4-sort the data by status, to separate orders shipped from orders that did not.
    5-sort the by account type
    6-get the sum and the count for the orders shipped, and those that did not shipped,
    7-put these results data in a table that summarizes it
    8-select and copy that table
    9-open Outlook and paste the table
    10-add the recipients and send the mail.

    As I said, I've got all this done with just ONE click of a button, but get an error if the number in [] of temporary excel file's name is different than what's in my code.
    Because the code from one workbook to another, back and forth, to compute data.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need code to switch between workbooks which unstable name

    Hi claudyrain,

    Maybe you could use Workbooks(Workbooks.count) if your exported data is the last book created.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need code to switch between workbooks which unstable name

    Hi Claudyrain,

    Here's what I mean. When you create your ExportFile - you can get its name this way:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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