Excel VBA links to other files

[ChristianDK]

Hello

Im trying to link to 125 other Excel files based on input from cells

Sub Link()
For i = 1 To ActiveWorkbook.Sheet.Rows.Count
Range("Sheet1!B(i)") = "D:\Documents and Settings\New Folder\Range" & ("Sheet1!B1") & Range("Udtrækrapportpakker!B1")
Next
End Sub

Im i totally off?
Hope someone can help me out

 

[xlbo]

sheets("Sheet1").Range("B" & i).FORMULA

Would be the way to write the 1st part. How to write the actual formula depends on what is held in Sheet1 B1 & ("Udtrækrapportpakker!B1")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ChristianDK]

Thanks xlbo

Range("Sheet1!B(i)") = "D:\Documents and Settings\New Folder\Range" & ("Sheet1!B1") & Range("sheet!B2")

with B1 and B2 I'm trying to complete the link

D:\Documents and Settings\New Folder\Range & workbook2 & sheet1!A1

The point is that i want to type in the link info in cell B1 and B2.

I am able to perform this on one cell but i have to problems, one the link comes in to the cell as text and i need to go in to the cell manual and update it. 2nd i can not seem to get it to work on all active rows.

Hope this specify my problem so someone can help me

 

[GlennUK]

ChristianDK, you say "Thanks xlbo", but do not seem to have learnt anything from his post.

sheets("Sheet1").Range("B" & i).FORMULA

see how a range on a sheet is referenced in VBA? Notice how the .FORMULA qualifier exists?

Also, your link formula looks wrong ... do macro record and generate the link manually in a cell, and post the resulting code back here ( or you might see how links actually look in a formula, like starting with a "=" for example ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

this will not work:

New Folder\Range"

You need a workbook reference. The referenceing path would be:

\\Root\Folder Name\Folder Name\[WorkbookName.xls]SheetName!RangeDefinition

So you need a set of folders, a workbook name, a sheet name and a range before this will work as you wish. You will also need to use the INDIRECT function but being as that won't work on closed workbooks and by the fact that as you have a full path in there, you must be referencing a closerd workbbook, you might have a trouble or 2

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ChristianDK]

Don’t get me wrong, I am really glad fore your relays, but my problem is not the link it self…

I do know who to make a link and it works. I as wrote I can get a link in to a cell but it comes in as a text string and I have to manual update it by entering the cell and pressing enter or adding:
SendKeys "{F2}", True
SendKeys "{ENTER}", True
To the macro

However id like to know if anyone had some info on how to stuff info in to a cell and forcing it not to become a text string but a formula from the beginning.

2nd Id like to do this on all active rows in the worksheet. Not sure how this is done, I know its possible to count all active worksheet, but can it be done to count all active rows in a worksheet?

I have around 125 Excel files that I need to link to and for it to be easy to use for the end use, the link info need to be typed in separate cells.

If can be done with a =INDIRECT() formula in excel but then all the excel files that being linked to needs to be opened. To avoid the 21 min wait time it takes to open all the fields id like to use a dead link.

 

[GlennUK]

To do something similar to INDIRECT but without opening the source workbooks then use EXT.INDIRECT from Laurent Longre, as in his free MOREFUNC add-in from here:

http://xcell05.free.fr/english/

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

ok - first. I have already told you why it won;t work. you need to use

Range("Sheet1!B(i)").FORMULA = "=D:\Documents and Settings\New Folder\Range" & ("Sheet1!B1") & Range("sheet!B2")

If it is still coming out as a text string then you have cells formatted as text. You need to make them not formatted as text prior to entering the formula so:

For i = 1 To ActiveWorkbook.Sheet.Rows.Count
Range("Sheet1!B(i)").NUMBERFORMAT = "General"
Range("Sheet1!B(i)").FORMULA = "=D:\Documents and Settings\New Folder\Range" & ("Sheet1!B1") & Range("Udtrækrapportpakker!B1")
Next



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ChristianDK]

Thanks Glenn

That's exactly what I am looking fore.

Got some miner problems. If anyone else is reading this its called INDIRECT.EXT

It actually returns the right info from the link in the "function arguments" window but returns a #value! in the excel sheet. Is there any other plug-inds with the same function that works? :=)