Editing Excel File from Word 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

 

[Cindy M -WordMVP-]

Hi Chesney,

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.

The first thing I suggest you do is to make sure you're
going to see all the errors. Right now, if something is
going wrong, you're not going to get notified:

On Error Resume Next ' Defer error trapping.

Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear
'ADD this
On Error GoTo 0

Now test your code and see what kind of error message it
might not have been showing you up until now.

The other thing is, you're making a Window visible, but
perhaps not the window of the file you're opening.

MyXL.Parent.Windows(1).Visible = True

It would probably be better to use
Set myXL = GetObject(, "Excel.Application")
Set myWB = myXL.Workbooks.Open "C:\File.xls"
myWB.Windows(1).Visible = True

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun
8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail