Solved

excel active sheets

Posted on 2011-09-19
2
337 Views
Last Modified: 2012-06-27
I have a worksheet as an object in a Word document.

There are three sheets.

When the worksheet is embedded in an object it seems to need to know which sheet it is dealing with at any time when you refer to a range of cells. So I need to refer to Sheet1. Range etc.

Rather than do it this way is it possible to do it with say worksheet.active sheet. range etc.
This would save me a lot of coding as the code is pretty much the same across all three sheets.

What would be the correct wording for active sheet etc.


0
Comment
Question by:topUKlawyer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 150 total points
ID: 36562597
It's not clear what difficulty you are having, but it might help you to know that the ActiveSheet is a property of the Excel Application and of the ActiveWorkbook, which itself is a property of the Excel Application.

Perhaps you should show us the relevant bits of your code.
0
 
LVL 5

Accepted Solution

by:
slycoder earned 350 total points
ID: 36563086
If you are coding in excel:

    Dim myCell As Range
    For Each myCell In ActiveWorkbook.ActiveSheet.Range("a1:d5")
        myCell.Value = "x"
    Next myCell


will populate cells a1 - d5 with an "x"

if coding in Word:

1) I used the example above and changed the values to "y" and inserted the worksheet into a word document.


2) you need to add the "Microsoft Excel 14.0 Object Library" Reference with Tools - References and use this code:


Public Sub ChangeValues()
    ' Source help:  http://vbadud.blogspot.com/
    Dim objWorkBook As Excel.Workbook
    Dim objInlineShape As InlineShape
    Dim myCell As Excel.Range
   
    On Error Resume Next
    ' Error occurred because one of my shapes is clipart
   
    For Each objInlineShape In ActiveDocument.InlineShapes
        If InStr(1, objInlineShape.OLEFormat.ProgID, "Excel") Then
            objInlineShape.OLEFormat.Activate
            Set objWorkBook = objInlineShape.OLEFormat.Object
           
            For Each myCell In objWorkBook.Sheets(1).Range("a1:d5")
                myCell.Value = "x"
            Next myCell
           
        End If
    Next objInlineShape
   
End Sub


Hope this helps.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question