?
Solved

excel active sheets

Posted on 2011-09-19
2
Medium Priority
?
339 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 600 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 1400 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
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.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 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