Solved: Large Excel File

[GaryB]

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]

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]

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]

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]

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]

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.

[GaryB]

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]

You can attach a small file here, or upload the full file somewhere and post a link as we can take a look.

[SamT]

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]

Hi,

I dropped the whole file, all 39+ mb of it on our ftp site. 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]

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]

Gary,

I "cannot find server" on that link.

SamT

[GaryB]

in your browser instead of http:// enter ftp.galaxypress.net instead and that should get you there.

gary

[SamT]

Nope.
Still can't get in.
I tried:

[VBA]
ftp://ftp.galaxypress.net, and *.com
ftp://galaxypress.net, and *.com
ftp://(ftp.)public(.)(/)galaxypress....m)(.)(/)public
[/VBA]

I even put you in my "trusted Sites."

[GaryB]

drop the ftp:// and put 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]

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]

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 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]

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.

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

The URL to galaxypress.net looks like this;
[VBA]
ipAddress/WebRootFolder/index.htm
[/VBA]
The URL of your .xls file probably looks like either,
[VBA]
ipAddress/WebRootFolder/public/schedule_board/FileName.xls
[/VBA]
or like
[VBA]
ipAddress/public/schedule%20board/FileName.xls
[/VBA]
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]

the problem with the ftp address is when you get to 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
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]

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)