- 帖子
- 41
- 主題
- 7
- 精華
- 0
- 積分
- 52
- 點名
- 0
- 作業系統
- Windows10
- 軟體版本
- 2019
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2017-11-28
- 最後登錄
- 2025-3-4

|
[分享] VBA-一次更新你WORD裡面,所有連動EXCEL儲存格連結!
小弟做報告書的習慣就偏好連動EXCEL裡面特定儲存格。
例如
{ LINK Excel.SheetMacroEnabled.12 "D:\\案件\\開發案\\水理計算.xlsm" 初期設定!R4C4 \t }
礙於裡面有存在很多連結到別的WORD書籤,導致最早做法都是在"檔案"->"資訊"->"編輯檔案連結" 慢慢改
所以上網找了以下的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
複製代碼 For x = 1 To fieldCount
在這裡我是會改成 1 To 25,不確定這什麼意思....哈
起初我文件內有100多頁,可能每10頁就會有個EXCEL連結,導致再刷新時...會等很久...
所以我後來的作法就是在第一頁設定表格並加入書籤,裡面都是EXCEL連結,假設裡面有25個是連結excel的....
好像就真的會讀取這25個....就會跑比較快
另外我有而外設定,每次開啟WORD 就會執行這代碼- Sub Document_Open()
- changeSource
- End Sub
複製代碼 另外再分享一個好,如果你想要參考別的WORD檔案的內容,例如A檔案連結B檔案,可以先在B檔案裡面,你想要的文字設定書籤
例如 麻辣家族討論版
此時你可以在A檔案:
標題:麻辣家族討論版
{ INCLUDETEXT " {FILENAME \p}\\..\\"B檔案" "麻辣家族討論版" \* MERGEFORMAT }
{}顏色粉色這個,一定要用ctrl+p哦!!!
這樣不管你放在哪裡,都不會出現錯誤嚕,只是這兩個檔案一定要放在旁哦 |
|