I am trying to import a range of cells from Excel to a Word content control. Currently I can import a single cell using the following:
[vba]dataInExcel = workBook.Worksheets("Sheet1").Range("A1").Formula[/vba]
As soon as I change "A1" to "A1:A6", it stops working. Is there another convention that I should be using?
The entire code I have is posted below:
[vba]Public strExcelFile As String
Public Function GetFilePath() As String
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select the file"
fd.Filters.Add "Excel Files", "*.xls; *.xlsx", 1
'fd.Filters.Add "All Files", "*.*"
If fd.Show Then
strExcelFile = fd.SelectedItems(1)
End If
End Function
Public Sub importExcelData()
Dim workBook As workBook
Dim dataInExcel As String
Application.ScreenUpdating = False
GetFilePath
MsgBox strExcelFile
Set workBook = Workbooks.Open(strExcelFile, True, True)
dataInExcel = workBook.Worksheets("Sheet1").Range("A1").Formula
Dim oCC As ContentControl
Set oCC = ActiveDocument.SelectContentControlsByTitle("box1").Item(1)
oCC.Range.Text = dataInExcel
workBook.Close False
Set workBook = Nothing
Application.ScreenUpdating = True
End Sub[/vba]
Is what I am trying to do even possible?
Thanks all in advance.
B.