Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting sheets with multiple links

    I have a master standard monthly accounts workbook that holds project details and summaries for 100 projects. Each month I run a macro that: 1 Extracts detailed project data from Access for a selected project, and copies this data into my standard spreadsheet. 2 Updates an associated linked forecasting sheet. 3 Copies the project data sheets and the forecasting sheet into a new workbook. 4 Breaks all the links from the new project workbook to the master workbook. 5 Saves the new project workbook as the project name. 6 Closes the new project workbook. I can run the above macro for a single project or a selection of projects as I require. Project reporting covers costs for multiple years and multiple currencies with a considerable number of linked cells within the master workbook. The above macro has run successfully since 2009. However since we moved to excel 2007, steps 3, 4 and 5 take considerably longer than they did under excel 2003 (5 minutes to run the process for a single project rather than 1 minute). Also when I re-enact steps 3 to 5 manually they do not take as long as when I enact them through the macro. Can anybody advise on why this is taking so much longer and how to speed up steps 3 to 5. The following points are also relevant: 1 I have already turned of auto caculate. 2 I cannot copy and past values from the master workbook, as the links that exist in the master need to be maintained in the new project workbook. Thanks for any help that may be forthcoming.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Has the location of the files changed e.g. are you now on a network, or is everything saved/linked on a local drive??

    zeddy

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 329 Times in 322 Posts
    Have you converted the files and maintain them as XL2007 type or are you still translating them each time from XL2003 to XL2007? It may be the translation and the recreation of formulas and calculation tree...

    Steve

  5. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Zeddy,The problem persists on both my local and the network drives, however it is more pronounced on the network drive. Therefore I tend to copy the files to my local drive, run the macro and then copy the master files and associated individual project files created back.SteveAll the files and associated macros have been re-written to update the programs to .xlsmRegardsAlex

  6. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    226
    Thanks
    0
    Thanked 11 Times in 11 Posts
    Not a full answer but the below comment could be of some assistance. Another user on a different site mentioned that the size of the file did cause VBA to slow down.

    Also, there was a comment that any hide or unhide of columns or rows will greatly slow a macro in 2007. Also any VBA for shapes can also cause a slowdown in 2007.

    I hope this will help.


    Hi, nice effort with regards to speeding up macros in Office 2007/2010. We just upgraded from 2003 to 2010 and not all my macros are running real slow. I did some tests and my results may shed some light on the problem. I made a real simple macro in excel which just set the background colour in 3 separate cells. The macro also recorded how time consuming this operation was. In the beginning I only had one sheet in my excel workbook and the macro was real quick (0,01sec). The macro was only running in sheet 1. When I added more sheets (with some text and pictures) the macro slowed down. When I had added 15 sheets and the file size was about 1,3Mb the same macro now used approximately 3 seconds to colour the 3 cells. I slowly removed one by one sheets and the macro picked up speed. Conlusion: The macro speed is affected by the files size. Reason unknown! PS: macro security set to low and sheet calculation set to manual, in accordance with other tips out there. Regards BAJohansen

  7. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Have you tried using .xlsb instead of .xlsm as the file type?
    I find that binary file format gives smallest file size, so when working on networks you get a speed advantage ( since bigger files take longer to load etc). You can still have macros in binary files, so don't worry about that.

    Also, since the major difference between XL2003 and XL2007 is the massive increase in worksheet size (from 65536 to over 1 million rows, and massive increase in columns available) then maybe you could speed things up by copying the used ranges in the relevant sheets to paste-all in a destination new workbook sheet rather than copying whole sheets.

    Finally, one tip for financial spreadsheets I found useful when going from XL2003 to XL2007 (or later) was to remember that with the new column range extending from IV to XFD, you should be careful with any range names like TAX2011 and VAT20 etc as these are now cell addresses in the new larger spreadsheets.

    zeddy

Posting Permissions

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