Update Links in Word from Excel Tables and Graphs

[MatteoCa]

Hi all,

I Have a problem trying to update links in Word from excel.

The problem borns when i try to update a table from excel because there isn't a path stored in the Item.

I try to explain it better.

I have two files excel and one word and 60 links.

I go to edit link and I can see, if i go inside the link and i click ITEM, that there is a string thus composed: WORKSHEET!RANGE talking about tables linked, instead ie INC3![file.xls]INC3 Chart 1 riferred to a graph.

OK

If i wanted change automatically the path for a graph, no problem, instead for update tables there is a problem.

Ok..maybe

Is possible to retrive the table source path directly from word, and use it pasting in file name?

I wrote this script:

Dim $continue = 1

While $continue == 1

$message = "Select the file word for work"

$message1 = "Select the file excel for work"

$percorso = @DesktopCommonDir

$percorso1 = @WorkingDir

$var = FileOpenDialog($message, $percorso, "word (*.doc)", 1)

$varxls = FileOpenDialog($message1, $percorso1, "excel (*.xls)", 1)

ShellExecute($var)

$var2 = StringSplit($var, "\")

$var3 = StringSplit($varxls, "\")

$filename = $var2[$var2[0]]

$filenamexls = $var3[$var3[0]]

$split = StringTrimRight($filename, 4)

$splitxls = StringTrimRight($filenamexls, 4)

$doc = $filename

$xls = $filenamexls

$active = $doc & " - Microsoft Word"

_WinWaitActivate($active, "")

_WinWaitActivate("Microsoft Office Word", "This document contai")

Send("n")

_WinWaitActivate($active, "")

Send("{ALTDOWN}e{ALTUP}k")

_WinWaitActivate("Links", "")

Send("{SHIFTDOWN}{END}{SHIFTUP}")

Send("{TAB}n")

While (WinWait("Change source", "", 3) <> 0)

_WinWaitActivate("Change source", "")

ClipPut(@WorkingDir & "\" & $xls)

Send("^v{TAB}{TAB}e")

_WinWaitActivate("Enter Text", "")

Send("^x")

_WinWaitActivate("Enter Text", "")

$clip = ClipGet() ; ottiene il valore della clipboard

$verifica = StringInStr($clip, "]")

If $verifica = 0 Then

_WinWaitActivate("Enter Text", "")

Send("^v")

_WinWaitActivate("Enter Text", "")

Send("{ENTER}")

_WinWaitActivate("Change source", "")

Send("o")

ContinueLoop

EndIf

$array = StringSplit($clip, "[]")

$copia = $array[1] & "[" & $xls & "]" & $array[3]

ClipPut($copia)

_WinWaitActivate("Enter Text", "")

Send("^v")

_WinWaitActivate("Enter Text", "")

Send("{ENTER}")

_WinWaitActivate("Change source", "")

Send("o")

WEnd

_WinWaitActivate("Links", "")

Sleep(300)

Send("{TAB}{TAB}{ENTER}")

_WinWaitActivate($active, "")

Send("^s")

Sleep(10000)

_WinWaitActivate($active, "")

Send("!{F4}")

$ans = MsgBox(4, "Program", "Do you want to manage another file?")

If $ans == 6 Then

; do nothing

EndIf

If $ans == 7 Then

$continue = 0

MsgBox(0, "Thanks", "Thanks")

EndIf

WEnd

Thanks guys.

[enaiman]

WORKSHEET!RANGE - that means, the linked table is referred by the sheet and row/column. It can't be other way around; for a graph, it's easy because you don't need its location.

What you want to do it would be close to impossible without keeping the tables consistent.

I mean, you need to know where these tables are:

Just an example:

- always the table starts at A1

- have 4 columns

- have 5 rows

If this is the case, then automating is easy.

What can you use:

- tables at fixed position (starting position) -> need to know number of rows and columns

- tables placed anywhere in the sheet (position unknown) -> need to know what the header columns text is and how many rows (pay attention to duplicate values)

- similar thing when you know the number of rows and the text in the first column

Without a system of finding the table in the spreadsheet, the script will fail.