Totally confused

[HeatherO]

Hi I am new to programming in excel and word but have programmed in other
languages and am totally stuck with word programming. What I am trying to do
via a macro created in word is read through an excel spreadsheet and import
data into a document which I have set up bookmarks as to where the data from
the spreadsheet will go, but can't understand about fields and mergefields or
textfields on the document or how to set them up on the document. I can get
to the bookmarks but now I don't know how to insert the data from the
spreadsheet. Can someone please tell me how do I insert the data from column
A2 in spreadsheet sheet 1 into bookmark 3 in a word document. Can this be
done?? Sorry just finding word really hard to understand and not alot out
there that just explains all the different word commands.
TIA
Heather

 

[Doug Robbins]

See the article "Control Excel from Word" at:

http://word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[HeatherO]

Hi Doug,
I've actually looked at that article and tried some of the stuff the first
line the dim oXL as Excel.Application won't compile it states user-defined
type not defined and then I also get type mismatches when I try it. Am I
missing something about that article?? Here's the code I was using which is
set up as a macro for the document:

Sub xl_setup()

' xl_setup Macro
' Macro created 2/26/2005 by Heather Ouellette

'open excel and workbooks to do lookups and store in listing table.
Dim AppXL As Excel.Application
Dim oWB As Excel.workbooks
Dim oSheet As Excel.worksheet
Dim oRng As Range

Dim xlntrn As Boolean
Dim WrkbkToWrkOn As String
Dim lislrow As Long
Dim lokval As String

On Error Resume Next
Set AppXL = CreateObject("Excel.application")

If Err Then
xlntrn = True
Set AppXL = New Application
End If

'On Error GoTo Err_Handler

AppXL.Visible = False
workbooks.Open ("C\Model Pilot\Listing" & dlrrep & ".xls")
workbooks.Open ("C\Model Pilot\Model Grid_SWI.xls")
workbooks.Open ("C\Model Pilot\Names.xls")

workbooks("Listing" & dlrrep & ".xls").worksheets("Sheet1").Activate
With Activesheet
'get the last row of data for the ranges.
lislrow = AppXL.Cells(Rows.Count, "A").End(xlUp).Row
For counter = 2 To lislrow
lokval = AppXL.Cells(counter, 15).Value
'row Z - col L of model
res = Application.vlookup(lokval, workbooks("Model
Grid_SWI.xls").Sheets("Sheet1").Range("A2:M55"), 12, False)
If IsError(res) Then
AppXL.Cells(counter, 26).Value = ""
Else:
xlApp.Cells(counter, 26).Value = res
End If
'row AA - col H of Model
res = Application.vlookup(lokval, workbooks("Model
Grid_SWI.xls").Sheets("Sheet1").Range("A2:M55"), 8, False)
If IsError(res) Then
AppXL.Cells(counter, 27).Value = ""
Else:
AppXL.Cells(counter, 27).Value = res
End If
'row AB - used for AC lookup
res = Application.vlookup(lokval, workbooks("Model
Grid_SWI.xls").Sheets("Sheet1").Range("A2:M55"), 2, False)
If IsError(res) Then
AppXL.Cells(counter, 28).Value = ""
Else:
AppXL.Cells(counter, 28).Value = res
End If
'row AC - Short Name for table 1
res = Application.vlookup(lokval,
workbooks("Names.xls").Sheets("Sheet1").Range("A2:E36"), 3, False)
If IsError(res) Then
AppXL.Cells(counter, 29).Value = ""
Else:
AppXL.Cells(counter, 29).Value = res
End If
'row AD - Name for table 2
res = Application.vlookup(lokval, workbooks("Model
Grid_SWI.xls").Sheets("Sheet1").Range("A2:M55"), 10, False)
If IsError(res) Then
AppXL.Cells(counter, 30).Value = ""
Else:
AppXL.Cells(counter, 30).Value = res
End If

Next counter
workbooks("Model Grid_SWI.xls").Close
workbooks("Names.xls").Close

End With
End Sub

Any suggestions are appreciated.
Heather

 

[Jonathan West]

Hi Doug,
I've actually looked at that article and tried some of the stuff the
first
line the dim oXL as Excel.Application won't compile it states user-defined
type not defined and then I also get type mismatches when I try it. Am I
missing something about that article??

Yes, you need to go into Tools, References in the VBA editor and set a
reference to Excel. Then the code will recognise Excel.Application as a
known type.

 

[Perry]

Several ingredients that make the code buggy:
I haven't looked any further in the code but following lines are definitely
offending.

On Error Resume Next
Set AppXL = CreateObject("Excel.application")

If Err Then
xlntrn = True
Set AppXL = New Application
End If

In above code sequence, there's no need to catch the error, because Word
will always create a new instance (in the above code listing)
If you want Word to utitlize the current instance of Excel, you would go

On Local Error Resume Next
Set AppXL = GetObject(, "Excel.application")

If Err Then
xlntrn = True
Set AppXL = New Excel.Application '<< notice the difference in raising the
application object
'<< or you could use the CreateObject function instead.
'<< remember to reset the error object within this If ..Then block
'<< and reinstate the error GoTo bookmark in another passage of the code
Err.Clear
End If

Furthermore:
These statements:
res = Application.vlookup(lokval,
workbooks("Names.xls").Sheets("Sheet1").Range("A2:E36"), 3, False)

have to be replaced by
res = AppXL.vlookup(lokval,
workbooks("Names.xls").Sheets("Sheet1").Range("A2:E36"), 3, False)

If you want to use the Excel application, refer to AppXL as that is the
instance of Excel you raised and want to use.
Using the "Application" statement in Word VBA code, you will always refer to
the host application (in this case: Word) and not Excel.

Krgrds,
Perry