Solved

excel active sheets

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

773 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