Word Template - Edit Excel with VBA

[chesney]

I have a Word template that creates a reference number
and collects information for the document from the user.
I am trying to input the information gathered from each
new document into an Excel Spreadsheet.

I have it working up to a point. It opens the Excel
file, finds the next empty row, then enters all the info
from the Word template, closes the file.

The problem I have is that it acts strangely if you
already have Excel open. The macro runs and enters the
data into the spreadsheet fine, but the next time you go
to open the Excel spreadsheet, you can only see it by
unhiding the window. This doesn't happen if Excel was
not open when the macro was run and it exits Excel at the
end of the macro.

This template is for basic users, and I don't want to
have to give them conditions for using the templates
(like having Excel closed), as they probably just won't
use it if I do that.

The code I'm using is the following:

Sub GetExcel()

QuoteRef = ThisDocument.Variables("QuoteRef")

Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final
release.

On Error Resume Next ' Defer error trapping.

Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error
occurred.

Set MyXL = GetObject("C:\File.xls")

MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
MyXL.Application.ScreenUpdating = False
MyXL.activesheet.Range("A1").Select

Dim i As Integer

i = 1

For i = 1 To 200
If MyXL.activesheet.Range("a" & i) = "" Then
GoTo Continue
End If
Next

Continue:

MyXL.activesheet.Range("A" & i) = QuoteRef
MyXL.activesheet.Range("B" & i) = "LINK"
MyXL.activesheet.Range("C" & i) = "SalesProp"
MyXL.activesheet.Range("D" & i) = Date
MyXL.activesheet.Range("E" & i) = form1.cbxSalesman
MyXL.activesheet.Range("F" & i) = form1.cbxCustomer
MyXL.activesheet.Range("G" & i) = form1.txtTitle

With MyXL.activesheet
.Hyperlinks.Add .Range("B" & i).Cells(1, 1),
ActiveDocument.FullName
End With

MyXL.activesheet.Range("A" & i).Select

MyXL.Application.ScreenUpdating = True

MyXL.Activeworkbook.Save

If ExcelWasNotRunning = True Then
MyXL.Application.Quit
Else
MyXL.ActiveWorkbook.Close
End If

Set MyXL = Nothing

End Sub

Any suggestions or work-arounds anyone has used would be
greatly appreciated

Thanks, Chesney

 

[Helmut Weber]

Hi Chesney,
not quite simple, you may have to force excel in the foreground.
Have a look at this, it could be a start into the right direction.
Karl. E. Peterson's One-Stop SourceShop
http://www.mvps.org/vb/
file "forcefore.zip"
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98