Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006


OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal












Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com



  Wednesday, April 16, 2008 – Permalink –

Word Ranges

Pre-defined locations



When entries are made in a document, Word creates a Story Range to identify what part of the document is being used. These ranges can be used in macros to search for items , change text, or other actions.


This macro, for instance, changes the text in just the header of the first section:

Sub HeaderFooterObject()
Dim MyText As String
MyHeaderText = "This would be your text"
With ActiveDocument.Sections(1)
.Headers(wdHeaderFooterPrimary).Range.Text = MyHeaderText
End With
End Sub


When you use Edit>Replace in Word, it does a fine job of locating all occurrences of the target in the body of the document or in the header or footer.


Something fails, however, when you record the action and try to run it as a macro. To make it work, you must loop through the built in ranges of a Word document.


The exercise is interesting if only for the exposure to the built in ranges such as:

  • wdCommentsStory
  • wdEndnotesStory
  • wdEvenPagesFooterStory
  • wdEvenPagesHeaderStory
  • wdFirstPageFooterStory
  • wdFirstPageHeaderStory
  • wdFootnotesStory
  • wdMainTextStory
  • wdPrimaryFooterStory
  • wdPrimaryHeaderStory

    and
  • wdTextFrameStory.


See this article for more information:

Word.MVPS.org:
Find and replace with VBA


Also:

Microsoft KB
VBA macro examples to insert text into a document



See all Topics

Labels: , , ,


<Doug Klippert@ 7:14 AM

Comments: Post a Comment


  Thursday, February 21, 2008 – Permalink –

VBA, Named Arguments

An easier read


Use named arguments for cleaner VBA code.


Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

 MsgBox(prompt[, buttons] [, title] [, helpfile, context])


When you work the MsgBox function this way, the order of the arguments can't be changed.

Therefore, if you want to skip an optional argument that's between two arguments you're defining, you need to include a blank argument, such as:
MsgBox "Hello World!", , "My Message Box"


Named arguments allow you to create more descriptive code and define arguments in any order you wish. To use named arguments, simply type the argument name, followed by :=, and then the argument value.

For instance, the previous statement can be rewritten as:

MsgBox Title:="My Message Box", _
Prompt:="Hello World!"


(To find out a function's named arguments, select the function in your code and press [F1].)



See all Topics

Labels:


<Doug Klippert@ 7:59 AM

Comments: Post a Comment


  Sunday, December 16, 2007 – Permalink –

Office VBA Tricks

Video + Free code



Quick tips VBA Video


"Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."


  • Update Word Document Statistics in the Title Bar
  • Create Outlook Rules Programmatically
  • Delete Repeated Text Throughout a Word Document
  • Run Macros Based on the Value of One or More Excel Spreadsheet Cells
  • Disable Related Controls on a PowerPoint Slide After a User Clicks an Input Control
  • Display Reminder Information When a User Opens an Office Document
  • Synchronize an Access Main Form to a Subform and Vice Versa
  • Log Worksheet Changes to an XML File
  • Merge Body Text from Multiple Outlook E-mail Messages to a Word Document
  • Use the Office Assistant as an Alternative to Displaying and Retrieving User Input


Ten Tips for Office VBA Developers



See all Topics

Labels:


<Doug Klippert@ 5:05 AM

Comments: Post a Comment


  Saturday, November 03, 2007 – Permalink –

Automation - VBA - Help File

Office Wide


"Automation (formerly known as OLE Automation) is a feature of the Component Object Model (COM), an industry-standard technology that applications use to expose their objects, methods, and properties to development tools, macro languages, and other applications.

For example, a spreadsheet application might expose a worksheet, chart, cell, or range of cells--each as a different type of object. A word processor might expose objects such as an application, document, paragraph, bookmark, or sentence.


When an application supports Automation, the objects that the application exposes can be accessed through Visual Basic. You can use Visual Basic to manipulate the objects by invoking methods or by getting and setting properties of the objects."


Here's an example:


Inserting Data into a Microsoft Word Document

With Automation code, you can open a Microsoft Word document and move to a bookmark location in the document. The following example opens a Microsoft Word document and inserts text after a bookmark.

This example assumes that you have Microsoft Word on your computer, that you have an existing document called C:\My Documents\WordTest.doc, and that the document contains a pre-defined bookmark named City.


Sub FindBMark()

Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim wordRange As Word.Range

Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Open("C:\My Documents\Wordtest.doc")

wordApp.Visible = True

' Go to the bookmark named "City".
Set wordRange = wordDoc.Goto(What:=wdGoToBookmark, Name:="City")
wordRange.InsertAfter "Los Angeles"

' Print the document.
wordDoc.PrintOut Background:=False

' Save the modified document.
wordDoc.Save

' Quit Word without saving changes to the document.
wordApp.Quit SaveChanges:=wdDoNotSaveChanges

Set wordApp = Nothing

End Sub



Microsoft Support provides an entire Help file to assist you. It includes theory and examples.

The file is called XPAutomation.chm.

Download it and then double click on the file to run it. You could also set up a shortcut on the desk top, if it will be used frequently.


Microsoft Knowledge Base Article: 302460


This was aimed at Office 2002 but it can be used with later versions:

  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft PowerPoint
  • Microsoft Word




See all Topics

Labels:


<Doug Klippert@ 6:51 AM

Comments: Post a Comment


  Sunday, September 16, 2007 – Permalink –

Task Panes 2002-2003

VBA at your own risk



From the MVPS.org site:

Word's Task Panes VBA Reference
The Mother of All Task Pane articles
By Steve Hudson

"Task Panes display within a Work Pane's area. A Work Pane is created by the combination of two objects. These objects are shrouded in misery and thwart most attempts to play with them. The whole area is hidden away from the Kill Cursor invoked with CTRL+ALT+-, which changes to a hand when waved over a Work Pane.

Functions are hidden away from the macro recorder. To make it easier, if it is not in this reference, it is hidden. It is like when a spy is caught and the government disavows all knowledge of their actions.

The Task Panes are spies from Microsoft that are known to only a few objects, in these versions of Office anyway.

Warning
The author gleefully notes at this point that the human race has enough intelligence to get itself into cauldrons of boiling water that it cannot climb out of and that means you and me both!

If you like to be ultra-safe, stay away from this reference and wait for MS to hand over full functionality. You will end up crashing Word many times and you can really damage your user interface."


(Ctrl+Alt+-, can be used to remove an item from a menu. Type the shortcut and then click on a menu item)

Also see:
Task Pane Control



See all Topics

Labels:


<Doug Klippert@ 6:42 AM

Comments: Post a Comment


  Tuesday, June 19, 2007 – Permalink –

Indent Code

Realign a bunch


Indenting blocks of VBA code, such as statements within loops or If...Then statements, makes reading a procedure much easier.

You probably indent a code statement using the [Tab] key, and outdent by using [Shift][Tab].

However, you may not be aware that the [Tab] and [Shift][Tab] techniques also work when multiple code lines are selected.

The Visual Basic Editor also provides Indent and Outdent buttons on the Edit toolbar that allow you to easily reposition blocks of code.



See all Topics

Labels:


<Doug Klippert@ 6:28 AM

Comments: Post a Comment


  Saturday, March 31, 2007 – Permalink –

Comment Code

Edit toolbar



You'll many times want to change blocks of code to comments in VBA modules; temporarily convert a block of VBA code to comments so that it's ignored during a trial run. Inserting an apostrophe before each line of code is a bother. Office 2000+ simplifies this task by letting you convert a block of code to comments with a click of a button.

Open any module in the Visual Basic Editor (VBE), and then choose View>Toolbars and choose Edit from the menu bar to display the Edit toolbar.

Select the lines of code that you want to turn into comments. Then, click the Comment Block button on the Edit toolbar (it's the sixth button in from the RIGHT end of the toolbar).
Each line of the selected code is now preceded with an apostrophe.




To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.
This, of course, works in any application that uses the VBE.

Ross, suggested that two or three apostrophes (sometimes called inverted commas) be placed around existing comments. When the Comment Block is used, the original comments will not be removed.



See all Topics

Labels: ,


<Doug Klippert@ 7:02 AM

Comments: Post a Comment