+ Reply to Thread
Results 1 to 2 of 2

[SOLVED] Close file and run macro from newly opened file

  1. #1
    Pradip Jain
    Guest

    [SOLVED] Close file and run macro from newly opened file

    i have an excelfile of 20 MB (due to lot of data which cannot be deleted). It
    has a macro in which simulation is run. it involves generation of random
    numbers and i need huge number of trials to get meaningful results. problem
    is when i try to run simulation when this 20 MB file is open it takes very
    high time, about 20 seconds for one simulation. however if i copy the sheet
    containing simulation code and save that in separate file, close the original
    20 MB file and now run the macro, the simulation time is very fast. less than
    1 second for 1 simulation.i can't do manual copy of the sheet and closing the
    original file for every time i run simulation (the 20 MB file has various
    sheets and various parameters can be changed and simulation done for that
    scenario).

    the simulation code is there in the original 20 mb file (let's call it
    original20.xls). there is a button which is linked to this macro. when i
    click on this button simulation starts. what i want to do is as that when i
    click on simulate button on the original20.xls file the following happens:

    the sheet named simulate is copied.
    the sheet is pasted in a new xls file.
    original20.xls is closed.
    the new xls file genrated is named as simulresults.xls
    there will be same button in the new file. but the macro associated with
    this button will refer to the original20.xls. macro association should be
    changed to macro contained in simulresults.xls
    now when i click simulate button, actual simulation process starts.

    Can anybody please help.

    thanks

    pradip





  2. #2
    Tom Ogilvy
    Guest

    Re: Close file and run macro from newly opened file

    You can't run a macro from a closed workbook, so you would need both
    workbooks open

    worksheets("Simulate").copy

    will put simulate and any control button in a new workbook. Any sheet level
    code will be copied with the sheet.

    You just need to write the click event code of the button to call a routine
    in the original workbook.

    Application.Run "Original20.xls!SimMacro"

    --
    Regards,
    Tom Ogilvy




    "Pradip Jain" <PradipJain@discussions.microsoft.com> wrote in message
    news:358B3938-6BA2-4CF8-A676-456C3381D6B2@microsoft.com...
    > i have an excelfile of 20 MB (due to lot of data which cannot be deleted).

    It
    > has a macro in which simulation is run. it involves generation of random
    > numbers and i need huge number of trials to get meaningful results.

    problem
    > is when i try to run simulation when this 20 MB file is open it takes very
    > high time, about 20 seconds for one simulation. however if i copy the

    sheet
    > containing simulation code and save that in separate file, close the

    original
    > 20 MB file and now run the macro, the simulation time is very fast. less

    than
    > 1 second for 1 simulation.i can't do manual copy of the sheet and closing

    the
    > original file for every time i run simulation (the 20 MB file has various
    > sheets and various parameters can be changed and simulation done for that
    > scenario).
    >
    > the simulation code is there in the original 20 mb file (let's call it
    > original20.xls). there is a button which is linked to this macro. when i
    > click on this button simulation starts. what i want to do is as that when

    i
    > click on simulate button on the original20.xls file the following happens:
    >
    > the sheet named simulate is copied.
    > the sheet is pasted in a new xls file.
    > original20.xls is closed.
    > the new xls file genrated is named as simulresults.xls
    > there will be same button in the new file. but the macro associated with
    > this button will refer to the original20.xls. macro association should be
    > changed to macro contained in simulresults.xls
    > now when i click simulate button, actual simulation process starts.
    >
    > Can anybody please help.
    >
    > thanks
    >
    > pradip
    >
    >
    >
    >




+ 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