Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

An Error Occurred While Saving The File

Hello, this is my first post just trying to get an answer on a problem I am having with Excel. I open an .xls document and it needs to be formatted so I do what is required and then I need to fit it to one page so I go to Page Setup and change it to Fit To: 1 page. Now I go to Save As and save the file as something else and I receive an error saying: "An error occurred while saving the file." Excel crashes and the file is not saved. I uninstalled office and then reinstalled... did the office updates and still got the same error. The error is reproducable. Anyone have any thoughts, ideas, or answers?? If you need any more information, please feel free to ask and I will reply ASAP. Thank you.


Post your answer or comment

comments powered by Disqus
I want to find the last row in within the range Z22 to AB52. However an error occur if all the cells within the range are empty. I tried to add in "On Error Resume Next" but the lastrow value did not return a 0.

How can i assign lastrow value to be 0 if all the cells are empty?
Thanks for any help in advance!

Sheets("sale").Select
With Range("Z22:AB52")
On Error Resume Next
lastrow = .Find(What:="?*", After:=.Cells(1, 1), LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End With

Hello all. I signed up here to ask for some help with the code I am going to post. The code works, but I sure it is not the most efficient way to do what I am doing and there are some problems that arise after many hours of execution that have baffled me to the point I need fresh eyes and more excel knowledgeable people to look at it.

In a nutshell, this code is behind a report that has to print each morning at 7am or desired time and generate a .csv file for storage backup of the data. It is also triggering database queries once an hour for new data. I give the user input boxes to set the Print time, view minutes and seconds to next update of data, I display current time to be compared with Next Print Time and give them input boxes to give a file path where the csv file will be written. It all works fine.....for a while.

I think my problem stems from keeping the time displayed accurately in a cell for viewing and using it for the compare to know when to print and save.

What I have noticed is that the longer the code runs, the more it loads up on memory and processor time to the point it will eventually crash. I have tried to slow the code down with WAIT commands, Sleep commands and experimented with some For/While looping but all of my attempts have affected either the time updating or paused function of the spreadsheet while waiting or sleeping.

I don't do a lot of excel vba so my methodology may be laughable, but for better or worse, here it is. Any help with this will be much appreciated.

With the exception of a couple of buttons coming from Sheet1 writing to Global bits, all code is in this one module. A screenshot of the user input area is also attached. Thank you.

Global Poll_Timer As Integer
Global RunCode As Boolean
Dim Timer_Interval As Double
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Auto_Open()
    PollTimer = 1 'On Open Start Poll Timer Counts at 1
    Call Start
End Sub

Sub Start()

Dim Interval As Double

    RunCode = True 'Set True at Startup
    Interval = CDbl(Range("L1").Value) 'Get the Interval Value (For Time Incrementing) from Cell L1
    Call Timer(Interval)
End Sub

Sub Timer(Optional ByVal Interval As Double)
    If Interval > 0 Then Timer_Interval = Interval 'Sends Interval Time from Cell L1 if > 0
    If Timer_Interval > 0 Then Application.OnTime (Now + Timer_Interval), "FcCalls" 'Update Time + 1 sec
End Sub

Sub FcCalls() 'Sub to call all System Functions
    If RunCode = True Then
    Call UpdateTime
    Call Watchdog
    Call Timer
    End If
End Sub

Sub UpdateTime()
    Sheet1.txtTime.Value = CStr(Time) 'Updates Current Time Cell
End Sub

Sub Watchdog()

Dim Poll_Timer_Countdown As Integer
Dim fPath As String
Dim fName As String
Dim fName_Print As String

    fPath = Sheet1.Range("G5") 'Get file path from Cell G5
    fName = Sheet1.Range("M6") 'Get file name from Cell M6
    strDate = Format(Date, "_mm_dd_yy") 'Append date stamp to filename for unique ID
    Sheet1.txtWritePath.Value = fPath & fName & strDate 'Update Current Save Path to Cell H3
    fName_Print = fPath & fName & strDate 'Generate file name for saving file

    Poll_Timer = Poll_Timer + 1 'Increment in seconds a poll timer for poll trigger
    Poll_Timer_Countdown = 3600 - Poll_Timer 'Reverse second output for countdown to next poll
    Sheet1.txtNextPoll.Value = Poll_Timer_Countdown 'Display poll countdown in seconds
    Sheet1.txtNextPollMin.Value = Format(Poll_Timer_Countdown / 60, "00.00") 'Display poll countdown in minutes

If Poll_Timer_Countdown < 1 Then
    Sheet1.Range("A1") = Now() 'Change value in Cell A1 to Prompt New Poll
    Poll_Timer = 1 'Resets for Next Poll
End If

If Sheet1.txtPrintTime.Value = Sheet1.txtTime.Value Then
        
    On Error GoTo PrintError

    Sheet1.PrintOut 'Print Report at Set Time
    
    On Error GoTo FileError
    
    Sleep (1000) 'Pause 1 second before continuing

    'The following code is to generate and save a csv file backup
    ChDir fPath
    Workbooks.Open Filename:=fPath & "DailyReport.csv"
    Windows("cam_report_Master.xls").Activate
    Range("B9:N66").Select
    Selection.Copy
    Windows("DailyReport.csv").Activate
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=fName_Print, _
        FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWindow.Close
    Range("A9").Select
    Application.DisplayAlerts = True
 
    '***End CSV File Code***
 

Exit Sub

PrintError: ' Error handler in event no printer is found
MsgBox "Print Error:  Check your printer, network connections."
End If

Exit Sub

FileError: ' Error handler in event no file can be generated
MsgBox "File Saving Error:  An error occured while saving the daily report file.  Check your filename and path."

End Sub


Hello All,

I need to add a condition in my Excel file.

Cells A1, B1 and C1 should not be empty.
I would like to check this condition while saving the file.

Once these data is entered in the file, this check is not required for further save actions.

Please help me to implement this in Excel.

Regards,
Sudheesh

When I try to revise and save an excel file that is on a flash drive, I get
an error message that says "the file or directory cannot be created."

I want 5 members to work simultaneously on excel file. But it is possible
only one person can work .when accesing this file by others its showing the
error "an error occured initializing the vba libraries 40040".Please give the
solution ASAP..

Thanks & Regards

Sreejith K S

A friend of mine is using my spreadsheet, which contains two macros (which the wonderful people here at OzGrid helped me with!!!). The problem is she said that she is receiving the following error when she opens the spreadsheet:

An error occurred initalizing the VBA libraries (126)

She stated that she is using version Excel 9.0.2720.

Now I created the spreadsheet using Office 2003. I saw some information in regards to this problem, but it was for Excel 2000, so I figured, I better come here to be sure. Thanks so much!! Please let me know if there is any more specific information you require.

I've added hyperlinks to some cells in a work sheet. they work as long as I
don't save the file. Once I save the file I get an error message saying
"...the address of this site is not valid..."

Is there a restiction on the length of a file path?

In the Edit Hyperlink dialog box, the address contains a lot of "%20's" and
"/'s"; is this a convention of Excel for identifying paths?

I'm fairly shure that I have don this before with no difficulty, what is my
problem?

Darrell

How do I create an error message that will pop up when certain criteria are
met. I have a file that compares prior and current month changes. I would
like the file to give an error message, when it is closed, if the variance is
greater than $5,000 or 10%. I would like the message to give the option to
cancel save so the manager can go in and make changes. If no changes are
necessary, the manager should be able to save the file and exit.

Also, in the same file, I would like to use conditional formatting to
highlight the current month cells that correspond to variances listed above.
My problem is the variance is calculated in another cell that is not visible
to the manager. An example is: current amount entered in cell A1; prior
dollar amount is in HY1, dollar variance is calculated in HZ1 and percent
variance is calculated in IA1. I would like A1 to be highlighted if either
HZ1 or IA1 meet the variances listed above.

Please let me know if I need to post this to one of the other boards.

Thanks

An error occurred in the underlying security system. An internal error occurred.

I sent a digitally signed e-mail to a client. She responded to my e-mail with an encrypted, signed e-mail. I imported her cerificate. When I try to open this e-mail, I get the above error with no other additional support information. I have done a "repair" on the Microsoft 2007 Office Ultimate and it still returns the same error. I am able to open unsigned, unencrypted e-mails just fine AND I am able to open this by using Internet Explorer 8, going to to my e-mail server site and it opens the encrypted e-mail just fine. I am using Windows 7 Pro.
Any help would be greatly appreciated.
I will say that I am very aggravated and dissappointed with Microsoft in that this is a NEW installation of an OS AND a new installation of Office 2007 Ultimate and there are immediately issues with both and I am unable, apparently, to get any assistance from Microsoft in resolving Setup and first use problems on brand new software. Frankly, I do not have hours and days and posssibley weeks to look through endless UNHELPFUL Knowledge Base articles, etc. looking for a resolution. Microsoft should support their products better than this!!
I can type in the EXACT errors I am receiving and get nothing. When I do finally find a resolution, it usuallly has the EXACT wording of the error in the article. Why is that?
There is NEVER an obvious appropriate category to post my issue in!

When I want to open the Excel (97 version, Windows 98), I receive the
following message: "An error occurred initializing the VBA libraries
(65535)".When I click "OK" the Excel however opens, but can not open all of
my excel documents.The same message I recceive when I want to open in Excel
TOOLS/MACRO/VISUAL BASIC EDITOR. What has hapened and how can I resolve this
problem? Thanks, Jovan Milojkovic, Belgrade!

Hi

I created A workbook with a few macros. After I saved the workbook, close it and ope it all the macros have gone.
when i click on a button (assigned to a macro) it says "cannot run the macro "workbook.xlsx'!Nextinvoice'. The macro may not be available in this workbook or all macros are disable."
after that another error comes up saying "an error occurred while initializing the VBA libraries (40040)"

I tried reinstalling ms office 2007 and still doesnt work
can you please help?

Dear all,
I am a teacher and learning excel 2007. I have developed the result sheet for the students for 30 sections in 2 class. I want the respective class teacher to view only the excel sheets pertaining to their section. For this, I have created a log in form using macros and it is running fine.
BUT when i wanted to protect the workbook (structure) with a password so that users don't add or delete sheets, the error pops up " Run time error'1004' unable to set the visible property of the worksheet class.
When i debug the error, it is with the sheets-Users and Splash. but if I chage the codes, i don't get the correct result as before the protection. Below is the attached codes for both the workbook and login form. Please help me if the workbook structure could be protected with the above points as reference.
Thank you for you genuine support in advance.

BELOW IS THE CODE FOR THE WORKSHEET

Private bBkIsClose As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bBkIsClose = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
Worksheets("Splash").Visible = True
Worksheets("Users").Visible = True
For Each wsSht In ThisWorkbook.Worksheets
If Not wsSht.Name = "Splash" Then
wsSht.Visible = xlSheetVeryHidden
iCnt = iCnt + 1: ReDim Preserve wsArray(1 To iCnt)
wsArray(iCnt) = wsSht.Name
End If
Next
Application.EnableEvents = True
If Not bBkIsClose Then
For iCnt = 1 To UBound(wsArray)
Worksheets(wsArray(iCnt)).Visible = True
Next iCnt
Worksheets("Splash").Visible = False
Worksheets("Users").Visible = Fasle
Cancel = True
End If
Application.ScreenUpdating = False
End Sub
Private Sub Workbook_Open()
Dim wsSht As Worksheet
Dim bar As CommandBar
Debug.Print Application.CommandBars.Count
For Each bar In Application.CommandBars
Select Case bar.Name
Case Is = "MyToolbar"
'do nothing
Case Is = "MyOtherToolBar"
'do nothing
Case Else
bar.Enabled = True
End Select
Next bar
ActiveWindow.DisplayWorkbookTabs = True
Worksheets("Splash").Visible = True
Worksheets("Users").Visible = Fasle
For Each wsSht In ThisWorkbook.Worksheets
If Not wsSht.Name = "Splash" Then wsSht.Visible = xlSheetVeryHidden
Next wsSht
With Worksheets("Splash")
.Visible = True
.Activate
End With
Application.EnableEvents = True
frmLogin.Show
bBkIsClose = True

End Sub

BELOW IS THE CODE FOR THE LOG IN FORM

Option Explicit
Dim iCounta As Integer
Private Sub cmbValidate_Click()
Dim rUsers As Range, rPasses As Range, rSheets As Range
Dim lUserRow As Long
Dim wsName As Variant
Dim wsArray As Variant
With Worksheets("Users").UsedRange
Set rUsers = .Columns(1)
Set rPasses = .Columns(2)
Set rSheets = .Columns(3)
End With
If Application.WorksheetFunction.CountIf(rUsers, Me.ComboBox1.Value) < 1 Or Me.ComboBox1.Value = "" Then
MsgBox "Invalid Username", vbExclamation, "Alert"
ComboBox1.SetFocus
Else
lUserRow = Application.WorksheetFunction.Match(Me.ComboBox1.Value, rUsers, False)
If Not CStr(Me.tbxPW.Value) = CStr(rPasses.Rows(lUserRow).Value) Or Me.tbxPW.Value = "" Then
MsgBox "Invalid Password", vbExclamation, "Alert"
tbxPW.SetFocus

Else
UnlockSheets (rSheets.Rows(lUserRow).Value)
Unload Me
End
End If
End If
With Me
.LblTries.Caption = .LblTries.Caption - 1
ComboBox1.Value = vbNullString
tbxPW.Value = vbNullString
ComboBox1.SetFocus
End With
iCounta = iCounta + 1
If iCounta > 2 Then
MsgBox "3 Invalid Attempts. WorkBook Will Now Close", vbOKOnly + vbCritical, "Warning"
ActiveWorkbook.Close SaveChanges:=False
End If
End Sub
Private Sub CommandButton1_Click()
ActiveWorkbook.Close SaveChanges:=False

End Sub

Private Sub Label7_Click()

End Sub

Private Sub Label4_Click()

End Sub

Private Sub tbxPW_Change()
cmbValidate.Enabled = (ComboBox1.TextLength > 3 And _
tbxPW.TextLength > 3)
End Sub

Private Function UnlockSheets(sShts As Variant)
Dim wsArray As Variant
Dim iLoop As Integer
On Error GoTo BadShts
If sShts = "" Then GoTo BadShts
If InStr(1, sShts, ",") > 0 Then
wsArray = Split(sShts, ",")
For iLoop = 0 To UBound(wsArray)
Worksheets(wsArray(iLoop)).Visible = True
Next iLoop
Else
Worksheets(sShts).Visible = True
End If
Worksheets("Splash").Visible = True
MsgBox "DEAR TEACHER! Please READ these INSTRUCTIONS.The Result Processing System is Designed for 7 Sections with a Maximum of 50 Students in Each Section.You just have to feed the information in the StudentInfo Form marked with Yellow Colours only. After you have entered the information Save the file for Mid-Term and Annual Yearwise,eg-MT-2011,AN-2011 for not losing the data of the previous years.Finally,Don't Add or Delete any Sheets.Else your system will not function."
On Error GoTo 0
Exit Function
BadShts:
MsgBox "Invalid Sheet Names", vbCritical
End Function
Private Sub UserForm_Initialize()
Me.LblTries.Caption = 3
'sets source of combobox1
Dim myrow As Long
Dim myrange As String
Sheets("Users").Visible = True
myrow = Sheets("Users").Range("A65536").End(xlUp).Row
myrange = "Users!A2:E" & myrow
Me.ComboBox1.RowSource = myrange
End Sub
Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
' Prevents use of the Close button
If CloseMode = vbFormControlMenu Then
MsgBox "Quit Now?", vbOKOnly + vbCritical, "Warning"
ActiveWorkbook.Close SaveChanges:=False
Cancel = True
End If

End Sub

When opening by double clicking an Excell file or using a Shortcut to an Excell file, Excell 2000 starts to open, halts and displays an error message stating that the file connot be found or one of its components. After clicking ok, Excell finishes opening. Opening Excell first and then opening a file has no problems. I reinstalled Excell, re-createded the shortcuts, installed the latest server packs for Windows 98 and Office 2000, but the problem persists. No software has been installed or downloads from the internet (excepting email) have occured just prior to the problem developing.

Any help or suggestions would be greatly appreciated.

Gary

Here is the scenario:
1) add a data connection from an excel workbook, ie the "Target" to an excel workbook on another server, the "Source"
2) open the file with the connection ie. "Target"
3) allow a user to replace the "Source" file while the "Target" is open.

An error occurs that says the file is in use by another process...and the update fails

I have tried setting the Mode on the connection to both Read and Allow Deny None but neither work.

Suggestions?
thanks

Hey guys, a few questions this time, any help would be greatly appreciated.

1. Is it possible to stop a user saving a file, but still be able to save the file using macros?

2. Is it possible for, when a spreadsheet is loaded, the view to be set so that a selection takes up the screen no matter what the resolution / screen size of the monitor? If so how would I get the macro to run on launch of the sheet?

3. Is it possible to get a form to pop up on launch of the spreadsheet with a logo and just an ok button to get rid of it?

4. And finally, I have a cell which is populated from a database report, the project name often contains / symbols. I need a cell which is a duplicate of this cell however with the / and symbols replaced with the - symbol, is this possible as a worksheet function? Or perhaps when a macro is run some kind of find and replace coding?

THANKS for ANY help in advance!

We recently upgraded to Windows XP client with MS Office 2003. We noticed
an error message when saving Excel files. The files were created under
Office 2000 on Windows 2000 work stations. The message when saving the file
file after modifications is: "The file ( filename .xls) may have been
changed by another user since you last used it. In that case what do you
want to do? Save a copy ? Overwrite changes."

Has anyone seen this behavior? How do we make it stop?

Vanessa Graham
University of Baltimore

I have this code which works great. I can insert images and define the area that I want it to reside in. The issue is I would like it to work for PDF and DWF images as well. I've added the extensions into the code. When I run the code it will show me pdf and dwf files but when I select them I receive an error "An error occured while importing this file". When I debug this code line is highlighted

	VB:
	
 pic = ActiveSheet.Pictures.Insert(sPicture) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
. The jpg, gif, bmp & tif files all import properly it's only the pdf and dwf files that fail.

Can anybody please assist me in correcting this or advise me if this is possible or not?

Here's the code I'm currently using.


	VB:
	
 InsertFloorPlan() 
     
    Dim sPicture As String, pic As Picture 
     
    sPicture = Application.GetOpenFilename _ 
    ("Pictures (*.gif; *.jpg; *.bmp; *.tif; *.pdf; *.dwf), *.gif; *.jpg; *.bmp; 
    *.tif; *.pdf; *.dwf", _ 
    , "Select Picture to Import") 
     
    If sPicture = "False" Then Exit Sub 
     
    Set pic = ActiveSheet.Pictures.Insert(sPicture) 
    With pic 
        .ShapeRange.LockAspectRatio = msoFalse 
        .Height = Range("a7:a45").Height 
        .Width = Range("a7:i7").Width 
        .Top = Range("a7").Top 
        .Left = Range("a7").Left 
        .Placement = xlMoveAndSize 
    End With 
     
    Set pic = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Is there a way to save the file using the VB in the default local drive without defining the filepath ?
I don't want a pop up dialog to enter the file name. The file name will always be "aaa.xls" ?

Thanks.

Hi,

Can any one let me know that why I cant save the file which is about 6.53MB size. It contains 4 recorded macros, and many formulas. It is like a template and I will populate the data for my further workings. This was saved earlier and now when I worked and saved this file today it showed me a dialog box saying that "Document not saved".

Let me know if there is any thing that I need to change Viz., settings etc.,

Regards
Shankar

I have two hyperlinks within my main file that open other excel files. One of
the links works fine 100% of the time, but the other doesn't work once I save
the file. The location of the one that works is in the same folder as my main
file, but the one that bonks is located in another location on my network.

When I click on the problem link it says that the address of this site is
not valid. When I edit the hyperlink the address at the bottom has changed
from what it should be to the correct location preceeded by
..../.../.../.../.../

When I correct the link to the right file it works fine, but as soon as I
save the file and try the link again it bonks!!

Why would this be happening and what can I do to correct this???

hi

I need a VBA help on the attched file.what i currently manually doing after prpearing
the attched main file I need to make a below mentioned version having the same pivot structure as in
attched main file . FOr example for making Midwest version first i need to go Document report sheet tab in
attched main file and then go to the BU column and delete all the data except Midwest and then save
the file with the name mention below

version
1-Midwest BU_ Defects Closed as of 053109.xls- In this data is only related to Midwest
2-Northeast BU_ Defects Closed as of 053109.xls- In this data is only related to Northeast
3-Southeast BU_ Defects Closed as of 053109.xls- In this data is only related to South east
4-West BU_ Defects Closed as of 053109.xls- In this data is only related to West

Is that possible the version is created with help of VBA the data range is diferent every time

Hello again,

I have posted already the problem to delete the code when sending a sheet via email and I have found the solution to the problem.

I tried to do the same when saving the file to specific folder, but i am facing problem.
The code that I am using now is:

Sub SaveFile()

Dim CurrentPath As String
Const PathToSaveTo As String = "C:Documents and SettingsAll UsersDocuments"
    'Store the current path
    CurrentPath = CurDir
    ''Change the path to the one we want
    'ChDrive "C"
    'ChDir PathToSaveTo
    ''or perhaps...
    SetCurrentDirectory PathToSaveTo    '"SomeServerSome Path"
    'test to see if the folder is already saved in the correct folder
    
             With ThisWorkbook
            If .Path <> PathToSaveTo Then
            .SaveAs FileName:=PathToSaveTo & .Name
                     Else
            'file is already saved in folder therefore just save it
            .Save
            
        End If
    End With
    ''Change the path back
    'ChDrive CurrentPath
    SetCurrentDirectory CurrentPath
    
End Sub
Can I add somewhere in this code the lines that will delete the code form the file?

 
Destwb.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.DeleteLines 1, _
  Destwb.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.CountOfLines


I have a 'master' spreadsheet that I would like to add additional tabs to.
I'm not having any problems moving the worksheets into the document but when
I try to save the file its not happening. I have tried renaming the file,
saving to a pen drive but nothing seems to be working. I seem to be able to
save the first worksheet but then reopening and saving additional worksheets
without success. Would anyone have any idea why I am unable to save the
file.....thanks

I have two hyperlinks within my main file that open other excel files. One of
the links works fine 100% of the time, but the other doesn't work once I save
the file. The location of the one that works is in the same folder as my main
file, but the one that bonks is located in another location on my network.

When I click on the problem link it says that the address of this site is
not valid. When I edit the hyperlink the address at the bottom has changed
from what it should be to the correct location preceeded by
..../.../.../.../.../

When I correct the link to the right file it works fine, but as soon as I
save the file and try the link again it bonks!!

Why would this be happening and what can I do to correct this???


No luck finding an answer? You could always try Google.