Pass excel variable to a footer in word

[glendon]

I have an Excel form to collect data. That data then goes in several Word doc's (using bookmarks) that are opened when a button is pressed. The only thing I am not able to do is send this data to a footer because it "cannot find" the bookmark in the footer. How do I send data to a footer in Word? Thanks in advance for any help ya'll might lend (I'm new to vba).

 

[Trevor G]

You can record macros in Word to give you what you need and then adapt the code in Excel to do the thing you want. Here is an example

Sub mcrFooter()
Dim wrd As Object
Set wrd = CreateObject("Word.Application")
With wrd
.Visible = True
.documents.Add
.WordBasic.ViewFooterOnly
.Selection.TypeText Text:="Hello"
End With
End Sub

 

[glendon]

Thanks Trevor. Not sure how to adapt this to excel. I get "Run-time error '438': Object doesn't support this property or method" for

.WordBasic.ViewFooterOnly

Any ideas?

Thanks - glen.

 

[Trevor G]

Glen,

Which version of Office are you using as I recorded in Word 2007 and converted the code in Excel 2007 to make it work !

Can I suggest that you record in your Word document a MACRO and then use the features to View the Footer and you type a basic word or phrase in and then tell it to stop recording that will give you the code and you can post back the code and what you have in Excel.

 

[glendon]

I am using 2003.

I did what you suggested (recorded the macro and copied code over to excel) but excel did not recognize it.

Run-time error '438' Object doesn't support this property or method.

How do I convert this so excel recognizes it?

thanks - glen

 

[Trevor G]

Glen can you post your code then I can take a look and get it to work for you.

Basically if you notice in the sample I sent you I have referenced to use Word as an Object and within the object I have used an With statement, so your code will work within a with statement, but that means you need to use a full stop at the beginning of each line within the with statement.

 

[glendon]

Hi Trevor, here's some of what I have so far... thanks - glen.


Private Sub Open_CommandButton_Click()

Dim objWord As Word.Document
Dim objExcel As Excel.Worksheet
Dim PackageNumber As String, PackageName As String, RevNum As String, BuyerName As String, BuyerPhone As String, BuyerFax As String, BuyerEmail As String, BidDueDate As String, CurrentDate As String, ReqDate As String, CompanyName As String, CompanyAddress1 As String, CompanyAddress2 As String, ContactName As String, ContactPhone As String, ContactFax As String, ContactEmail As String
Dim PackageNameNum As String

'// Set variable values from what is entered on the form. \\

BuyerName = BuyerName_ComboBox.Value
PackageNameNum = ReqNum_ComboBox.Value
RevNum = ReqRevNum_ComboBox.Value
ReqDate = ReqDate_ComboBox.Value
BidDueDate = BidDueDate_ComboBox.Value
CompanyName = CompanyName_TextBox.Value
CompanyAddress1 = CompanyAddress1_TextBox.Value
CompanyAddress2 = CompanyAddress2_TextBox.Value
ContactName = ContactName_TextBox.Value
ContactPhone = ContactPhone_TextBox.Value
ContactFax = ContactFax_TextBox.Value
ContactEmail = ContactEmail_TextBox.Value

'// Search (.find) ranges to set remaining variables based on what is entered on the form. \\

With Sheet4.Range("BuyerName")
BuyerPhone = .Find(What:=BuyerName, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)

BuyerFax = .Find(What:=BuyerName, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 2)

BuyerEmail = .Find(What:=BuyerName, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)
End With

With Sheet2.Range("PackageNumberList")
PackageNumber = .Find(What:=PackageNameNum, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)

PackageName = .Find(What:=PackageNameNum, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 2)
End With

'// Start Word as an Application \\
Set objWordApp = New Word.Application

'// Set the Word App to Visible \\
objWordApp.Visible = True

'// Open the Template Doc - B) INSTRUCTION TO BIDDERS \\
objWordApp.Documents.Open Filename:="L:\00137-HEBTS\00\PP\Procurement Forms\RFQ\B) Instr to Bidders - Current Template 25 Apr 11.doc"

'// Set the document as an object for easy referencing \\
Set objWord = objWordApp.Documents(1)

'// Go to a pre-defined bookmark \\
objWordApp.Selection.Goto What:=wdGoToBookmark, Name:="PackageNumber1"
'// Type the text passed from excel \\
objWordApp.Selection.TypeText Text:=PackageNumber

objWordApp.Selection.Goto What:=wdGoToBookmark, Name:="RevNum"
objWordApp.Selection.TypeText Text:=RevNum
objWordApp.Selection.Goto What:=wdGoToBookmark, Name:="ReqDate1"
objWordApp.Selection.TypeText Text:=ReqDate
objWordApp.Selection.Goto What:=wdGoToBookmark, Name:="BuyerName1"
objWordApp.Selection.TypeText Text:=BuyerName

'**** I repeat this for several different documents. ****'

'// Close form once complete. \\
Unload Me

End Sub

 

[Trevor G]

OK I can see the Excel code and getting to Word but you haven't shown the Footer MACRO I suggested you record in Word. It is very easy to do this, simply createa new word document, then select the Tools Menu and Macro then Record New Macro, then change the name mcrFooter Then OK

Select the View Menu and Header/Footer then press down cursor and type something in the Footer. Select the Tools Menu and Macro then Stop Recording.

You can then view the code by pressing Alt + F8 and edit the Macro, if you post that code I will add it to your code.

 

[glendon]

Thanks Trevor - I didn't put this in because it didn't work. Thanks again - glen.


Sub mcrFooter()
'
' mcrFooter Macro
' Macro recorded 5/27/2011 by glen.abrams
'
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
ActiveWindow.ActivePane.VerticalPercentScrolled = 29
Selection.TypeText Text:="Howdy"
End Sub

 

[NdNoviceHlp]

HTH, Dave

Dim FtStr As String
FtStr = CStr(Sheets("sheet1").Range("A" & 1).Value)
Objwordapp.activedocument.Sections(1).Footers(1).Range.Text = FtStr