Compact Excel file?
I've got an Excel file which contains a couple of macros (several hundred lines of code) which is 6Meg in size.
This is a template that I use to export data from a production database into on a monthly basis, but there are currently about 10 worksheets, most of which have less than 20 cells with data or formatting. The two main worksheets will contain about 300 rows by 30 columns of data each month, but they are currently empty.
I cannot figure out why this spreadsheet is so large, or how to shrink (compact) it down to a reasonable size.
This is a template that I use to export data from a production database into on a monthly basis, but there are currently about 10 worksheets, most of which have less than 20 cells with data or formatting. The two main worksheets will contain about 300 rows by 30 columns of data each month, but they are currently empty.
I cannot figure out why this spreadsheet is so large, or how to shrink (compact) it down to a reasonable size.
Are you using querytables? It's possible that you have inadvertantly added a lot of pivot tables on top of each other.
Also I find that overuse of named ranges (generally linked to other documents) makes the sizes far far larger. We really need to see the workbook to tell
Also I find that overuse of named ranges (generally linked to other documents) makes the sizes far far larger. We really need to see the workbook to tell
ASKER
Makrini,
No query tables, I'm using Access to open Excel and dump the data into the workbook. However, many of the extra worksheets mentioned above are to create named ranges which are used for data validation in about 15 of the columns in the main two worksheets. I deleted all of the named ranges but that did not help much.
I'll take a look at some of the other options presented above. I've not tried copying the worksheets and code into a new workbook, but will give that a try tonight.
No query tables, I'm using Access to open Excel and dump the data into the workbook. However, many of the extra worksheets mentioned above are to create named ranges which are used for data validation in about 15 of the columns in the main two worksheets. I deleted all of the named ranges but that did not help much.
I'll take a look at some of the other options presented above. I've not tried copying the worksheets and code into a new workbook, but will give that a try tonight.
ASKER
I apologize for abandoning this, should have closed it out, but got sidelined onto another project (or aspect of this project).
I tried the "solutions" recommended by Michael74 and JimyX, and nothing has worked. I believe that part of the problem may be all the named ranges on the spreadsheet, so I need to modify some of the code examples referenced above after deleting the named ranges.
Please leave this open and I'll try to get to it this week.
I tried the "solutions" recommended by Michael74 and JimyX, and nothing has worked. I believe that part of the problem may be all the named ranges on the spreadsheet, so I need to modify some of the code examples referenced above after deleting the named ranges.
Please leave this open and I'll try to get to it this week.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
see final note in thread
https://www.experts-exchange.com/questions/23802929/Reduce-file-size.html
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2415-Beginner%27s-Guide-to-Improving-the-Speed-and-Size-of-Excel-files.html?sfQueryTermInfo=1+10+30+excel+file+reduc+size
Michael