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



  Monday, February 01, 2010 – Permalink –

Tips for Word and Excel

Also some Windows hints


This site has useful information about:

  • Word
  • Word VBA
  • Excel
  • Excel VBA

    and
  • Windows
Tribbs.co.uk




See all Topics

Labels: , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Sunday, January 17, 2010 – Permalink –

Merge Word to PDF

Not just MailMerge anymore

Sure, you can create a merged letter and have 30 pages of individualized information.
What now? How do you create separately named documents to, maybe, send as attachments?
Another task might be to convert those 30 docs to PDF. Oh, the hours wasted!

Graham Mayor, a retired newsgroup junkie, offers a solution.
It's a macro that will convert the merged output as individually named doc(x), and/or PDF files.
I've used it, and now wonder what I can do with the time I'm saving.






GMayor.com

Merge with Attachments



See all Topics

Labels: , , , ,


<Doug Klippert@ 3:10 AM

Comments: Post a Comment


  Thursday, November 05, 2009 – Permalink –

Change Code to Comments

Fast solution


When you're testing procedures, you can temporarily convert a block of VBA code to comments that will be ignored during a trial run.

Doing so manually by inserting an apostrophe before each line of code can be a real chore.

To simplify this task,
  1. Open any module in the Visual Basic Editor (VBE)
  2. Choose View >Toolbars>Edit from the menu bar to display the Edit toolbar.
  3. Select the lines of code that you want to turn into comments.
  4. 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.




See all Topics

Labels:


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  Saturday, September 12, 2009 – Permalink –

Declaring Multiple Variables

Declare each one


When setting up a macro in VBA, if you want to declare multiple variables in one line of code, be sure to specify the type for each variable, even if the variables are the same type. Avoid code like the following:

Dim strFName, strLName, strMI As String

In such a case, only the last variable, strMI, is actually declared as a String type. The first two variables are designated by default as Variant data types.

To correctly declare the three variables, you would use the statement:

Dim strFName As String, strLName As String, strMI As String




See all Topics

Labels:


<Doug Klippert@ 3:56 AM

Comments: Post a Comment


  Thursday, August 06, 2009 – Permalink –

Backup Your Word

Stand behind your documents


Backup/restore or transfer AutoCorrect, AutoText, Macros, and other critical files from one version of Word and Windows to another


  1. Normal.dot:
    Stores formatted AutoCorrect entries, AutoText entries, keyboard shortcuts, menu customizations, custom toolbars, styles, macros.

  2. acl file:
    Stores unformatted AutoCorrect entries (the majority of your AutoCorrect entries).

  3. custom.dic:
    Words you add during spellcheck.

  4. Templates you create (.dot files).
From ProductivityTalk.com:
MS Word's critical files

Word comes with a supplemental macros template that includes the AutoCorrect Utility. The utility is used to backup and restore entries. If you only do the backup portion, it will create a regular Word document that you can print.. In Word 2002/2003: it's part of the support.dot (included on Office installation CD)

AutoCorrect Utility

Also: Export AutoCorrect




See all Topics

Labels: ,


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  Thursday, July 30, 2009 – Permalink –

Protected Spell Checker

Correct the protected


Unfortunately, Word's protection feature disables a huge number of important functions, even if you only protect a single section of a document.

Besides the spellchecker, many other items on the View, Insert, Format, Tools and Table menus are disabled, as well as most items on the Drawing, Database, Visual Basic and Picture toolbars.

This MS Word MVP FAQ Site article lays out the steps needed to

Enable the spellchecker in a protected document.

One of the important elements is "NoReset=True". The act of removing and then re-protecting a document will reset the data without this line.



In Office 2007 Protection is found on the Review tab:





See all Topics

Labels: , , ,


<Doug Klippert@ 3:03 AM

Comments: Post a Comment


  Wednesday, June 17, 2009 – Permalink –

VBA Variable Problems

Explicit protection


It's good practice to always use the Option Explicit statement in the beginning of your code modules to ensure that all variables are unambiguously declared in your procedures.

With this process in place, you'll receive a "Variable not defined" error if you try to execute code containing undeclared variables. Without this statement, it's possible to mistype variable names, which would be interpreted as new Variant type variables.

This could severely impact the results of your code, and you might not ever know it. If you do find a problem, tracking down where the error is can be a chore.

Although you can manually type the statement into your modules, changing a setting in Access can ensure that the statement is always added to new modules.

  1. Open a module (start the VBA Editor)

  2. Choose Tools>Options from the menu bar

  3. On the Editor tab of the Options dialog box, select the Require Variable Declaration check box in the Code Settings panel

  4. Finally, click OK





See all Topics

Labels:


<Doug Klippert@ 3:36 AM

Comments: Post a Comment


  Tuesday, March 31, 2009 – Permalink –

Add Captions Programatically

When the project seems overwhelming


Faced with a repetitive task, I often have to make the decision, do I do it by hand again and again? Or do I write or find a macro to do the heavy lifting?
"Have you ever had a document with ten, twenty, or even 100 images, and then as an afterthought decided you wanted to add a caption to each of those images? This is an easy task through the user interface (UI). All you have to do is select each image, one at a time, and choose Insert, then Reference, then Caption. Unfortunately doing this through an image-heavy document is a boring chore and takes up a lot of time.

Fortunately, the Word object model lets you to apply a caption to a selected object. An add-in that does this for you is only a small chunk of code away. The sample add-in accompanying this article demonstrates how to do this. It also demonstrates many other details about working with shapes and images in Word programmatically that are useful to know about."

Working with Word 2003 Images Programmatically




See all Topics

Labels: , ,


<Doug Klippert@ 3:18 AM

Comments: Post a Comment


  Wednesday, February 04, 2009 – Permalink –

Automate Word Tables

VBA examples and instructions


The Microsoft Developer's Network has a pretty comprehensive article on programmatically working with data and Word tables.

"You can look at the world as split into applications that store data (databases) and applications that present information, such as Microsoft Office Word 2003 and Microsoft Office PowerPoint 2003. Increasingly, the end user demands to display database content in documents and presentations.

While Word does provide some tools for displaying tables from databases in its documents, these are somewhat rudimentary, they require a basic understanding of how the database is built, and using them involves a number of steps. In addition, there may also be security and access issues involved, requiring additional layers of protection.

The developer is therefore increasingly confronted with the task of transferring data into Word, whether in the form of tables, or as part of the document text. This article considers some of the major aspects of using the Word object model to work with tables."


  • Introduction to Automating Tables
  • Creating Tables Programmatically in Word
  • Populating Word Tables with Data
  • Adding Linking in Word Tables Programmatically
  • Extracting Data from Word Tables Programmatically
A downloadable document is also available for those of us still addicted to paper. Automating Word Tables for Data Insertion and Extraction See all Topics

Labels: ,


<Doug Klippert@ 3:07 AM

Comments: Post a Comment


  Wednesday, December 03, 2008 – Permalink –

Start Menu List

List programs




Here is an interesting macro, with instructions on how to install it and a template that can be downloaded.

I recommend using the template to avoid the problems that can occur when pasting code.

A Macro to List All Programs in the Windows Start Menu
by Greg Chapman, MVP




"TechTrax is a free, monthly Ezine (online magazine) published the beginning of each month. TechTrax is geared toward anyone who wants to learn more about using computers, with a highlight on Microsoft technologies. TechTrax also makes a point to focus on issues of accessibility. The range of overall subjects and user level articles here in TechTrax are far reaching to cover a wide audience. From newbie to black belt articles."




See all Topics

Labels: ,


<Doug Klippert@ 6:56 AM

Comments: Post a Comment


  Thursday, August 14, 2008 – Permalink –

Sequentially Number Documents

Budget0056.doc


Here's an example of how to use an external text file to record incremental numbering.

"Sometimes, when working on a project, you may want to save your documents in sequential order (for example, "0001", "0002", "0003", and so on).

If you wanted to do this manually, you would need to sort through your working directory for the latest file number before you could assign the next number to a new file.

Using this fairly straightforward Word macro, you can make creating sequenced files as easy as pressing a button."


LogicalExpressions.com:

Save sequential number docs
(Kevin Christy)


Also see:

Autonumber Invoices



See all Topics

Labels: , , , ,


<Doug Klippert@ 4:00 AM

Comments: Post a Comment


  Friday, July 18, 2008 – Permalink –

Signing Macros

Security levels


There are three levels of Macro security:

High:
A computer user can open without a prompt a digitally signed project from a trusted publisher. Otherwise, the application blocks opening signed projects from untrusted publishers as well as unsigned projects.
Medium:
A computer user can open without a prompt a digitally signed project from a trusted publisher. In addition, you can also designate the publisher of a signed project as trusted so their projects will open without a prompt in the future. Unsigned projects are always prompted with a reminder that the file may contain potentially harmful code, but users can elect to open them anyway.
Low:
A computer user can open an unsigned project without a prompt. When users make a Low security setting, they're reminded that they aren't protected from potentially unsafe macros.
Securing Access Databases
"If you've used Access 2003, you've probably seen several security warning messages - Access 2003 cares about your security. An important part of Access 2003 security is digitally signing your code. As Rick Dobson shows, you can do it, but preparing for digital signing is critical.

A digital signature acts like shrink-wrap on your project: Clients know that they're getting a copy directly from you that no one else modified. Clients will also know that they're working with "your" code and not any version of it modified by a third party. As computing moves forward into a "security conscious" era, learning how to acquire and use a digital certificate is also important for interfacing with organizations that adopt policies of only running digitally signed Access 2003 projects: Your users may refuse to accept software from you that isn't shrink-wrapped."

Also:
Signing Access 2003 Projects

Other links:

How to make sure that your Office document has a valid digital signature in 2007 Office products and in Office 2003

Also:
HAL-PC MS Office & Excel SIG in Houston, Texas:
Digital Certificates and Trusted Sources for running Excel Macros under High Macro Security



See all Topics

Labels: ,


<Doug Klippert@ 5:00 AM

Comments: Post a Comment


  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