CODEWITHSUNDEEP
excelvba
user181796
user181796

Reputation: 195

Open excel from Word and perform action

I want to create a marco which enables me to add something in an excel sheet while working in Word. I managed to get something working which opens an excel file from word:

  Dim excelApp As Excel.Application
  Dim openExcel As Workbook
  Dim var1 As Integer


  Set excelApp = New Excel.Application
  Set openExcel = excelApp.Workbooks.Open("C:\Documents and Settings\aa471714\Desktop\Book1.xls")
  excelApp.Visible = True

But now I want to add the code which should happen in excel right after. But when I add the relevant code:

Sheets("Sheet2").Select
Range("A4").Select

This does not seem to work. Am I overlooking something?

Dear regards,

Marc

Upvotes: 2

Views: 6554

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Keep in mind that in macro created in Word default application is Word Application all the time. Therefore each time you want to make any operation in Excel you need to state it by adding full references to Excel Application (or other Excel object as presented below).

Keep also in mind that you will need to add extended object hierarchy in such situation.

Back to your code- adding this kind of object references should solve the problem: (incl. some additional re-editions)

'comments referring to Object hierarchy
openExcel.Sheets("Sheet2").Select   'OK because sheet is an object below Workbook
excelApp.Range("A4").Select    'NEW, Range is object below Application or...
openExcel.Sheets("Sheet2").Range("A4").Select   'IMPROVED, Range is object below sheet
'general- Range.Select works only for activesheet!!

Object openExcel keeps reference to Excel application which is represented by excelApp.

Upvotes: 3

Related Questions