+ Reply to Thread
Results 1 to 8 of 8

macro to open and close Excel workbooks

  1. #1
    Registered User
    Join Date
    12-09-2010
    Location
    Grand Cayman
    MS-Off Ver
    Excel 2007
    Posts
    3

    macro to open and close Excel workbooks

    I have created a system to manage mortgage accounting. Each mortgage has it's own spreadsheet. File names: mortgage.xlsx, mortgage1.xlsx, mortgage2.xlsx up to mortgage49.xlsx.

    I have created a master control spreadsheet that pulls information from all the accounts (spreadsheets).
    Some of the spreadsheets may not exist, so to prevent a Macro error the file is only opened if it exists. Thus:

    Please Login or Register  to view this content.
    This part works fine. Having opened them I then need to close them in the same Macro.

    However the following does not work and results in a Runtime error 9. Subscript out of range.
    This trips up at the first statement below, which IS an existing file:

    Please Login or Register  to view this content.
    Last edited by Paul; 12-09-2010 at 06:44 PM. Reason: Added code tags for new user. Please do so yourself in the future.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to open and close Excel workbooks

    We could parse the 1-49, too with another loop, but just in case you want to use any random text strings for your workbook names, you can put them all into an array of string values like so, then just run them all:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-09-2010
    Location
    Grand Cayman
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Re: macro to open and close Excel workbooks

    Thank you. I really appreciate you taking the time to help me.

    Unfortunately your revised version fell apart at the same point as my original one.

    Error 9.
    Please Login or Register  to view this content.
    I added
    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    at the beginning and end of the code. Here is the final version:

    Please Login or Register  to view this content.
    I just don't understand it as both your coding and mine looks like it should do the job.
    Last edited by norman0000; 12-10-2010 at 03:18 PM. Reason: added [code]

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to open and close Excel workbooks

    Please revise your post to add code tags around the code, as shown in my posts and demonstrated in my signature below.

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to open and close Excel workbooks

    I'm thinking it may be as simple as blowing through those errors. Add this near the top with all the other App settings:
    Please Login or Register  to view this content.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: macro to open and close Excel workbooks

    You don't want the path:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: macro to open and close Excel workbooks

    When you close a workbook, you're referring to a workbook in memory, not the workbook on disk, so you don't include the path.

    I can't imagine why you would want to open all the workbooks at once, rather than process them sequentially:
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    12-09-2010
    Location
    Grand Cayman
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: macro to open and close Excel workbooks

    Many thanks for all your help. Removing the path did the trick and it now works perfectly.

    To answer the question about "why open all the workbooks at once".

    This control workbook extracts values from the various individual mortgage accounts. I originally tried to open and close them sequentially.
    The result however was that the value from the first workbook flashed on the screen for a second, but disappeared when the second value appeared.

    Thanks to everyone who helped me.

    Norman

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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