Re: Word-Excel link only works the first time

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

If you mean a reference in the VBE to the Object library, I've got that. It
wouldn't even work the first time if I didn't.
If you mean something else, how do I "qualify" the references ?

--
David M


"Tony Jollans" wrote:

You need to qualify your reference to Rows - and several other references -
so that they refer to properties of Excel objects.

--
Enjoy,
Tony

"#DIV/0" <DIV0@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2DEC932F-B9D7-41E5-968C-1448C8B3698A@xxxxxxxxxxxxxxxx
A button on my Word UserForm links to an Excel work***. It copies some
data
from the form, gets some other data off the work***, saves the work***
and quits Excel. Once I've visually checked the data is OK I click another
button to put everything into bookmarks on my Word document.

Private Sub ProtButton_Click()
Dim OffNo As String
Dim xlApp As Excel.Application
Dim xlWB As Excel.workbook
Dim i As Integer
'On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlWB = xlApp.Workbooks.Open("C:\Offers.xls")
With xlWB.Worksheets("germany").ScrollArea = "A1:G2000"
Dim iRow As Long
iRow = Active***.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Active***.Cells(iRow, 3).Activate
ActiveCell.Offset(-1, 0).Copy
Active***.Paste
Active***.Cells(iRow, 6).Activate
ActiveCell.Offset(-1, 0).Copy
Active***.Paste
Active***.Cells(iRow, 1).Activate
ActiveCell.Value = TextBox9.Value
ActiveCell.Offset(0, 1).Value = TextBox10.Value
OffNo = ActiveCell.Offset(0, 2).Value
ActiveCell.Offset(0, 3).Value = ContactLabel.Caption
ActiveCell.Offset(0, 4).Value = TextBox12.Value
ActiveWorkbook.Save
End With
xlApp.Quit
Set xlWB = Nothing
Set xlApp = Nothing
Label10.Caption = OffNo
Label10.Caption = Format(Label10.Caption, "0000")
ProtLabel.Caption = "07-" & Label10.Caption & "-" &
Application.UserInitials
End Sub

At least, it does all the above the first time every session. The second
time I click the button it throws up an error pointing the finger at
"Rows"
in the line
iRow = Active***.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Since the operation is interrupted, Excel thinks the *** is still open
and
I have to log out in order to open it again.

Does anyone have any idea why this is happening and what I can do about it
?

--
David M


.

Quantcast