Import range of cells in excel to word

[brentfraser]

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:

dataInExcel = workBook.Worksheets("Sheet1").Range("A1").Formula

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:

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

Is what I am trying to do even possible? I would be happy to reference a named range of cells in excel too. That seems to make more sense than just rows/columns.....

After doing some reading, I am thinking I have to place the range in an array and then reference that array into the content control I have created. This way all six rows of information in the named range in the excel spreadsheet will be imported into the content control in Word.

Is this the right path to be going down?

Public strExcelFile As String, workbook As String 
Public Sub GetFilePath() 
    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 
    If fd.Show Then 
        strExcelFile = fd.SelectedItems(1) 
    End If 
End Sub 
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 
   [COLOR=#A40000] Dim Arr() As Variant 
    Arr = workbook.Worksheets("Sheet1").Range("A1:A6").Value 
    Dim R As Long 
    Dim C As Long 
    For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
    For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
    Debug.Print Arr(R, C) 
    Next C 
    Next R 
    Dim oCC_testing As ContentControl 
    Set oCC_testing = ActiveDocument.SelectContentControlsByTitle("testing").item(1) 
    oCC_testing.Range.Text = Arr[/color]     Dim oCC_box1 As ContentControl 
    Set oCC_box1 = ActiveDocument.SelectContentControlsByTitle("box1").item(1) 
    oCC_box1.Range.Text = dataInExcel 
    workbook.Close False 
    Set workbook = Nothing 
    Application.ScreenUpdating = True 
End Sub

I assume this is possible but having a few other sets of eyes on this might get me past this.

Thanks all who have taken the time to look at this.

Brent

 

[combo]

If you need to pick formulas in excel range, you need an array, the simplest is a variant array:
[tt]Dim dataInExcel As Variant
dataInExcel = workBook.Worksheets("Sheet1").Range("A1").Formula[/tt]


combo

 

[brentfraser]

Hi Combo,

No I don't need the formulas, I just want the text from the cells (I have 6 in question; A1:A6).

I still think I need an array but I am not sure how to do that at this point.

Still working on it. Thanks for the tip.

B

 

[SkipVought]

hi,

you cannot assign an array to a STRING variable.

You could do something like this

dim r as range, sTextForDoc as string

for each r in workBook.Worksheets("Sheet1").Range("A1:A6")
   sTextForDoc = sTextForDoc & r.value & " "
next

padding with spaces as required.

Then just assign the sTextForDoc to where you need in the Word document.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[brentfraser]

Thanks Skip,

It seems logical to what you are suggesting. I tried it in the word document and I get an error stating "Compile error: Method or data member not found" and it is highlighting .Value in:

dim r as range, sTextForDoc as string

for each r in workBook.Worksheets("Sheet1").Range("A1:A6")
   sTextForDoc = sTextForDoc & r[highlight #FCE94F].value[/highlight] & " "
next

I will continue to play around with it as this seems to be going in the right direction. Thanks for the pointers.

Brent

 

[PHV]

Ddim r As Excel.Range

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[brentfraser]

Hey Skip and PHV, thank you so much for the tips.... it's working and getting the information into the content control.

Is it at all possible to have each line come in as a separate line and not concatenated in a single line?

All of you have made my day so far!

Brent

 

[SkipVought]

dim r as range, sTextForDoc as string

for each r in workBook.Worksheets("Sheet1").Range("A1:A6")
   sTextForDoc = sTextForDoc & r.value & [highlight][b]vbLF[/b][/highlight]
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[brentfraser]

Thanks Skip, et. al.

It works wonderfully! Very excited. You have all made my Tuesday afternoon!!!

Brent