+ Reply to Thread
Results 1 to 11 of 11

Adding hundreds of sheets - calculation time problem

  1. #1
    Registered User
    Join Date
    11-11-2019
    Location
    Warsaw, Poland
    MS-Off Ver
    2016, 32bit
    Posts
    5

    Adding hundreds of sheets - calculation time problem

    Hi,
    I have the code which opens hundreds of csv files and move the sheets to the main file (noticed it was the most efficient way unless I am wrong), then convert text to column and move to the next csv file. The problem is that excel calculates each newly opened csv file, despite I turned calculation off in VBA code. I think a "turn off" works only to currently opened files. I noticed when I just click with my mouse in any cell why the code is working - it gets 10x speed of doing the work. I guess it is because when you do the same when normally excel cacluates a big file then the calculation is cancelled, however during macro it cancels all following calculations. If the work would be purely shifting the sheets there would be no problem, however later on I want to do other calculations.
    Do you have any guess how to run the code so I wouldn't need to make such manual thing and vba would simply open the file fastest possible without calculation?


    Please Login or Register  to view this content.
    Last edited by Mariusz(); 11-11-2019 at 03:28 AM.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Adding hundreds of sheets - calculation time problem

    You still have/t read the rules and used code tags, and your first question is in this one too
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    11-11-2019
    Location
    Warsaw, Poland
    MS-Off Ver
    2016, 32bit
    Posts
    5

    Re: Adding hundreds of sheets - calculation time problem

    thanks for a note!

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Adding hundreds of sheets - calculation time problem

    Great, and about why it may take longer at different moments, I don't know.
    Are the sheets you want to delete all empty or do they contain the same amount of data at all times? (this is about your first question (other post) but the same macro as far as I can see

  5. #5
    Registered User
    Join Date
    11-11-2019
    Location
    Warsaw, Poland
    MS-Off Ver
    2016, 32bit
    Posts
    5

    Re: Adding hundreds of sheets - calculation time problem

    Well I did 2 posts as they are separate matters. Deleting sheets it takes 2 time ranges - sometimes it is around 3 sec, sometimes around 50 sec and nothing between.
    It has almost the same size of information each time - around 5 columns x 6000 rows with data. This post relates to opening the files and shifting to the main file and I concluded the case is with calculation of newly opened files. With "delete" VBA code posted in another topic - by clicking in the cell while the code runs it doesn't make it faster, in fact excel can get "not responded" for a while and at the end takes more time to finalize. These seems not related problems.

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Adding hundreds of sheets - calculation time problem

    Can this site help one way or another. There is also some code about merging on the other pages of the site

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Adding hundreds of sheets - calculation time problem

    Hi Mariusz, Excel not responing is a matter that started with Excel 2007, when large amounts of data is being processed (loops, deleting, etc) the process is carried out in the background and the Exel application waits, and since the process does not return anything it says 'Not Responing' but it really is working.
    You could add a DoEvents after the worksheets delete command, this (in some of my routines) did speedup things.

  8. #8
    Registered User
    Join Date
    11-11-2019
    Location
    Warsaw, Poland
    MS-Off Ver
    2016, 32bit
    Posts
    5

    Re: Adding hundreds of sheets - calculation time problem

    Yes - I know that. I was just pointing that while I click to VBA for opening files - it helps get much faster, but in deleting VBA code it only gets a bit slower.

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Adding hundreds of sheets - calculation time problem

    When deleting , I'm just guessing, you may have links to other sheets? Graphs and pivots tables can cause this, but this is aall just guessing.
    Happy coding and hope you find the solution.
    The VBA code, for as far as I can see looks good.

  10. #10
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Adding hundreds of sheets - calculation time problem

    I guess my post went invisible. This forum does funny things

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Hi !

    Quote Originally Posted by Mariusz() View Post
    I have the code which opens hundreds of csv files and move the sheets to the main file (noticed it was the most efficient way unless I am wrong), then convert text to column
    Usually that no needs to open a csv file, to move the sheet in the main file and to convert text to columns
    as all this stuff can be achieve at once via a QueryTable ! …


    Quote Originally Posted by Mariusz() View Post
    The problem is that excel calculates each newly opened csv file
    It can't calculate anything as a csv does not contain any formula but data only ! …


    Just use the ISREF worksheet function for example to check if a sheet already exists,
    if yes just clear data if necessary and if not create it …

+ 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. [SOLVED] Time difference calculation adding 12 hours?
    By gaudiumpenrith in forum Excel General
    Replies: 6
    Last Post: 08-01-2017, 05:33 AM
  2. Need help adding 00 and inserting it in hundreds of rows
    By excelstone in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-24-2016, 06:20 PM
  3. Need help adding 00 and inserting it in hundreds of rows
    By excelstone in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-24-2016, 05:36 PM
  4. [SOLVED] Excel calculation for adding time and calculating remaining time
    By mark888 in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 01-12-2015, 12:55 PM
  5. [SOLVED] Excel calculation for adding time and calculating remaining time
    By mark888 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-12-2015, 06:38 AM
  6. Replies: 2
    Last Post: 08-12-2014, 12:23 PM
  7. [SOLVED] Time calculation by adding minutes
    By lavan_joy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-12-2013, 08:27 AM

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