Excel 2007 Macro/Link Issue (apologies for cross-posting)

I apologize in advance for anyone who has read a version of this post in the
programming thread. Sadly, all my research hasn't helped, and I still have
the issue :/ I'm sure it's a simple thing I'm missing, but I sure can't see
it! Anywho...

I have workbooks for payroll that update the YTD amounts from the previous
workbook's total. Each pay period has its own workbook and the workbooks are
all included in the same file folder. I created a macro to pull the
accumulated YTD info from the previous pay period and place it in the most
recent workbook. It worked wonderfully and without fault in Excel 2003. Our
company has since migrated to Office 2007 and suddenly, when I run the macro,
I am asked to select the file that contains the information for *each cell*.
Oh my. It's over 1600 times I would have to select a file manually (and i've
discovered that I sometimes click the wrong file).

I've looked at the file name and path, and everything seems to be fine - I
even changed the extnsion to .xlsm for macro-enabled 2007 workbook. I simply
can't figure out why it's asking me for every cell to select the workbook
that I indicated in the macro. I've tried changing settings in the options,
but nothing seems to help.

The code (not elegant, but it functioned at one time...) is below:

Sub YTD()

'MACROSUBTITLE
Sheets("OpenSheetName").Select

' Part 1

Range("E11").Select
ActiveCell.FormulaR1C1 = _
"='C:\FolderName\[FileName.xls]SheetName'!R13C5"
Range("F11").Select
ActiveCell.FormulaR1C1 = _
"='C:\FolderName\[FileName.xls]SheetName'!R13C6"

....And so on...

I sincerely appreciate any advice on this problem!

Jessica

xl 2007: Go into help and search for links. Scroll down until you come to the
following:-

Control the startup prompt for updating all links. Read both sections:-
Don't ask whether to update links when I open any workbook, and update links
automatically and also

Don't ask to update links for this workbook, and let me control whether
links are updated

I feel that the answer to your problem is described there. However, under
the Microsoft Office button- Advanced - 'Ask to update automatic links' is
under General heading not 'When Calculating this workbook' as help says. (It
is the next heading down).

Regards,

OssieMac


"MorgSz" wrote:

I apologize in advance for anyone who has read a version of this post in the
programming thread. Sadly, all my research hasn't helped, and I still have
the issue :/ I'm sure it's a simple thing I'm missing, but I sure can't see
it! Anywho...

I have workbooks for payroll that update the YTD amounts from the previous
workbook's total. Each pay period has its own workbook and the workbooks are
all included in the same file folder. I created a macro to pull the
accumulated YTD info from the previous pay period and place it in the most
recent workbook. It worked wonderfully and without fault in Excel 2003. Our
company has since migrated to Office 2007 and suddenly, when I run the macro,
I am asked to select the file that contains the information for *each cell*.
Oh my. It's over 1600 times I would have to select a file manually (and i've
discovered that I sometimes click the wrong file).

I've looked at the file name and path, and everything seems to be fine - I
even changed the extnsion to .xlsm for macro-enabled 2007 workbook. I simply
can't figure out why it's asking me for every cell to select the workbook
that I indicated in the macro. I've tried changing settings in the options,
but nothing seems to help.

The code (not elegant, but it functioned at one time...) is below:

Sub YTD()

'MACROSUBTITLE
Sheets("OpenSheetName").Select

' Part 1

Range("E11").Select
ActiveCell.FormulaR1C1 = _
"='C:\FolderName\[FileName.xls]SheetName'!R13C5"
Range("F11").Select
ActiveCell.FormulaR1C1 = _
"='C:\FolderName\[FileName.xls]SheetName'!R13C6"

...And so on...

I sincerely appreciate any advice on this problem!

Jessica

Grr...this didn't work. For every cell, I still must click on the file in a
dialog box. Anyone else with ideas?

Jessica

"OssieMac" wrote:

xl 2007: Go into help and search for links. Scroll down until you come to the
following:-

Control the startup prompt for updating all links. Read both sections:-
Don't ask whether to update links when I open any workbook, and update links
automatically and also

Don't ask to update links for this workbook, and let me control whether
links are updated

I feel that the answer to your problem is described there. However, under
the Microsoft Office button- Advanced - 'Ask to update automatic links' is
under General heading not 'When Calculating this workbook' as help says. (It
is the next heading down).

Regards,

OssieMac


"MorgSz" wrote:

I apologize in advance for anyone who has read a version of this post in the
programming thread. Sadly, all my research hasn't helped, and I still have
the issue :/ I'm sure it's a simple thing I'm missing, but I sure can't see
it! Anywho...

I have workbooks for payroll that update the YTD amounts from the previous
workbook's total. Each pay period has its own workbook and the workbooks are
all included in the same file folder. I created a macro to pull the
accumulated YTD info from the previous pay period and place it in the most
recent workbook. It worked wonderfully and without fault in Excel 2003. Our
company has since migrated to Office 2007 and suddenly, when I run the macro,
I am asked to select the file that contains the information for *each cell*.
Oh my. It's over 1600 times I would have to select a file manually (and i've
discovered that I sometimes click the wrong file).

I've looked at the file name and path, and everything seems to be fine - I
even changed the extnsion to .xlsm for macro-enabled 2007 workbook. I simply
can't figure out why it's asking me for every cell to select the workbook
that I indicated in the macro. I've tried changing settings in the options,
but nothing seems to help.

The code (not elegant, but it functioned at one time...) is below:

Sub YTD()

'MACROSUBTITLE
Sheets("OpenSheetName").Select

' Part 1

Range("E11").Select
ActiveCell.FormulaR1C1 = _
"='C:\FolderName\[FileName.xls]SheetName'!R13C5"
Range("F11").Select
ActiveCell.FormulaR1C1 = _
"='C:\FolderName\[FileName.xls]SheetName'!R13C6"

...And so on...

I sincerely appreciate any advice on this problem!

Jessica