Solved

excel active sheets

Posted on 2011-09-19
2
338 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

Industry Leaders: 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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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