View original (French)

Large Excel file

Solved
cedricdu95 Posted messages 221 Status Member -  
cedricdu95 Posted messages 221 Status Member -
Hello everyone,

I need your help.
I have an Excel file that takes a lot of time to load, and I think it's related to its size and the fact that it's on a NAS that is very slow.

Anyway, we will only talk about the Excel file, which is 43MB, so I think that's pretty large.

The file consists of several sheets (about 20), and it's an Excel file gathering several disputes with my supplier.

I copy a sheet called "base dispute" that I sort and group in this Excel file. The base dispute sheet consists of several rows and columns and also has two buttons (created blocks) that allow, when clicked, to directly send the dispute request by email.
I think that what takes up the most space is this "code" sending system; however, I can't really find where and how to delete it. I've deleted the blocks, but it doesn't change anything.

If you have any ideas... I understand it's easier for you if you have the file, but that's complicated since it contains professional information.

Thank you in advance.

1 answer

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    Already did Ctrl+End on each sheet and delete the unused rows and columns, it unnecessarily bloats your file. But 45 MB doesn't seem exaggerated.
    The code itself shouldn't take up much space.
    And if your NAS is slow, work with a local copy that you back up regularly (with SyncBack for example if you’re worried about forgetting). It's probably the main cause, especially if you're on Wi-Fi...
    Eric

    By continually trying, we eventually succeed.
    So the more it fails, the more chances we have that it works. (the Shadoks)
    In addition to thank you (yes, it can be done!!!), remember to mark it as resolved. Thank you
    0
    1. cedricdu95 Posted messages 221 Status Member 1
       
      Hello, first of all, thank you for your reply.
      I started deleting rows and columns even if they keep reappearing.
      After a few sheets, the file started crashing.
      So I thought I would copy it directly to my PC to speed things up, but the file won't open, or rather, it's been stuck at 4% for 5 minutes while opening.

      If I manage to open it, I will copy the information into a new Excel file and see if that works better...

      So the NAS is slow, very slow, but the problem doesn’t seem to come solely from it, apparently, for the Excel file.

      I am using an Ethernet connection through a switch to provide access to 3 PCs. I opened another topic for my speed issues, but apparently, not many people have solutions since there are 0 replies. I had done tests with iPerf, and everything seemed fine, on the admin page everything is green and seems to be working, but I have no idea how to solve this...
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello,

      You can open it by disabling macros just in case, or even in protected mode (open from Excel and use the dropdown list of the open button) in case it opens better.
      It might have gotten infected, recreating it will do it some good.
      Since we're doing it again, is putting everything in the same file really the best solution?
      You could create a master workbook with the minimum information (date, closed, ...), and links to the others. It will be more responsive since you'll only load what you need, and it will limit the damage in case of a new issue.
      Eric
      0
    3. cedricdu95 Posted messages 221 Status Member 1
       
      Hello,

      By copying and pasting all my sheets into a new file, I went from 43MB to 513KB, so it did it some good.

      However, the NAS access is driving me "crazy," but I think I can mark it as resolved.

      What do you mean by master workbook? How to create it?

      Thanks for your help.
      0
    4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Instead of making a sheet for each case, you make a workbook (a simple drag-and-drop of the sheet into the Excel workspace will create it)
      And you create another one (master workbook) with a link to Sheet1!A1 of each other workbook. A click on this link will open the one you’re interested in.
      Create a dedicated folder to gather them all in the same place.
      eric
      0
    5. cedricdu95 Posted messages 221 Status Member 1
       
      Alright, thank you, I'll look into doing that option!

      I appreciate your help, I'm marking the topic as resolved.
      0