GenuineGin
02-06-2015, 05:38 AM
Hello again, and thanks for everyone's help so far!
I have question regarding importing data.
I have created a macro enabled word template in Word 2013 that allows me to import data from an excel spreadsheet using paste links and the following code:
Private Sub cmdBatRemoteID_Click()
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
'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 only 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
Dim BatRng As Word.Range
Set BatRng = ActiveDocument.Bookmarks("Bat").Range
'update fields
fieldCount = BatRng.Fields.Count
For x = 1 To fieldCount
'Debug.Print x
'Debug.Print ActiveDocument.Fields(x).Type
If BatRng.Fields(x).Type = 56 Then 'only update Excel links. Type 56 is an excel link
BatRng.Fields(x).LinkFormat.SourceFullName = newFile
'DoEvents
End If
Next x
MsgBox "Source data has been successfully imported."
Me.Hide
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
I initially create the paste links using a blank, default document, then use the code above choose the new document (which has exactly the same layout, cell for cell) and make this the new source of the paste links.
I have been trying to avoid copy/pasting code I don't understand into my template but in this case I really wanted this function and the code is a bit above my level!
The situation now is that the source data is being provided in a word table instead of an excel one.
So my question is: Is there a way to convert the above code so it can be applied to a word table in the same way it is being applied to the excel spreadsheet currently. If not, how can I automatically import data from a word table in one document, into fields (e.g. docvariables) in another?
Many thanks.
Gin
I have question regarding importing data.
I have created a macro enabled word template in Word 2013 that allows me to import data from an excel spreadsheet using paste links and the following code:
Private Sub cmdBatRemoteID_Click()
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
'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 only 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
Dim BatRng As Word.Range
Set BatRng = ActiveDocument.Bookmarks("Bat").Range
'update fields
fieldCount = BatRng.Fields.Count
For x = 1 To fieldCount
'Debug.Print x
'Debug.Print ActiveDocument.Fields(x).Type
If BatRng.Fields(x).Type = 56 Then 'only update Excel links. Type 56 is an excel link
BatRng.Fields(x).LinkFormat.SourceFullName = newFile
'DoEvents
End If
Next x
MsgBox "Source data has been successfully imported."
Me.Hide
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
I initially create the paste links using a blank, default document, then use the code above choose the new document (which has exactly the same layout, cell for cell) and make this the new source of the paste links.
I have been trying to avoid copy/pasting code I don't understand into my template but in this case I really wanted this function and the code is a bit above my level!
The situation now is that the source data is being provided in a word table instead of an excel one.
So my question is: Is there a way to convert the above code so it can be applied to a word table in the same way it is being applied to the excel spreadsheet currently. If not, how can I automatically import data from a word table in one document, into fields (e.g. docvariables) in another?
Many thanks.
Gin