Interacting between Word & Excel

[Dave C]

I don't know if this is possible, I hope that it is, and if so would be
grateful if someone could point me in the right direction of articles etc to
provide the how to.

I have a functioning spreadsheet (Excel) that calculates staff holiday
entitlement based on employment start date & contracted hours. I also have a
employment contract template (Word) that merges basic data (name; address)
from Access.

I currently have the spreadsheet linked to the document but it requires the
user to open Excel enter the information there and then open the Word
document to create the contract. I want to provide the user with a single
interface (Word user form) and not require them to be entering information
into both applications.

What I want to do is create a userform (I can do that) and have the user
enter the variable data (start date; contract hours) and pass that to the
spreadsheet to calculate the holiday entitlement, and then have Excel pass
the calculated value (number of hours) back to Word (display on user form
would be good) so it can be included in the employee contract (I know how to
do that once I've got the data).

Any help will, as always, be gratefully received.

Thanks ... Dave

 

[Pesach Shelnitz]

Hi Dave,

One way to do this would be to create a MACROBUTTON in the Word document
that lauches a macro that opens the Excel spreadsheet, copies the data from
the Word doc into the appropriate spreadsheet cells, tells Excel to perform
the necessary calculations, and passes the Excel data back to the appropriate
places in the Word document. Basically, the macro would do what you don't
want the user to have to do. Write back if you need any help to implement all
of this.

 

[Dave C]

Hi Pesach,
Thanks for getting back to me so quickly.
If you could elaborate on the 'how to' that would be great. By that I mean
an example of the macro (vba code I assume) I need to open excel, copy the
data etc etc.

Thanks once again .... Dave

 

[Pesach Shelnitz]

Hi Dave,

Since I don't have you Excel file and don't know exactly what you want to do
with the data in it, I can only give you something general. The following
Word macro retrieves the data in the cell located in the second column in the
first row in the Excel file whose name is hard-coded in the macro and inserts
it into the Word file at the cursor.

Sub GetDataFromExcelDemo()
Const Error_FileNotFound = 1004
Const Error_NotRunning = 429
Const Error_NotInCollection = 9
Dim myExcelfile As String
Dim wkbName As String
Dim xlValue As String
Dim xlApp As Object
Dim wkBook As Object
Dim newInstance As Boolean
Dim i As Long
Dim j As Long
Dim k As Integer

myExcelfile = "C:\MyExcelFiles\MyFile.xls"
k = InStrRev(myExcelfile, "\", -1, vbTextCompare)
If k > 0 Then
wkbName = Right(myExcelfile, k - 1)
Else
MsgBox "A suitable file name was not specified."
Exit Sub
End If
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = Error_NotRunning Then
Set xlApp = CreateObject("Excel.Application")
newInstance = True
Else
newInstance = False
End If
Err.Clear
Set wkBook = xlApp.Workbooks(wkbName)
If Err.Number = Error_NotInCollection Then
Err.Clear
Set wkBook = xlApp.Workbooks.Open(myExcelfile)
If Err.Number = Error_FileNotFound Then
MsgBox "The file specified could not be opened."
If newInstance = True Then
xlApp.Close
End If
Set xlApp = Nothing
Exit Sub
End If
End If
With wkBook.WorkSheets(1)
i = 1
j = 2
xlValue = .Cells(i, j).Value
Selection.TypeText xlValue
End With
wkBook.Close
If newInstance = True Then
xlApp.Close
End If
Set wkBook = Nothing
Set xlApp = Nothing
End Sub

 

[Dave C]

Thanks Pesach,
I'll work with what you've provided and see how I go.

Thanks once again

 

[Doug Robbins - Word MVP]

It should be possible to calculate the holiday entitlement with code in the
userform without needing to go out to Excel to do the calculation.

--
Hope this helps

Doug Robbins - Word MVP
Please reply only to the newsgroups unless you wish to avail yourself of my
services on a paid, professional basis.

 

[Dave C]

Hi Pesach,
I've quickly tested against my file and it works great, however, it only
retrieves data from Excel and drops it into Word. Sorry to be a pain but can
you suggest the code I'd need to pass a value from Word to Excel in order for
it to calculate the cell value I'm bring back to Word

Thanks once again,

Dave