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

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

The problem I have is that it acts strangely if you already have Exce
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 onl
see it by unhiding the window. This doesn't happen if Excel was no
open when the macro was run and it exits Excel at the end of th
macro.

This template is for basic users, and I don't want to have to give the
conditions for using the templates
(like having Excel closed), as they probably just won't use it if I d
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 greatl
appreciated

Thanks, Chesne

--
Message posted from http://www.ExcelForum.com