+ Reply to Thread
Results 1 to 18 of 18

Process to run multiple processes.

  1. #1
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Process to run multiple processes.

    I have been tasked with creating a single sheet that shows details from multiple (same exact format for each of these separate files) sheets. Its like like a sales managers overview of all of his salesmen.

    I know how to just grab the info from the sheets so thats not the issue. The issue is that when you open each of these files you need to click (run a macro to update them).

    So i don't know how does one open a sheet- run the macro within THAT sheet- save THAT sheet and then grab some data.... then move on to the next one.. and so on.

    I just don't know really where to start here.

    -Ken

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Process to run multiple processes.

    Here's a general VBA skeleton for what you mentioned.

    Requirements are -
    * You must copy and paste the code to a new module in your managers sheet.
    * All the individual source files need to be collected in one folder. You will need to indicate where that is (green part below).
    * You then need some kind of partial name match to identify the files you want to pull the data from (red part).

    Also - I'm just looking at every file ending in .xls, .xlsx, .xlsm -- i.e., any excel file. That is all you have to do if ONLY those sales files with the macro are placed in that folder. By default, my routine is recursive and checks subfolders -keep the subfolders clean as well.

    The orange parts are specific to your project. You can use Application.run to run the macro you need to use to update the file, and then after the save - you need to insert whatever code you are using to transfer the data (you indicated you had that working).


    Basically - this skeleton provides the looping over multiple files to do whatever it is you are trying to do (that's what you need to add in the orange areas).

    Please Login or Register  to view this content.
    Last edited by GeneralDisarray; 10-28-2015 at 09:38 AM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Process to run multiple processes.

    Application.Run wbk.Name & "!" & "GETTGECME"

    This is my first macro on the sheets. GETTGECM is the name of the macro.
    Last edited by sungen99; 10-27-2015 at 03:19 PM.

  4. #4
    Forum Contributor
    Join Date
    09-03-2015
    Location
    IE
    MS-Off Ver
    2003 - 2016
    Posts
    258

    Re: Process to run multiple processes.

    Excel does not have Objects named FOLDER or FILE...

    Please Login or Register  to view this content.
    So change to generic Objects...
    Please Login or Register  to view this content.
    Remainder of code untested - mobile phone.

  5. #5
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Process to run multiple processes.

    This is my code now.

    Please Login or Register  to view this content.
    It does start now.. it DOES open the first excel program in the folder but then fails when it tries to execute the first macro

    Please Login or Register  to view this content.
    with a run time 1004 error.

    ADDED: I dont know if this helps or not but the file name is a multiple words long. Example is "Lot #100 top camp.xlsm"
    Last edited by sungen99; 10-27-2015 at 03:56 PM.

  6. #6
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Process to run multiple processes.

    Quote Originally Posted by grimes0332 View Post
    Excel does not have Objects named FOLDER or FILE...
    As I indicated in my post - you must set a reference to the Microsoft scripting runtime in your project. I admit, I put the comment in the code itself, so you may not have seen it.

    To do that:

    With the project open and active, open the VBA editor (press Alt+F11).
    Go to Tools>References
    Scroll to find "Microsoft Scripting Runtime" in the list of libraries.
    Check the box next to it, press OK.

    NOW excel DOES have knowledge of the objects needed for that code.
    Last edited by GeneralDisarray; 10-28-2015 at 09:39 AM.

  7. #7
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Process to run multiple processes.

    Quote Originally Posted by sungen99 View Post
    I dont know if this helps or not but the file name is a multiple words long. Example is "Lot #100 top camp.xlsm"
    OK - Spaces in the name are a problem. You solve that by wrapping the workbook name in single quotes.

    Try this:
    Please Login or Register  to view this content.
    Last edited by GeneralDisarray; 10-28-2015 at 10:15 AM.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Process to run multiple processes.

    If there are spaces in the filename you need to enclose it in single quotes when using Application.Run.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  9. #9
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Process to run multiple processes.

    This is going great now with one issue.

    The macros work great now but it does not save the updated files in the correct folder but rather in the same folder as the Master sheet which i do not want. Also i assume that this when this is done correctly it will want to overwrite the old file so i anticipate that error.

    Thanks again for all of your help.

    -Ken

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Process to run multiple processes.

    You don't specify where to save the workbook, all you have is this which I suspect will save to the 'active' directory.
    Please Login or Register  to view this content.
    Try using SaveAs and specify the path/folder/filename etc.

    By the way, what do the macros in each of the workbooks do?

    Is it something specific to the workbook they are in?

  11. #11
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Process to run multiple processes.

    I am an idiot so can you actually show me the code that will overwrite the file in the correct folder? the code currently is:

    Please Login or Register  to view this content.
    As far as your question on the macros in each workbook go. Each separate workbook is a stand alone project that needs to have its "Stock Ticker" prices updated in order to show a real time P&L. You need to be able to open each individually as well if needed and update them at a specific moment as a "one off". So i basically have 100 of these SAME workbooks with different stock names and buy history. But in order for me to have a overview at any specific time the MASTER sheet needs to update ALL of the prices for ALL of the workbooks and then the output on the MASTER sheet will be accurate for that moment.

    Question: I have physical LINKS on the master to specific location on each of the workbooks. with the MASTER sheet automatically reflect the changes on all of the sheets or is there something that needs to be done to "Refresh" the links? If so- (HA) what is the code i need to add to make that happen.

    Thank you all again for your help with this. I will clearly look like a rock star if i am able to pull this off and get a real result.

    -Ken

  12. #12
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Process to run multiple processes.

    As to the "Save" issue. Yeah, I missed that. Normally when I'm looping sheets like this, I don't save them.

    I think there is more than one way. But, I would just save the individual sheet with the CLOSE command as follows -NOTE you can just take out the wbk.save line if you save upon closing.

    Please Login or Register  to view this content.
    I noticed you are declaring your variable 'As Object' - I addressed this issue in post #6 > I'm not sure if you can use my little trick of accessing the file's parent folder and name if you just declare that as 'object'.

    The objects declared in the 'Microsoft Scripting Runtime' are quite useful, I'm not aware of any reason to avoid adding a checkmark for that library.


    Links Question:
    I just tested that scenario - and the links DO indeed update for me.

    If you need to, you can also manually update the links by reopening the workbook or going through the data ribbon.
    Last edited by GeneralDisarray; 10-28-2015 at 02:05 PM.

  13. #13
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Process to run multiple processes.

    That line gives me a runtime 1004 error.. ahhhhhh

    General- i DID add the Microsoft Scripting add in like you said. I made sure that it was on on the Master sheet as well as ALL of the data sheets but it still was erroring for some reason. so perhaps there is code to save the file another way?
    Last edited by sungen99; 10-28-2015 at 03:16 PM.

  14. #14
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Process to run multiple processes.

    Please Login or Register  to view this content.
    That line should throw no error.


    Error 1004 is general - What is the description of the problem included with that error?


    I took your code from post#5 and made minimal modifications below - this runs without any problems, so I can't reproduce your issue

    Please Login or Register  to view this content.
    Last edited by GeneralDisarray; 10-29-2015 at 09:28 AM.

  15. #15
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Process to run multiple processes.

    Nope still get that runtime 1004 error on:

    Please Login or Register  to view this content.
    It says that the extension can not be used with the selected file type. Change the file extension in the file name text box or select a different file type by changing the save as type.

    The file is an excel enabled Macro file. its extention should be .xlsm right?

    If it matters there are ALSO spaces in between the name of the files.

    Also- if it matters ONLY the excel xlsm files will be in this specific directory.

  16. #16
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Process to run multiple processes.

    I didn't have that error previously - but when I put copied that code to a workbook, I got the same error. Curious.


    TRY CODE BELOW - Seems like breaking up the operation into a save, then a close works out. Spaces in the name don't matter in that line.


    ALSO! I've attached a workbook to run the loop, and a zipped collection of dummy files to run on - if you unzip it onto your Desktop (and the desktop is sitting at C:\users\yourUserName\Dektop... then you should be able to run it.


    Please Login or Register  to view this content.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Process to run multiple processes.

    It worked!!!!!!!
    [solved]

  18. #18
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Process to run multiple processes.

    Awesome news.




    To mark your thread solved do the following:
    New Method
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    Old Method
    - Go to the first post
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solve
    - Click Save

+ 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. VBA Multiple Process Splits
    By imav in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2011, 02:44 PM
  2. Multiple processes Excel Multiple Books
    By aehartle in forum Excel General
    Replies: 0
    Last Post: 08-24-2010, 03:23 PM
  3. Macro that processes subfolders
    By jimbofoxman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2009, 02:36 PM
  4. Excel timer for multiple processes
    By wineandstein in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2009, 11:27 AM
  5. How many excel processes can you open?
    By Stanley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2006, 07:25 AM
  6. Display System Processes
    By Ashley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2005, 11:30 PM
  7. [SOLVED] How to count process running time ( process not finished)
    By miao jie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2005, 06:06 AM
  8. [SOLVED] How to count process running time ( process not finished)
    By miao jie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-12-2005, 02:06 AM

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