directory window keeps opening up

almagg

Well-known Member
Joined
Aug 21, 2002
Messages
705
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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

Well-known Member
Joined
Aug 21, 2002
Messages
705
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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

almagg

Well-known Member
Joined
Aug 21, 2002
Messages
705
yes

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

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Hmm, can't seem to be able to duplicate your symptoms... I'm using this code:

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

Well-known Member
Joined
Aug 21, 2002
Messages
705
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
the syntax
".......... & myaddress" is not correct
you need
"........." & myaddress

best regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,685
Members
410,697
Latest member
srishtijain0708
Top