Free Microsoft Excel 2013 Quick Reference

Excel Workbook takes a minute to open

Hi,
I have an Excel workbook, with about 1000 lined of VBA code. It has 8 sheets, most of them are just tables and charts (1 chart per sheet). One sheet is a form sheet, which has several bottons and combo boxes, and a graphic backround. The whole workbook's size is about 0.5MB.

It takes about a minute to open it, which comes as a surprise, as it weights almost nothing. I tried to disable the workbook_open macro, but it didn't matter much.

Any ideas guys?

Thanks!


Why does Excel 2007 take 30 minutes to save my 35MB file?? This never used to happen in Excel 2003. Why is this happening? What can I do to reduce this time?

Help!! A workbook was created in Excel 97. A few months ago the file opened fine on another computer with Excel 2000. Since then, the file is taking longer and longer to open. It was emailed to someone recently and opened fine on their computer with Excel 2000. I tried "Detect and repair"...no change. I tried running Norton's WinDoctor...no change. I uninstalled Office 2000, reinstalled Office 2000...no change. After waiting for the file to open completely, I tried saving it as an Excel 95/2000 file and as a template. Closed the original, tried opening the newly created files... no change. I did a search of the hard drive for *.tmp files, found over 4,000(!) files, deleted all...no change. I reloaded Win 98 SE, the file still took over 9 minutes to open, so I believe I'm making some progress, but not there yet. I'm out of ideas, short of reformatting the hard-drive and starting fresh. Any ideas out there?
Mike

I have a file exported from an internal software where I have no access
to modify the settings, the file is approx. 2.7mb. It takes 1 minute
and 15 seconds to open the file. Even my 7mb files don't take this
long. File contains 2 logos, approx. 600kb. Even I remove them, nothing
changes, it still takes same duration to open. There is no formula or
other objects. It goes down 12,000 rows and 26 columns with some
columns merged.
I need to open this file with a code as part of some process, I tried
"calculation to manual" as well as "screenupdating to false", no avail.
When I tried to with Open and Repair facility, it works fine and fast
moreover the data stays as I really need, however I was not able to run
this way through code and I have to get over the messages during that
process.

I am not able to find any posts for this, all posts suggestions require
opening the file to get the data. Any help is very much appreciated.

Regards,
Bayo

Hi all,

I have a spreadsheet that is 50MB and is taking close to a minute to open.
The spreadsheet contains an Output table that has 120 VLookups and 12 other data worksheets each containing a variety of 350 IF, SUMIF and other formulas.

I also have a macro that I run on each data sheet that performs conditional formatting, copies unique records, copies down formulas, sorts AZ which then updates the Output table. – this also takes about a minute to complete.

Without going into the mechanics or reengineering of the sheet is there anything simple someone can recommend that I can apply to reduce the size of the spreadsheet or the time it takes to open or run the macro?

Many thanks

How can I automatically save Excel Workbooks every few minutes to the file
name as I could with Office 2000?

I created a workbook on excel 2003 and works fine, when I try to open it on
excel 2000 it takes an eternity to open. its a big document 6MB it haves
formulas and macros. earlier I had the same document that was 12MB and opened
fast in the 2000 excel but now thats smaller is slower....

Hello all,

I'm hoping you can help me because I'm quite stuck. Here is what I know.

I'm using XP SP3 Excel SP3 with all updates.

Excel Files on both a Novell/Windows Server take minutes to open (4MB files).

If you copy them to your desktop first, they open fast.

If I uninstall update KB2502786 - it seems to fix the problem - however this problem came back for one of my users, and uninstalling KB2344893 fixed the problem again. I'm concerned that the only way to fix this is to uninstall an Excel update.

I've used proccess monitor and I've identified that when the updates are installed, excel reads the file only 4 or 6 bytes at a time! When the updates are not installed, it reads the file at over 16,000 bytes at a time.

How do I fix this problem? Ideally I would still like the updates installed. Is there a registry tweak or something where I can change how it reads networked files?

This happens both when I double click a file in explorer, or when I go File -> Open.

Excel freezes for several minutes (and then the file loads).

I've cleared my temp files, tried a different user profile, reinstalled excel, repaired excel, renamed my normal.dot file and I'm running out of options.

Do any of you have any ideas of what I can do to resolve this problem?

Thanks,

- Mike

Office 2007 performance issues - docs taking minutes to open and print

I have tried about everything I can find in forums, etc... but am still having issues. I have system that is running Windows 7 with Office Pro 2007 and MS Word 2007 is hanging randomly when I open or print documents. It is not always the same documents nor does there appear to be a specific combination of things setting this off. The system was recently rebuilt as it was upgraded to Windows 7 from XP and previously had Office 2003. At first it seemed the issue may be linked to Office 2007 updating the 2003 files but now even new documents created in 2007 have the same issue. It can take up to 2 or 3 minutes to open a document - does not matter if it is 5 pages or 100 pages. I have removed all the printers and reinstalled them, checked to ensure there are not netowrk issues back to the server, diabled all add-ons, and still the issue is present. Any thoughts?

Large Excel 2007 files take too long to open

Opening an excel 2007 file of about 24 MB size is taking about 25~30 seconds. The file is a data dump and just has a lot of data in a single sheet. It does not contain any macros or formulae/calcs etc.

If I convert the same file(do a Save As) into an Excel 2003 file (.xls) it opens up very quickly.
Can anything be done to reduce the time to open such large files in Excel 2007 format (.xlsx)?

Hello,

I had an excel file (v1) with 26 mb that worked well until one day I
had to include some additional data that would work as an option,
therefore I included an option button.

This new file (v2) started to take about 10 minutes to open and worked
really slow, so I had to change it to manual calculation.

I converted the slow file in a much lighter version (vlight) , so the
calculus ist's fast again, but it still takes the same 10 minutes to
open. The original version v1 still opens and moves fast, and has
twice the size of ths one.

I put too much of work in this file so I can't really return to the
original to see what's wrong. Can anyone help?

Thanks

PS: It has no external links. It is a main file of a group of about
six that distributes info.

I have a 7 MB Excel file that takes about 40 minutes to open up. If I
turn off auto-calculation, the file opens almost instantly. I have a
desktop with Intel P4-2.8GHz, 512MB, Win2K, Office XP with SP3. The
thing is, my coworkers who have older/slower machines can open the file
almost instantly whether auto-calculation is on or off. There is one
other person in the office who is experiencing the same problem.
Any thoughts?

I have two workbooks called INPUT and VIEW.
They both have links to each other, but far more cells in INPUT refrencing values in VIEW.
INPUT is a shared file.

When I open VIEW then open INPUT, everything is fine.
When I open INPUT then open VIEW, it takes forever.

However if INPUT is unshared, then it doesn't cause this problem.

Any ideas of what is happening here or how to avoid it?

Is there any way I can not allow people to open VIEW if INPUT is already open?

Having a sub in the OPEN event of VIEW doesnt work, it is executed too late.

Hey.. I am trying to figure out how to set up a workbook so that all of the following can occur:

1) The workbook is shared
2) It requires a password to open up as editable, otherwise it will open up as read only.
3) If it opens up as editable, it should require another password to be edited except when editing certain columns. (aka, the workbook should be protected except for certain columns).

So to clarify, if I open the file and do not have the password, the only thing i can do is view the data - I cant edit anything. If I have the password to open the file, all I should be able to edit is certain columns, unless I have the separate password to unprotect a sheet which will give me full control.

Is there a way to get all of these things at the same time? Are there any problems when running shared excel files over a network where 2 users are able to edit the file?

Thanks.

My new Excel workbook is always slow to open, save or exit, sometimes taking as long as 3 minutes. The file is only 733 Kb, but it does contain about 9 macros. Saving the macros to a personal macro workbook is not an option, as the workbook will be shared and used on many computers.

Although the macros are recorded, I did go back and edit them to clean them up, removing uncecessary scrolling and stuff. I'm sure they're not as efficient as macros written in VBA, but I don't know VBA. The workbook is also chock full of formulas, but that is unavoidable.

Any ideas what is causing the slow load and save times, or suggestions to prevent it, would be greatly appreciated.

Thanks in advance.

Jerry

Hello,

I have created 19 excel workbooks in a directory that are all basically the same. I currently have to open each workbook to refresh the pivot tables and to update the links. Is there a way for me to automatically run the macro in each of the workbooks?

Thank You

Can someone help me how to open a excel sheet which has a password to open

Thanks

I'm trying to have a loop to open a set of workbook one at
a time to sort and copy some data out and then close it.
The following code ONLY works for the first workbook, it
fails when it tries to open the 2nd workbook and prompted
me "Run-time error '1004' Application-defined or object-
defined error."

Can someone please tell me what I need to do to have s.t.
I can open this set of workbooks in a loop ?

Thanks in advance for any assistance.

dim wkbk as Workbook
dim fPathname as String

For i=1 to 5
fPathname = ("c:Myfile"+cstr(i)+".xs"
Set wkbk = Workbooks.Open(fPathname)
j = 10
Do While wkbk.Sheets(1).Cells(j, 8).Value > 0
j = j + 1
Loop
MsgBox (fPathname + " contains " + CStr(j - 1) + " rows
of data.")
' *** Copy data and other stuff here.
Application.DisplayAlerts = False
wkbk.Close fPathname
Application.DisplayAlerts = True
Set wkbk = Nothing
Next i

Both Word and Excel crash when I try to open a file from within the program.
As soon as I click on a folder, the programs hang. I remember setting a
condition for "hiding" my files when I created two additional user accounts.
I have been unable to find that option to turn it off (assuming it has
something to do with the problem). I reinstalled Office XP, but the problem
persists. I have no trouble opening a file if I go to the file first and
open it by double clicking it. Nor do I have any trouble opening a file in
the list of recent files. The only change in configuration that I can recall
is the aforementioned hidden files setting.
Intel Pentium 3.4, ASUS P5GD2-X, 2GB Mem, Windows XP Pro, Office Pro

I have a local spreadsheet which has a couple of queries. When I try to edit
the query, it takes about 10 minutes to open the query wizard. When I
select to edit it in MS Query it takes another 5 minutes to open. Why is
this?
--
Tony

Hello everyone,

Hope everyone is having a great day!

I am having trouble getting this code to loop though a folder.

I am trying to rename all the excel workbooks in a folder, to corresponding cells in each workbook, the workbooks should be
renamed using the values of the selected cells as follows,

F4G4H4
F4 is a proper name, G4 is a set of four numbers and F4 is also a set of four numbers. There aren't any illegal charaters in the cells I have selected.

The excel workbooks (will be 100's at a time) in the folder.
I have had success with the "loop" code with 3 other codes, this one gives me the 'A file named (me98981212, as an example) already exists in this location. Do you want to replace it? message box.

I can see the selected workbook open and the name of the file, in the title bar change. But, when I select Yes to the changes, the name of the excel file reverts back to the original name.

I don't need the message box to appear to confirm the change either.

Could someone provide some insight to this?

I hope I have provided enough information, to make the issue clear.

Here is the final code I am using.

Option Explicit

Sub Exec_Macro_For_All()

Dim sPath As String
Dim sFile As String
Dim sDir As String
Dim oWB As Workbook
Dim i1 As Long
Dim iMax As Long

On Error GoTo Err_Clk

sPath = "D:loop test"
If Right$(sPath, 1) <> "" Then sPath = sPath & ""

sDir = Dir$(sPath & "*.xls", vbNormal)
Do Until LenB(sDir) = 0
Set oWB = Workbooks.Open(sPath & sDir)

ActiveWorkbook.SaveAs Filename:=Cells(4, 6).Value & Cells(4, 7).Value & Cells(4, 8).Value

Application.ScreenUpdating = False

oWB.Save
oWB.Close False
sDir = Dir$
Loop

Err_Clk:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub
My Thanks in advance.

Hi,

Does anyone know how to set a hyperlink to open the link within excel -
instead of in the browser.

I have tried to no avail. Will this require some VBA code - if so can
somepone please provide an example code.

Thanks,

DT

Hi
I am trying to add a VBA button in excel to open IE to a web page. I have a link for that page on my desktop where I can click on it and it does this for me but I would like to add a button on a excel worksheet to do this also.

Thanks Jeff

I have a huge file with a lot of formulas and cross references. The file was originally created in Excel 2007. Recently we upgrated to Excel 2010 after which the file is giving all the trouble in the world. It takes a minute to open and save any change. The biggest concern is that all the numbers are not being updaed automatically. Even though in the option, the Workbook Calculation is set to Automatic but actually the numbers to dot get updated. The strange thing is that when I open the file in Excel 2007, it starts working pefactly.

Anything, any solution which I can use to fix the issue. Appreciate your help.

Thanks.

I recently installed Service pack 2 for Office 2003 and now my Excel
documents take much longer to open. Can anyone help me correct this problem.