Excel Cell Values to Work

[RayFrye]

Hi, I just need some guidance. I have an Excel Workbook that hs multiple Worksheets that has hundreds of Cell Values that need to find their way to a specific location in a word document. I have plenty of experience with Excel VBA but zero with Word VBA. My thought is to use Excel VBA to define a name for each Excel Cell Value and then thru some magic that has yet to be defined cram those named cells into a work document in the correct place. I was thinking of the word document having some text like 'blah blah <TotalRevenue>blah blah' and then VBA would take the Excel Cell Named TotalRevenue and cram the results into the Word document.

Any advice would be greatly appreciated.

Thanks in advance for looking...

 

[Trevor G]

Word allows you to record Macros same as Excel. Word also uses Bookmarks and you can assign each bookmark in Excel VBA to collect the cell content from each cell. Final option is that you could use Mail Merge in Word and link the spreadsheet that way.

 

[RayFrye]

Thanks, I will add this notion to my tool bag.

 

[RayFrye]

This is the solution I settled in on. Works great and is fast.
I loop thru the Excel Workbook NamedCells and call this routine.

'This code assumes an Excel Workbook has a number of NamedCells.
'There is corresponding text in a Word document in the
'    format <NamedCell>.
'There can be any number of these 'related collections'.
'On entering the 'WordDocumentPath' is set.
'  The 'ExcelDocumentPath' path is set.
'I screwed around with a number of solutions, each
'  worse than the previous until I settled in on this code.

Sub ExcelToWordByNamedCells()
    
  'This VBA code in this Word Document and this Document is open
    Set WordDoc = GetObject(WordDocumentPath)
    Set ExcelApp = CreateObject("Excel.Application")
    Set ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelDocumentPath)

  'Format example: <Plans>
    PasteFindCharacter = DataToGetAndPaste
  'Remove < and >
    ExcelNamedCell = replace(replace(PasteFindCharacter, "<", ""), ">", "")
    
  'Cellvalue Named ex: Plans
    DataToPaste = FormatCurrency(Range(ExcelNamedCell).Value, 0)
    
    With Selection.Find
        .Text = PasteFindCharacter
        .Replacement.Text = DataToPaste
        .Execute replace:=wdReplaceAll, _
        Forward:=True, Wrap:=wdFindContinue
    End With

End Sub