MS Word 中的链接表 [英] Linked Table in MS Word
问题描述
我想使用 VBA 以编程方式刷新 Word 模板报告中的 Excel 表格.这些表格被写入到来自 Matlab 的模板 Excel 文件中的多个工作表中.文件结构如下:
I would like to refresh Excel tables in a Word template report programmatically using VBA. The tables are written to several sheets in a template Excel file from Matlab. The file structure will look like this:
代码必须检查文件夹结构以查看它是否将 Excel 文件从最新文件夹中拉出.如果是,它只会刷新所有单元格.如果没有,则必须删除所有表格并从与先前表格相同的表格中插入新表格.我不确定下面星号之间的代码.任何帮助将不胜感激.
The code would have to check the folder structure to see if it is pulling the Excel file out of the newest folder. If it was, it would just refresh all of the cells. If not, it would have to delete all of the tables and insert new ones from the same sheets as the previous ones were pulled. I am unsure of the code between the asterisks below. Any help would be much appreciated.
Sub LinkToCurrentTableFolder()
'Get current folder by date
Dim clientTablesPath As Variant
filePath = ActiveDocument.Path & "ClientTables"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(filePath)
Dim currentFolder As Variant: currentFolder = ""
For Each sf In fld.SUBFOLDERS
'Look at name and get current date
If currentFolder = "" Then
currentFolder = sf.Path
ElseIf sf.Path > currentFolder Then
currentFolder = sf.Path
End If
Next
'***
'Debug: display current Excel folder path
'MsgBox (currentFolder)
If currentPath = currentFolder Then
'Loop through all tables in document and refresh
'If path is not current delete current table
Dim tbTemp As Table
Dim cellTemp As Cell
For Each tbTemp In ActiveDocument.Tables
For Each cellTemp In tbTemp.Range.Cells
cellTemp.Range.Fields.Update
Next
Next
Else
'Locate same file name in new folder
shpName = .LinkFormat.SourceName
NewPath = currentFolder & "" & shpName
'Delete existing table (???) Not sure
.Delete
'Create new table (???) Not sure - must be from same location and same size as previous one
Selection.Table.AddOLEObject ClassType:=cType, FileName:=NewPath, LinkToFile:=True, DisplayAsIcon:=False
End If
'***
End Sub
编辑 - 复制和粘贴如下所示:
EDIT - The copy and pasting is done as shown below:
推荐答案
我找到了答案 此处 - 此代码要求提供新 Excel 的位置文件并更新 Excel 链接表的所有域代码.
I found my answer here - this code asks for the location of the new Excel file and updates all of the field codes of the Excel linked tables.
该链接中的代码如下.
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 "Source data has been successfully imported."
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
这篇关于MS Word 中的链接表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!