Help with a macro

[TwEaKFrEaK]

Below is the a portion of a macro that I am creating.

Range("L56").Select
Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _
"='S:\Local Disk\Data\2004\Quarter 2, 2004\[Data Entry
Form.xls]Performance'!$A$2:$J$402"

ActiveWorkbook.Names.Add Name:="Data1", RefersToR1C1:= _
"='S:\Local Disk\Data\2004\Quarter 2, 2004\[Data Entry
Form.xls]Performance'!R2C1:R402C10"

I would like to macro to copy a cells contents. Right now its coping a
specific formula that is in L56 but if I change the filepath on the
sheet (L56) the macro changes it back to the original (i.e, "='S:\Local
Disk\Data\2004\Quarter 2, 2004\[Data Entry
Form.xls]Performance'!$A$2:$J$402")

Since the filename changes every quarter I want to macro to copy the
contents in cell L56 but I do not want to see the actual file name in
the macro. I think it has to be like a wildcard. (In L56 I will
generate the current directory and file name based on an input sheet.)

I know this is confusing, you have any other questions please let me
know. Thank you so much for your help. Your expertise is appreciated.

 

[GJones]

Hi TwEaKFrEaK ;


Go into VBA help and look at the topic for the ChangeLink
Method.

Thanks,

Greg

 

[TwEaKFrEaK]

Where is VBA help?

 

[Norman Jones]

Hi TwEaKFrEaK,

If the help files are not installed on your system, you will need to install
them from your Office Installation CD.

 

[TwEaKFrEaK]

Can anyone provide help using the changelink method? I do not have th
vba help files on my computer and the disk is at home

 

[Norman Jones]

Hi TwEaKFrEaK,

From VBA Help:

ChangeLink Method

Changes a link from one document to another.

Syntax

expression.ChangeLink(Name, NewName, Type)

expression Required. An expression that returns a Workbook object.

Name Required String. The name of the Microsoft Excel or DDE/OLE link to
be changed, as it was returned from the LinkSources method.

NewName Required String. The new name of the link.

Type Optional Variant. The link type. Can be one of the following
XlLinkType constants: xlLinkTypeExcelLinks or xlLinkTypeOLELinks. The
default value is xlLinkTypeExcelLinks. Use xlLinkTypeOLELinks for both DDE
and OLE links.


ChangeLink Method Example

This example changes a Microsoft Excel link.

ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _
"c:\excel\book2.xls", xlExcelLinks