Macro between Word & Excel

[Thrillho]

I wasn't sure if I should post this in the Word or the Excel section. I decided Word, since this is where I start and end my macro.

Without the details, I need a macro to do the following:
- Word: Copy data from multiple cells of a table
- Word: Open a specific Excel spreadsheet (in another directory, possibly)
- Excel: Paste data in specific cells
- Excel: Run another macro to do a calculation based on said data
- Excel: Copy results
- Excel: Close Excel spreadsheet
- Word: Paste data in multiple cells of a table

Now for some details. The data I'll be copying comes from a certain part of a table. Unfortunately, I can't guarantee this location will stay the same all the time. However, I can have this data put in to two columns - name in one column, value in another. So, VALUE1: 1234 would be one line, VALUE2: 5678 would be another. I would be able to guarantee VALUE1 and VALUE2, etc., wouldn't change (so, perhaps a search in the macro?)

The destination in Excel wouldn't change. Neither would the results area.

The final destination for the results data would be similar to the original - I can't guarantee it's location, but I can put it in two multiple columns.

Office version: 2010

Thoughts?

Thanks in advance!

[macropod]

Why does Excel need to be involved at all? It's not as if Word fields & Word vba are bereft of calculation abilities.

[Thrillho]

The excel file which would be opened is a database.

The word doc is a standard template which gets used multiple times, so I want some of the info that gets inputted to get logged in the database. And the easier I make the database get populated, the more likely it is to happen.

As for the calculation itself - that's based on the contents of the database, so a contained calculation in word wouldn't be sufficient. As for linking the table to excel via paste>link, the database, as it stands right now, is 15,000 rows x 9 columns and growing.

The word doc contains more than just the database data (in fact, it contains no database data, I want to add this section to it). So changing over to an excel template and inputting there isn't an option. And having two files (this doc + an excel doc) for the user to input will make it more of an inconvenience for the user, and more likely that this action will not be done.

[macropod]

In that case, it seems to me it would make more sense to have the basic data entry and calculations done in the Excel workbook before the data and results are output from there to a new document based on the template. That way, at least, the data only need to flow in one direction. Furthermore, you'll have access to all of Excel's data validation functions before the document is created.

[Thrillho]

That is, unfortunately, not going to work. This database will be used by about 200 people, so giving them all access to open it up at any given time wouldn't be good - especially if one of them messes something up in the database. Having a controlled entry spot keeps this running quickly so there's less likelihood of two people being in at the same time, and keeps control of how the data is entered in.

If I were to put the spreadsheet to Shared, I would be able to track any changes and revert back if something were change that ought not be changed. However, macros don't run properly in shared workbooks as far as I'm aware.

And lastly, this adds more work to people who are already overworked.

I should have asked in the beginning - is it even possible to do something like this? Go from Word to Excel in a macro?

[macropod]

Yes, it's quite possible to do what you envisage and a search of this board will turn up numerous threads discussing the question of populating an Excel workbook from Word and, conversely, populating a Word document from Excel. You'll find quite a few in the 'Integration/Automation of Office Applications Help' area (http://www.vbaexpress.com/forum/forumdisplay.php?f=21). There are probably even some for automating Excel calculations from Word.

Whether it's advisable to do what you envisage is another matter entirely. As this thread has developed, it's emerged that many people have access to the Excel workbook, and multiple users may require access at the same time. No amount of automation from Word is going to overcome the fact that this will inevitably lead to conflicts, including users being locked out when they want to prepare their documents. You are using the wrong tool for the job. Instead of Excel, you should be using Access, or another database application that supports multi-user concurrent read/write access.

[fumei]

My two cents. I have to totally agree with Paul. While many people try and pretend to use Excel - and indeed mention as you do - as a database, but the fact remains...it is not a database application.

It can be faked to act somewhat like one, but it remains a spreadsheet. It can be faked successfully, but only up to a point. It can not be scaled up to the point of "15,000 rows x 9 columns and growing", and 200 odd users.

Trying to shoehorn Word (which is not a front-end of a database) into the mix is, at the very least, problematic. As Paul points out, no amount of automation in Word is going ameliorate an essentially poor situation.

[Thrillho]

Thanks for the input.
You're right. This won't work as well as I had intended. At the moment though, it's all I have to work with. If I can get a proof of concept going with it, I can get the resources to switch to an actual database system.

[macropod]

FWIW, here's some code to, ostensibly, open an Excel workbook from Word so you can do something with it.
Sub UpdateWithExcel()
Application.ScreenUpdating = True
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim bStrt As Boolean, iDataRow As Long, bFound As Boolean
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
StrWkSht = "Sheet1"
If Dir(StrWkBkNm) = "" Then
MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
Exit Sub
End If
' Test whether Excel is already running.
On Error Resume Next
bStrt = False ' Flag to record if we start Excel, so we can close it later.
Set xlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel.", vbExclamation
Exit Sub
End If
' Record that we've started Excel.
bStrt = True
End If
On Error GoTo 0
'Check if the workbook is open.
bFound = False
With xlApp
'Hide our Excel session
If bStrt = True Then .Visible = False
For Each xlWkBk In .Workbooks
If xlWkBk.FullName = StrWkBkNm Then ' It's open
Set xlWkBk = xlWkBk
bFound = True
Exit For
End If
Next
' If not open by the current user.
If bFound = False Then
' Check if another user has it open.
If IsFileLocked(StrWkBkNm) = True Then
' Report and exit if true
MsgBox "The Excel workbook is in use." & vbCr & "Please try again later.", vbExclamation, "File in use"
If bStrt = True Then .Quit
Exit Sub
End If
' The file is available, so open it.
Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm)
If xlWkBk Is Nothing Then
MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
If bStrt = True Then .Quit
Exit Sub
End If
End If
' Update the workbook.
With xlWkBk.Worksheets(StrWkSht)
' Find the last-used row in column A.
' Add 1 to get the next row for data-entry.
iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row + 1 ' -4162 = xlUp
' Output the captured data.
' Your code to read the document content and update the Excel workbook goes here
' As does your code to automate the excel calculation, then read back the results.
End With
If bFound = False Then xlWkBk.Close True
If bStrt = True Then .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
Application.ScreenUpdating = True
End Sub
Function IsFileLocked(strFileName As String) As Boolean
On Error Resume Next
Open strFileName For Binary Access Read Write Lock Read Write As #1
Close #1
IsFileLocked = Err.Number
Err.Clear
End Function
At this stage, nothing is passing between Word & Excel - all we've done so far is to go through the hoops of opening the Excel Workbook, if possible, and finding an unused row into which the document's data might go.

IMHO, the better your 'proof of concept' works out, the less likely management will be to develop a database to do it properly - until they're inundated with complaints from the users. By then, of course, they'll have invested so much in an inadequate solution they'll be even more reluctant to see all that investment go to waste (which it will) by switching over to a database (which is what they should have done in the first place).