Export Data from Excel into Word

[matthoward]

Hi All,

I want to pull data from a excel spreadsheet and put it into a word document. At the moment, I have created a Label and gone into its properties and named it 'yrTotal'. I have also put a Command Button into the word document and gone into VBA Editor for it. I have made sure is Microsoft Excel 14.0 Object Library selected. I have been given the following example code from a website, but it is not tailored to my files:

Private Sub CommandButton1_Click()
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("c:\temp\expenses.xls")

ThisDocument.yrTotal.Caption = exWb.Sheets("Sheet1").Cells(12, 2)

exWb.Close

Set exWb = Nothing

End Sub

However I want to pull data from the Spreadsheet called 'reference' from 'Sheet 1' and cell 'A1', and to get to that file from 'File Explorer' you need to follow the path:

This PC, Personal Area (H:), 2017, Forecasting SS, reference

Any help would be much appreciated in providing the coding

Thanks

Matt

 

[Macropod]

First, replace:
"c:\temp\expenses.xls"
with your own system's filepath & filename. You should be able to get these details via Windows/File Explorer. The path will look something like the one you're replacing. The description you've posted for that is ambiguous.

Second, replace:
.Cells(12, 2)
with:
.Range("A1")

Third, instead of using:
ThisDocument.yrTotal.Caption
simply insert a bookmark in the document wherever you want the content to appear and use:
ActiveDocument.Bookmarks("BookMarkName").Range.Text
where "BookMarkName" is the bookmark's name.