VBA-一次更新你WORD裡面，所有連動EXCEL儲存格連結!
小弟做報告書的習慣就偏好連動EXCEL裡面特定儲存格。例如
[color=Blue]{ LINK Excel.SheetMacroEnabled.12 "D:\\案件\\開發案\\水理計算.xlsm" 初期設定!R4C4 \t }[/color]
礙於裡面有存在很多連結到別的WORD書籤，導致最早做法都是在[color=Red]"檔案"->"資訊"->"編輯檔案連結" [/color]慢慢改
所以上網找了以下的CODE[code]Public Sub changeSource()
Dim dlgSelectFile As FileDialog 'FileDialog object '
Dim thisField As Field
Dim selectedFile As Variant
'must be Variant to contain filepath of selected item
Dim newFile As Variant
Dim fieldCount As Integer '
Dim x As Long
On Error GoTo LinkError
'create FileDialog object as File Picker dialog box
Set dlgSelectFile = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
With dlgSelectFile
.Filters.Clear 'clear filters
.Filters.Add "Microsoft Excel Files", "*.xls, *.xlsb, *.xlsm, *.xlsx" 'filter for o nly Excel files
'use Show method to display File Picker dialog box and return user's action
If .Show = -1 Then
'step through each string in the FileDialogSelectedItems collection
For Each selectedFile In .SelectedItems
newFile = selectedFile 'gets new filepath
Next selectedFile
Else 'user clicked cancel
Exit Sub
End If
End With
Set dlgSelectFile = Nothing
'update fields
With ActiveDocument
fieldCount = .Fields.Count
For x = 1 To fieldCount
With .Fields(x)
'Debug.Print x '
Debug.Print .Type
If .Type = 56 Then
'only update Excel links. Type 56 is an excel link
.LinkFormat.SourceFullName = newFile '
.Update
.LinkFormat.AutoUpdate = False
DoEvents
End If
End With
Next x
End With
MsgBox "！更新完成！"
Exit Sub
LinkError:
Select Case Err.Number
Case 5391 'could not find associated Range Name
MsgBox "Could not find the associated Excel Range Name " & _
"for one or more links in this document. " & _
"Please be sure that you have selected a valid " & _
"Quote Submission input file.", vbCritical
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Select
End Sub
[/code][color=Red]For x = 1 To fieldCount[/color]
在這裡我是會改成 1 To 25，不確定這什麼意思....哈
起初我文件內有100多頁，可能每10頁就會有個EXCEL連結，導致再刷新時...會等很久...
所以我後來的作法就是在第一頁設定表格並加入書籤，裡面都是EXCEL連結，假設裡面有25個是連結excel的....
好像就真的會讀取這25個....就會跑比較快
另外我有而外設定，每次開啟WORD 就會執行這代碼[code]Sub Document_Open()
changeSource
End Sub[/code]另外再分享一個好，如果你想要參考別的WORD檔案的內容，例如A檔案連結B檔案，可以先在B檔案裡面，你想要的文字設定書籤
例如 [color=Red]麻辣家族討論版[/color]
此時你可以在A檔案：
標題：[color=Red]麻辣家族討論版[/color]
[color=Magenta]{[/color] INCLUDETEXT " [color=Magenta]{[/color]FILENAME \p[color=Magenta]}[/color]\\..\\"B檔案" "麻辣家族討論版" \* MERGEFORMAT [color=Magenta]}[/color]
[color=Magenta]{}[/color]顏色粉色這個，一定要用ctrl+p哦!!!
這樣不管你放在哪裡，都不會出現錯誤嚕，只是這兩個檔案一定要放在旁哦 請問可以有附檔嗎 [attach]33926[/attach]
你在試用看看嚕
頁:
[1]