Hello,

I have the following code in a macro to setup a series of formulas in a
sheet. Trouble is, when run, Excel prompts for me to select the file from a
list. I don't think I should need to select it as I am being specific about
the path and filename in the code.

ActiveCell.Offset(0, 0).Formula = pathname &
Worksheets("variance").Range("I1").Value & ".xls'!" & ActiveCell.Offset(0,
12).Value

(ActiveCell.Offset(0, 12).Value adds a range name to the formula)

Any ideas anyone?

Thanks

Richard

hi
trying putting this just before the filepath line...
Application.DisplayAlerts = False
be sure to turn alerts back on BEFORE exiting the sub
Application.DisplayAlerts = True

regards
FSt1

"Richard M Burton" wrote:

Hello,

I have the following code in a macro to setup a series of formulas in a
sheet. Trouble is, when run, Excel prompts for me to select the file from a
list. I don't think I should need to select it as I am being specific about
the path and filename in the code.

ActiveCell.Offset(0, 0).Formula = pathname &
Worksheets("variance").Range("I1").Value & ".xls'!" & ActiveCell.Offset(0,
12).Value

(ActiveCell.Offset(0, 12).Value adds a range name to the formula)

Any ideas anyone?

Thanks

Richard

Richard,

It seems you're missing an equal sign and a single quote character to
precede the pathname. Try it this way:


ActiveCell.Formula = "='" & pathname &
Worksheets("variance").Range("I1").Value & ".xls'!" & ActiveCell.Offset(0,
12).Value


--
Hope that helps.

Vergel Adriano


"Richard M Burton" wrote:

Hello,

I have the following code in a macro to setup a series of formulas in a
sheet. Trouble is, when run, Excel prompts for me to select the file from a
list. I don't think I should need to select it as I am being specific about
the path and filename in the code.

ActiveCell.Offset(0, 0).Formula = pathname &
Worksheets("variance").Range("I1").Value & ".xls'!" & ActiveCell.Offset(0,
12).Value

(ActiveCell.Offset(0, 12).Value adds a range name to the formula)

Any ideas anyone?

Thanks

Richard

Thanks FSt1,

This certainly helped.

After I ran the macro I needed to update the links and this seems to have
sorted it.....after some further tweaking.

Regards

Richard

"FSt1" wrote:

hi
trying putting this just before the filepath line...
Application.DisplayAlerts = False
be sure to turn alerts back on BEFORE exiting the sub
Application.DisplayAlerts = True

regards
FSt1

"Richard M Burton" wrote:

Hello,

I have the following code in a macro to setup a series of formulas in a
sheet. Trouble is, when run, Excel prompts for me to select the file from a
list. I don't think I should need to select it as I am being specific about
the path and filename in the code.

ActiveCell.Offset(0, 0).Formula = pathname &
Worksheets("variance").Range("I1").Value & ".xls'!" & ActiveCell.Offset(0,
12).Value

(ActiveCell.Offset(0, 12).Value adds a range name to the formula)

Any ideas anyone?

Thanks

Richard

Thanks for the reply Vergel.

This was not the problem as the =' was included elsewhere, but I certianly
did not help myself by using Pathname as a variable!

Thanks again

Richard

"Vergel Adriano" wrote:

Richard,

It seems you're missing an equal sign and a single quote character to
precede the pathname. Try it this way:


ActiveCell.Formula = "='" & pathname &
Worksheets("variance").Range("I1").Value & ".xls'!" & ActiveCell.Offset(0,
12).Value


--
Hope that helps.

Vergel Adriano


"Richard M Burton" wrote:

Hello,

I have the following code in a macro to setup a series of formulas in a
sheet. Trouble is, when run, Excel prompts for me to select the file from a
list. I don't think I should need to select it as I am being specific about
the path and filename in the code.

ActiveCell.Offset(0, 0).Formula = pathname &
Worksheets("variance").Range("I1").Value & ".xls'!" & ActiveCell.Offset(0,
12).Value

(ActiveCell.Offset(0, 12).Value adds a range name to the formula)

Any ideas anyone?

Thanks

Richard