+ Reply to Thread
Results 1 to 6 of 6

supplement CSV files opening too slowly

  1. #1
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    167

    supplement CSV files opening too slowly

    hi

    hoping if anyone can help me efficient-up or simplify but not omit the task my following code intends to accomplish, ie just to open 3 supplement files if any 1 is not already open, & if any is opened just skip that and jump to check for next supplement file. The current code is opening the files taking around 3-4 minutes.


    Please Login or Register  to view this content.

    cheers
    Stewart

  2. #2
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    401

    Re: supplement CSV files opening too slowly

    Hi Stewart

    Opening a .csv file always triggers a recalculation in your Excel file, regardless of your calculation setting.
    So even if you set calc mode to manual, this will be disregarded when opening any csv file.

    However, such a recalculation is not done if you open that csv file as a .xls file. So the trick is to use code something like
    Name "C:\User\SM\outdoors.csv" As "C:\User\SM\outdoors.xls"
    Workbooks.Open "C:\User\SM\outdoors.xls", Format:=2

    ..you can always do the reverse to rename the file back to .csv for example
    Name "C:\User\SM\outdoors.xls" As "C:\User\SM\outdoors.csv"

    I'm sure you could adjust your code and test this out

    zeddy

  3. #3
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    167

    Re: supplement CSV files opening too slowly

    hi Zeddy

    well, I’ll be .. i faintly suspected but never knew for sure the CSVs force calculates..
    so according to your suggestion then i could just discard all my CSVs after having them to be saved as XLS then i wouldn’t face the long drag anymore right ? in future just use XLSX or did you mean they have to be old XLS, or would XLSB be even “lighter” thus faster ..? In my understanding XLS means 97-2003 Excel format?

    Stewart

  4. #4
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    401

    Re: supplement CSV files opening too slowly

    Hi Stewart

    Well I did some testing again today and it seems that the simple trick of renaming of csv files no longer works, probably due to some recent system updates.

    Opening a csv file will still trigger a recalculation even if you have calc mode set to Manual, and regardless of the 'file extension'.

    But there is another way!

    Each worksheet in your file has it's own EnableCalculation property.

    You can turn calcs OFF for any particular worksheet using that particular worksheet's EnableCalculation property!
    If you set a worksheet's EnableCalculation property to False, then opening a csv file does NOT cause a recalculation on that particular sheet.

    It is easy to set/reset the EnableCalculation property for worksheets.

    You could use VBA to do this e.g
    Worksheets("this one").EnableCalculation = False

    ..and to turn calcs back ON for that particular sheet:
    Worksheets("this one").EnableCalculation = True

    Obviously, you could have a loop to do this for ALL worksheets, or, say, all worksheets that have a particular Tab color, or all worksheets with names Like "Month *" etc etc etc

    You can view a worksheets Properties in the VBA>View>Properties window
    (Press [Alt][F11] and then [F4] to see the Properties Window; select your required worksheet in the Project Explorer window etc etc etc)

    So, if you have a calc-intensive worksheet(s), this might help you.

    zeddy
    Excel Tactical Beverages Unit
    Last edited by zzzeddy; 11-15-2020 at 12:36 PM.

  5. #5
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    167

    Re: supplement CSV files opening too slowly

    hi Zeddy

    did you mean to set the calculation mode for all the CSV files from the master XLSM workbook that opens them? my CSV files only have 1 sheet, just lots of rows and columns.

    i’ll try out your code from the master workbook and see if that works

    Stewart

  6. #6
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    401

    Re: supplement CSV files opening too slowly

    Hi Stewart

    As you know, CSV files are just data files, and the time taken to open them certainly depends on their file-size, but more importantly the time taken also depends on the complexity of your xlsm file and how long it takes your xlsm file to recalculate itself when changes are made within your xlsm file.

    Suppose you have a complex xlsm workbook that has many worksheets, each with lots of calculated cells.
    When you make changes within such a workbook, it can sometimes take along time for the calculations to finish.
    For example, it might take say, 3 minutes to update all the calculations in the workbook, each time a change is made.

    In such circumstances, it is often a good plan to temporarily turn calcs to Manual, and then make all the cell changes you need, before turning the calcs back ON again for updated results.

    If your workbook-application involves opening several CSV files, for example to 'fetch' data from such files, then Excel will do the 3-minute (or whatever) calc for each and every CSV file you open, even with the calc mode set to Manual.
    So for 4 CSV files, that would add at least 4 x 3-minutes = 12 minutes of processing time.

    If your xlsm file is not a complex-file, i.e. recalculates very quickly, e.g. in seconds (or even less), then the time taken to open CSV files is more dependant on the actual CSV file sizes, and possibly where they are being loaded from. That could be a network-bandwidth issue, available memory-size on the actual computer you are using etc etc etc.

    So my suggestion of setting the EnableCalculation property of worksheets to False only really applies if you have lots of complex time-consuming calculations on those particular worksheets. If this is so, it is possible to explore what is causing those calculation bottlenecks, and see if there are more efficient ways of dealing with such issues.

    zeddy
    Excel Tactical Beverages Unit

+ 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. Hyperlinks are opening slowly
    By damian119 in forum Excel General
    Replies: 0
    Last Post: 04-26-2017, 02:07 PM
  2. Excel files open very slowly
    By Andrew87.. in forum Excel General
    Replies: 10
    Last Post: 08-02-2015, 12:03 PM
  3. Excel file with links opening extremely slowly
    By blakrapter in forum Excel General
    Replies: 0
    Last Post: 09-09-2013, 06:28 PM
  4. [SOLVED] Excel Files Opening Slowly
    By grafx77 in forum Excel General
    Replies: 3
    Last Post: 05-07-2013, 08:10 PM
  5. slowly opening workbook
    By mak_pj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2013, 03:47 PM
  6. Repeated Opening with VBA calls causing Excel files to open slowly
    By al0501 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2009, 02:06 AM
  7. [SOLVED] Files Opening Very Slowly
    By Gilley in forum Excel General
    Replies: 3
    Last Post: 01-16-2006, 04:25 PM

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