VBA Code to Save and Close Files Not Working

Hello everyone:

Thanks to everyone for helping me in my last post.

I have some VBA code which I have started using in Excel 2007 which is used to save 2 open workbooks (which have the date as part of their filenames) and close the files, then close Excel itself.  The code is as follows:

Sub CloseFiles()
With Worksheets("Raw Data")
.Range("A398:A" & Rows.Count).EntireRow.ClearContents
End With
Sheets("Raw Data").Select
Range("A1").Select

wbname = ThisWorkbook.Name
wbfnlname = Left(wbname, Len(wbname) - 11) & "-D CAN I" & ".xlsm"
Workbooks(wbfnlname).Close savechanges:=True
With ActiveWorkbook
   .Save
End With
Application.Quit
End Sub

Sometimes when I run the code, I get a message from Excel asking me if I wish to save a file with a weird filename such as "F2E60000", with no extension, yet Explorer shows it to have the exact same size as the other files which I am saving down to the byte.  If I do not save the file, then the data in the files in the above code are not saved.

Previously, the code ran in Excel 2003 without a hitch.  What is going on?

Suggestions...

Use Option Explicit as the first line of every module.
Declare all variables
Close all workbooks before quitting Excel

Something like this...
'--
Sub CloseFiles()
Dim wbName As String
Dim wbFnlName As String

With Worksheets("Raw Data")
    .Select
    .Range("A398:A" & Rows.Count).EntireRow.ClearContents
    .Range("A1").Select
End With

wbName = ThisWorkbook.Name
wbFnlName = Left(wbName, Len(wbName) - 11) & "-D CAN I" & ".xlsm"
Workbooks(wbFnlName).Close SaveChanges:=True
Workbooks(wbName).Close SaveChanges:=True
'other workbooks?

Application.Quit
End Sub

Grayson Lee:

Sometimes when I run the code, I get a message from Excel asking me if I wish to save a file with a weird filename such as "F2E60000", with no extension, yet Explorer shows it to have the exact same size as the other files which I am saving down to the byte.  If I do not save the file, then the data in the files in the above code are not saved.

Previously, the code ran in Excel 2003 without a hitch.  What is going on?

Hi Grayson,

Not directly related to your code but you're not alone with this random error.
We have found since switching to 2007 that we get many more server related errors - or at least that how they appear.
We see this coming together with error messages relating to the file being in use or locked by 'another user' or by a named user, even if there is no one else in the file or if the user locking it is the user trying to save it.
It's been with our netowrk team for a while now with, unsurprisingly, no resolution or suggestions offered, but they believe flags are getting left in place; I wonder if it has something to do with the newer saving process and, in our case, that we are still running many workbooks in compatibility mode.

It'll be interesting if anyone else has any thoughts.

Hello everyone:

I have incorporated Jim's code, but I get the same problem when I run the code to save and close the files.  Any ideas or thoughts?

Grayson

The 8 digit file name is a temporary file that Excel creates during the process of saving a file.
When those files don't go away the blame seems to be placed on anti-virus program interference or on some sort of network complication.

I've never experienced the problem, but  try turning off your anti-virus program and see what happens.

I've only ever seen those files after excel has crashed during saving.  What Jim said above makes sense to me now.  But alas, I can't help either.  Just letting you know there's a sympathetic soul over here in Australia...

Hi Jim,

Your code closes the workbook with the code and hence never reaches the Application.Quit statement.
Seems to me this code is identical in function:

Sub CloseFiles()
Dim wbName As String
Dim wbFnlName As String

With Worksheets("Raw Data")
    .Select
    .Range("A398:A" & Rows.Count).EntireRow.ClearContents
    .Range("A1").Select
End With

wbName = ThisWorkbook.Name
wbFnlName = Left(wbName, Len(wbName) - 11) & "-D CAN I" & ".xlsm"
Workbooks(wbFnlName).Close SaveChanges:=True
ThisWorkbook.Close SaveChanges:=True
End Sub

Hello:

My apolgies for not getting back earlier.  I tried working without anti-virus, but the sharing violation still persisted.  I should also note that I am not on a network. 

I did try disabling the autorecover function for the workbooks.  That seemed to prevent the sharing violation for a while, but it has now returned. 

As it stands now, I intermittently get the sharing violation somtimes when I save the files, even without the macro code.

Any thoughts?

Hello:

I have an update.  I deselected the folder holding my VBA code under Indexing Options in the Control Panel.  Simply changing the Index Attributes by right-clicking the folder and going to Advanced Attributes does NOT work.

Since then, I have not had a sharing violation error when saving files in this folder either by VBA code or manual saving for over a week now.

Thanks,

Grayson

Hi Grayson,

Thanks for letting us know!!