Linked file Excel 2007

D

Dave Unger

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
 
D

Dave Unger

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top