Hello,

We have an application that keeps track of weekly transactions, and is
linked to a summary workbook. At the end of the week, a procedure is
run (from the weekly file) that saves the weekly file under a new
name, and adds new links to the summary file.

This application ran without a hitch for over a year, until the client
updated to Excel 2007. Now we're frequently experiencing the "Excel
has experienced a problem and needs to close, etc" message. I've been
able to reproduce the problem with the following bit of code.

Create 2 workbooks, "week_01.xlsm" & "summary.xlsx". From the summary
workbook, create a link to the weekly file. Add the code to the
weekly workbook. The 1st run proceeds without a problem, but the 2nd
run crashes while trying to open "summary.xlsx"

Strangely enough, it runs OK from the VBE. Also runs OK if the
summary is changed to an "xls" file. After making the appropriate
changes, it also runs without trouble in Excel 97. I know I'm
probably missing something quite basic, but I've been working on this
for a few days, and this is as far as I've got. Any insight into this
would be most appreciated.

Regards,

Dave


Sub WeeklyRollOver()

Dim x As Integer, str As String
Dim fName As String
Dim Wb As Workbook

Set Wb = ThisWorkbook
ChDir Wb.Path

'generate new week file name
str = Wb.Name
x = CInt(Mid(str, 6, 2)) + 1
str = Format(x, "00")
fName = "week_" & str & ".xlsm"

'Save with new name
Wb.SaveAs Filename:=fName, FileFormat:=52

'open summary file

fName = "summary.xlsx"
Workbooks.Open fName, updatelinks:=0

ActiveWorkbook.Close savechanges:=False

Set Wb = Nothing

End Sub

Hello,

I believe I've found what's causing our problem, hopefully this may
help someone else experiencing something similar.

I was running the procedure from a Form button on the worksheet -
changed it to an Active-X button, and now everything works fine -
repeated testing appears to confirm the 'fix'. However, although it's
a non-issue in our situation, it still fails if run from the Ribbon
(Developer Macros Macro name) - which makes me slightly uneasy.

Perhaps someone can offer an explanation for this.

Regards,

Dave