1) I have been told Word constants (wdOpenFormatAuto and wdsendtonewdocument) are not valid in Excel, so I need to declare them or use literals. What does this mean, and how is it accomplished.
Once you have created an instance of Word via Excel everything concerning Word is preceded with the object variable name. We write code as if we are actually using Word, and use Word macro syntax.
It is often easier to start by recording macros in Word and include them in the Excel code - when all they need is the additional reference.
Make sure in Excel Editor you got to Tools/References and include the 'Microsoft Word xx Object Library'.
Word macros are not quite so easy to record as Excel because the mouse doesn't always record things. The best method is to use the keyboard for *everything* you want to record in Word. I still begin most projects by recording macros of various stages. Not only does it cut down typing errors, but it gets over the "blank sheet" stage.
Putting the line
x = wdOpenFormatAuto
into a module pressing F8 to start the macro, hover mouse pointer over the variable, shows me its value is zero.
These are system variable names exclusive to VBA, as compared with our personal variable names we make up.
You could therefore equally as well use 0 (zero) instead. Perhaps you can begin to see the necessity of using variable names meaningful (to you) instead of numbers or whatever. With the mentioned reference to Word in Excel (or any other Office application code module) it will understand this variable name too.
'wdsendtonewdocument' does not appear in Word 2000. It looks like this is from a later version. It also looks like the poster of the code did not really know what he/she was doing. I could be wrong. If it had been available it would have been capitalised/formatted correctly like the other one. Unfortunately a lot of code on the web is useless because users just copy/paste someone else's without testing it first. The example you have picked up could be such. This is an obvious error ...
Code:
' these 2 lines mean exactly the same in your code
ObjWord.Visible = True
Odoc.Application.Visible = True
So do not use "Reserved words" for your variable names in code - ie. names that VBA already uses for something else eg. Sheet, Document, Row, Column .......
We often get away with it to start with, but bigger projects fail "inexplicably" at a later stage. We do not tend to look for bugs in code that is already "working".
Need to get into good habits with formatting code etc. Not only does it get faster with practice, but you will come back and understand it 12 months later if you (or someone else) need to make changes. You only need to see some of the garbage that appears here to see what I mean. What you have posted is not bad. Needs correct indentation.
======================================================================================================
2) The main mail merge document, in this case is SO-DR_HPL.doc, is one of 15 different reports each with their own unique SQL query. Rather than repeating this same code 15 different times with the only difference being the path, is their a means I can substitute the file name with a variable? The variable would equal the value of cell F21 in my active worksheet.
Please see code below. A subroutine would be good for this. you will see why.
3) This code will display two word documents ... one is the main mail merge document (SO10DR_HPL.doc), and the second is the product of that merge (the completed document). How would I go about just closing the main mail merge document? How can I ensure that the user focus goes to the new Word document rather than remaining on the Excel worksheet?
Please see code below
4) And finally ... not necessarily Excel related, but maybe can be done from Excel code, how can I automatically name the produced Word document so that the user doesn't have to try to remember the format of the filename. I need to specify a file name based on the report, but it has to be placed into a directory for the date of the report (represented in cell A1 of the worksheet) So, if the date was 21-Aug-10, I want this produced document automatically named DR_HPL.doc in a folder (if not there, make it!) 21-Aug-10?
Please see code below
Code:
'=============================================================================
'- BASIC EXCEL CODE TO USE WORD. SHOWS BASIC USE OF A VARIABLE LOOP
'- ALSO HOW TO CLOSE AN INSTANCE OF WORD - WHICH WILL OTHERWISE REMAIN IN MEMORY
'- otherwise could use up a lot of system memory especially during development
'- NB. Need to close Word (etc.) in the same way as we do it manually
'- Best to record Word macros first to get the code
'=============================================================================
'- variables here can be shared by all subroutines
Dim ExcelSheet As Worksheet ' Excel worksheet object
Dim WordApp As Object ' Word application object
Dim WordDoc As Object ' variable document object
Dim WordDocName As String ' variable document name
Dim MyDate As String ' today's date
Dim d As Integer ' loop counter
Dim MyFolder As String ' target document folder
'=============================================================================
'- MAIN ROUTINE
'- Document names in Excel range A1 to A3. Run from this worksheet (don't need name)
'- Makes a new set of Word documents with today's date in the file name
'=============================================================================
Sub test()
'-------------------------------------------------------------------------
'- INITIALISE VARIABLES
Set ExcelSheet = ActiveSheet
Set WordApp = CreateObject("Word.Application") ' start at the top level
WordApp.Visible = True
WordApp.Activate ' bring to top
MyFolder = "F:\TEST\" ' here with final " \ ""
MyDate = Format(Date, "dd-mm-yyyy") ' make date string
'-------------------------------------------------------------------------
'- LOOP WORKSHEET TO GET NAMES
For d = 1 To 3
WordDocName = ExcelSheet.Cells(d, 1).Value & ".doc" ' get name
MAKE_WORD_DOCUMENT ' CALL SUBROUTINE
Next
'-------------------------------------------------------------------------
'- FINISH - close everything & clear object variables
'ActiveDocument.Close ' not needed. all documents have been closed here
WordApp.Quit ' close Word
Set ExcelSheet = Nothing
Set WordDoc = Nothing
Set WordApp = Nothing
MsgBox ("Done")
'-------------------------------------------------------------------------
End Sub
'=============================================================================
'=============================================================================
'- SUBROUTINE : CODE TO MAKE & SAVE A NEW DOCUMENT
'- Would not necessarily use a subroutine for such a small amount of code,
'- but it shows the method.
'- Don't always need to have a Document Object - but better in larger
'- projects requiring more code. Saves typing the object name each time.
'- Private Sub does not show in the macro list
'=============================================================================
Private Sub MAKE_WORD_DOCUMENT()
'-------------------------------------------------------------------------
'- APPLICATION LEVEL
'-------------------------------------------------------------------------
With WordApp ' saves keep writing the variable name
.Documents.Add ' new empty document
'- ADD SOME TEXT
.Selection.TypeText Text:="THIS IS A TEST DOCUMENT " & MyDate
'---------------------------------------------------------------------
'- DOCUMENT LEVEL
'---------------------------------------------------------------------
Set WordDoc = ActiveDocument
With WordDoc
'-----------------------------------------------------------------
'- SAVE & CLOSE
.SaveAs Filename:=MyFolder & WordDocName & " " & MyDate
'-----------------------------------------------------------------
'- could be '.Close' addition for information
.Close savechanges:=False ' or True. stops "save" message
'-----------------------------------------------------------------
End With
'---------------------------------------------------------------------
End With
'-------------------------------------------------------------------------
End Sub
'============ end of subroutine ==============================================
