PDA

View Full Version : Solved: Large Excel File



GaryB
10-23-2006, 03:57 PM
Hi All,

I have an excel file that is a schedule board for the printing company I work for. It contains several worksheets, macros and coding and currrently it is at 39MB. It seems like it takes forever to save and I was wondering if there was anything I could do to speed the process up? Can an excel file be compacted? Any help is greatly appreciated.

Thanks,

GaryB

Jacob Hilderbrand
10-23-2006, 07:58 PM
Depending on what you are doing, you can likely use Access to help. What I normally do in these situations is store all the data in Access, then pull out the data that I need to do something with as needed.

But it depends on how the data is broken down. If it is a schedule, you likely only need to look at 1 week or 1 month at a time so macros can prompt for the date range and pull that data only.

matthewspatrick
10-24-2006, 05:18 AM
I agree with Jake. You'll find no bigger fan of Excel than me, but the app was simply not designed to handle heavy duty data storage. Access or some other db looks like the logical next step. (Depending on just what it is you're doing, you might want to look at more robust yet still free RDBMS like SQL Server 2005 Express or MySQL.)

SamT
10-24-2006, 08:42 AM
Try a procedure like this:

For each sheet
Delete last row+1 to end
Delete lastcol+1 to end

Note that ClearContents won't do the same thing.

SamT

supermonkey
10-24-2006, 11:33 AM
you could split your file into different workbooks having the userforms and modules in one workbook and having it open seperate workbooks for your data. That way you would never need to save the original large document, just the smaller information workbooks

Zack Barresse
10-24-2006, 03:46 PM
I'm completely agreeing with Patrick and Jake on this one. I would not recommend splitting this into multiple files. This sounds like a database all the way. :yes

GaryB
10-24-2006, 04:28 PM
Thanks you all for your responses,
I had a version of this posted when I needed some help pulling information to a common page. It really isn't a very complicated workbook. It has some macro's to automate coloring in cells, it has some conditional formatting and the code I was refering to. Would it help if I posted a watered down version?

Gary

Jacob Hilderbrand
10-24-2006, 07:08 PM
You can attach a small file here, or upload the full file somewhere and post a link as we can take a look.

SamT
10-24-2006, 07:25 PM
All those formatted cells is what's eating up 39MB. Since you can only see one screen at a time, only visually format one screens display of the worksheet and only while its open. You can store the formatting values on one sheet, apply while the Sheet is Visible, then strip them before closing and saving.

Try this, make a copy , strip ALL the visual formatting and see how big it is.

Then for grins, strip all formulas, then all code, until you're left with nothing but Data.

I betcha one or two columns of formulas is as big as all your code. :)

SamT

There are other ways that others can see much better than I.

GaryB
10-25-2006, 04:13 PM
Hi,

I dropped the whole file, all 39+ mb of it on our ftp site. ftp.galaxypress.net (ftp://ftp.galaxypress.net). you will find a folder called public and the password is publc. In the public folder is another folder called schedule board and the file is there. If you are using explorer you should be able to drag and drop the file. I think Sam is probably right and I'll give his suggestion a try. Thanks again to everyone.

GaryB

GaryB
10-25-2006, 04:48 PM
Hi Again,

I took out the two macros "copyrows" and " copyback" and the file saves immediately. So, it looks like those pieces of code are the problem. If anyone takes a look at this and can come up with a better solution, other that doing away with the function of these macros, I would appreciate any ideas.

Thanaks as always.

GaryB

SamT
10-25-2006, 09:36 PM
Gary,

I "cannot find server" on that link.

SamT

GaryB
10-26-2006, 04:22 PM
in your browser instead of http:// enter ftp.galaxypress.net (ftp://ftp.galaxypress.net) instead and that should get you there.

gary

SamT
10-26-2006, 05:22 PM
Nope.
Still can't get in.
I tried:


ftp://ftp.galaxypress.net, and *.com
ftp://galaxypress.net, and *.com
ftp://(ftp.)public(.)(/)galaxypress.net(com)(.)(/)public


I even put you in my "trusted Sites."

GaryB
10-27-2006, 07:18 AM
drop the ftp:// and put ftp.galaxypress.net (ftp://ftp.galaxypress.net) If you are using explorer that should put you right into the site. If not contact me at gary@galaxypress.net and I'll email you a zipped copy.

Thanks for the interest,

Gary

SamT
10-27-2006, 04:20 PM
Ya gotta have ftp:// or http:// to use the internet.

I can access a public folder when I http to public/galaxypress.net

What is the exact name of the schedule board folder and the file?

If the password is publc(sic) what is the username? public also or anonymous?

Samt

GaryB
10-27-2006, 04:32 PM
The folder is "public", the password is "public" the folder in the public folder is "schedule board" and the file is in that folder. The ftp site is ftp.galaxypress.net (ftp://ftp.galaxypress.net) I just went through the whole thing and it opened without a problem. When I said drop the FTP\\ explorer will automatically put it back. Thanks for trying.

Gary

ps here's the link I used. ftp://ftp.galaxypress.net/

SamT
10-27-2006, 06:03 PM
Gary,

You've posted the same URL 4 times now.

If we can fix this, you can get one of the XLMasters to look at your file.

If you already know the following, please skip to the bottom. :beerchug:

URL stands for Uniform Resource Locator. URLs get longer as the object or Resource gets smaller.

The URL to galaxypress.net looks like this;

ipAddress/WebRootFolder/index.htm

The URL of your .xls file probably looks like either,

ipAddress/WebRootFolder/public/schedule_board/FileName.xls

or like

ipAddress/public/schedule%20board/FileName.xls

I know the ipAddress, I can T&E the rest. But I don't have a clue as to the FileName.xls part.

Before you do anything else, put those two macros back in, but make sure they're killed dead and out of memory before you save.

SamT

GaryB
10-30-2006, 03:29 PM
the problem with the ftp address is when you get to ftp.galaxpess.net (ftp://ftp.galaxpess.net) it is going to automatically pop up and with the name public as the folder and ask you what the password is. There is no way to get around this so the longer url is not going to work. When you enter the password it puts you into the public folder which has a folder in it named schedule board which has a file in it named schedule board 2006.xls. I am assuming you are using explorer to to do this and you should be able to drag and drop the file. So I guess the long url would be ftp.galaxypresss.net/public/scheduleboard/scheduleboard2006.xls (ftp://ftp.galaxypresss.net/public/scheduleboard/scheduleboard2006.xls)
I hope this works. Let me know. I'm not sure a link from here is going to work. You may have go directly from your brower rather thaN these links.

thanks

gary

mdmackillop
10-30-2006, 04:47 PM
Hi Gary,
I downloaded it at 42.6Mb, opened it and saved it in Excel 2003 and it has reduced to 4.6Mb. Unfortunately zipped it's a little to big to repost here (256kb)

mdmackillop
10-30-2006, 04:53 PM
I've removed the colour from Column A beyond row 400 and to the right of the column headings prior to zipping. Otherwise no changes made.

SamT
10-30-2006, 06:25 PM
Told ya that in #9.

mdmackillop
10-31-2006, 01:21 AM
Told ya that in #9.
Not a particularly helpful comment.

This saved and zipped version of the original has no changes, but for some unknown reason saved at 2.26Mb. I know this may not be a solution, but what size file do you get in unzipping it?

SamT
10-31-2006, 07:18 AM
2.7MB

I'm really glad someone else was able to download that. I wasn't looking forward to DL'ing 30+MB on my slow dialup, but as long as I was the only one trying, I figured someone had to.

SamT
10-31-2006, 07:22 AM
This got me to wondering, so I created and saved some sheets and here's what I got.

Criteria File Size
Empty Sheet 13,824
Row 1 filled 14,336
Rows 1 & 2 Filled 15,872
First & Last Cells (A1 & IV65335) Filled 37,376
Rows 1 thru 20 filled 44,032
Column A filled 2,415,104
Columns A & B Filled 3,339,776
Columns A & B Filled & Summed in Col C 5,018,112
Color Column A 13,824
Color Columns A & B 13,824
Color Columns A & C 13,824
Color Alternating Rows 768,000
Color Alternating Rows & Fill Columns A & B 3,350,528
Color Alternating Rows & SUM A+B in COL C 5,022,208

GaryB
10-31-2006, 11:44 AM
WOW!!!
Once again this forum is an amazming place. Mdmackillop, your solution worked like a charm. It unzipped at 2.3 mb and saves immediately. Thank you so much for yours and everyone else's help. I will mark this one solved.

Thanks again

GaryB
10-31-2006, 12:40 PM
I hate to reopen something that is solved, but, I am perplexed. Could zipping and unzipping the file cleaned out whatever was making it a 40MB+ file? I unzipped the origianal the Mdmackillop had zipped ( and I did clear out the colors past 400 etc...) but prior to doing so it still upzipped at the smaller size, 2.3MB. So, the only thing different was zipping and unzipping the file.

GaryB

mdmackillop
10-31-2006, 12:47 PM
Hi Gary,
The zipping had nothing to do with this, I downloaded and saved your file and it was reduced to the smaller size. :dunno
For the future though, avoid colouring whole rows and especially columns. You could also try the ExcelDiet KB http://vbaexpress.com/kb/getarticle.php?kb_id=83

GaryB
10-31-2006, 01:32 PM
Yeah,
I see the error colorizing more that what you need but the rest of this is really goofy. Go figure. Thanks for the help it is working great. Let's see what happens from this point.

Gary