directory window keeps opening up

[almagg]

i am linking cells between workbooks.

when i paste the directory(?) window opens up wanting me to select the correct file. like it doesn't know exactly which file i am linking.

this is what i paste.

='[2007 Department Expense Summary - 10018.xls]Summary'!D24

then the window opens up and i have to manually select the xls file that is in the brackets.

i dunno.

 

[erik.van.geit]

Hi,

assuming your filename is
"2007 Department Expense Summary - 10018"
this file could be anywhere on your machine ...
specifiy the path within the formula
like this
='C:\WINDOWS\Desktop\[2007 Department Expense Summary - 10018.xls]Summary'!D24

kind regards,
Erik

 

[hatman]

If the file being linked is not open, then Excel needs to know the FULL path in addition to the filename. So you have 2 options: 1) Have the linked file open when you perform the cop/paste function, or 2)copy/paste the full file/pathname only.

 

[almagg]

i had thought of that and did put in the full path name:

'ActiveCell.Value = "='N:\IT Analysis Group\2007 Plan\Cost Center Files to Link\[2007 Department Expense Summary - 10018.xls]Summary' D12

and the window still opens up.
something is not right here

 

[hatman]

Hang on... are you doing this in VBA?

 

[almagg]

yes

in fact when i manually do the paste, the window does not open up.

 

[hatman]

I'm surprised that you are not getting a runtime error... the exclamation point seems to be missing in your code:

'ActiveCell.Value = "='N:\IT Analysis Group\2007 Plan\Cost Center Files to Link\[2007 Department Expense Summary - 10018.xls]Summary'!D12

 

[hatman]

Hmm, can't seem to be able to duplicate your symptoms... I'm using this code:

ActiveCell.Value = "='U:\proceng\wip\test_procs\wpoga\oga\Administration\[N3OGA_Test_Tooling.xls]WPOGA Tooling'!$D$148"

The only difference I can see is your syntax error, but that throws a runtime error, not the symptom you are seeing...

 

[almagg]

that did not do it

the window still pops up.

there is another possible solution.
initially i just did a

ActiveSheet. Paste Link:=True,

='[2007 Department Expense Summary Initial Submission - 10331.xls]Summary'!$D$12

but i need to remove the absolute reference symbol($) and i couldn't find anything in the help file.


what i did was get the source cell address using:

myaddress = ActiveCell.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False)

then in the target cell:
ActiveCell.Value = "='N:\IT Analysis Group\2007 Plan\Cost Center Files to Link\[2007 Department Expense Summary - 10331.xls]Summary'! & myaddress"

is there another way of removing the '$' ?
then i could use:

ActiveSheet. Paste Link:=True

thanks

 

[erik.van.geit]

the syntax
".......... & myaddress" is not correct
you need
"........." & myaddress

best regards,
Erik