CODEWITHSUNDEEP
excelvbams-word
user1713174
user1713174

Reputation: 377

How to get reference to an open Excel spreadsheet from Word?

I have some VBA code that copies stuff from Excel and pastes it into Word. The problem I'm having is how to open the spreadsheet. I can open it using an absolute path reference by

Workbooks.Open "C:\path\filename.xls"

I would prefer to reference the spreadsheet using a relative path reference. I was able to find code for relative path references from an Excel workbook to another one but it doesn't seem to work if you're doing it from Word.

Upvotes: 3

Views: 8157

Answers (1)

KFleschner
KFleschner

Reputation: 499

Add a reference to Excel object library, then create an object in code and use that object to control an instance of Excel. Just make sure to avoid things like ActiveWorkbook, just in case.

After adding the reference:

Sub DoStuffWithExcelInWord()
   Dim xl As Excel.Application
   Dim wkbk As Excel.Workbook
   Dim wk As Excel.Worksheet
   Set xl = CreateObject("Excel.Application")
   Set wkbk = xl.Workbooks.Open("C:\test.csv")
   Set wk = wkbk.Sheets(1)
   Debug.Print wk.Cells(1, 1).Value
   xl.Quit
   Set wk = Nothing
   Set wkbk = Nothing
   Set xl = Nothing
End Sub

You can create something very similar using Excel to automate Word too, if that's more of what you're looking for.

Upvotes: 1

Related Questions