3

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.

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
user1713174
  • 307
  • 2
  • 7
  • `xlApp.Workbooks.Open ThisDocument.Path & "\filename.xls"` Your title doesn't match your question though... – Tim Williams Oct 01 '12 at 23:54
  • 1
    You're right -- my bad. To be entirely honest, the title is actually closer to what I want than what I actually wrote :) That is, I have a spreadsheet that's open and I want to grab the stuff on it. Is there a way to get Word to activate whatever spreadsheet is open? Thanks very much for your reply! – user1713174 Oct 02 '12 at 00:14
  • 1
    You should be able to use `GetObject()` to get a reference to a running instance of Excel (assuming there's only one instance open). Once you have that, you can loop through the `Workbooks` collection and find the one you want to work with. – Tim Williams Oct 02 '12 at 04:36
  • What happens with multiple instances? – Zev Spitz Oct 17 '12 at 09:42

1 Answers1

1

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.

ChrisM
  • 1,576
  • 6
  • 18
  • 29
KFleschner
  • 499
  • 3
  • 13
  • In case you haven't used it before, to Add a Reference, go to Tools -> References in the Menu. Look for Microsoft Excel 15.0 Object Library, or another number if your Excel isn't version 15. – Gary Mar 12 '19 at 13:21