Solved

Compact Excel file?

Posted on 2011-03-01
8
592 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:Dale Fye (Access MVP)
8 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 35014384
0
 
LVL 10

Expert Comment

by:Makrini
ID: 35014404
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
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35014512
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 35018394
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.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 35316535
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.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 0 total points
ID: 35376163
None of the above solutions resolved the problem, even after removing the named ranges.

I have abandoned this effort because I was able to convince my client to move the entire spreadsheet functionality into the database application; which is a much better long term solution.

It will just take a while to get their users comfortable with working in a database rather than using spreadsheets like a database.

0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 35410260
see final note in thread
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question