Create Excel Workbook etc - Close - in a loop - hyperlink add failure

[ML]

I'm newbie to group - but not in VBA - Access.
I'm generating Excel workbook/sheets etc on the fly and it works! -
except...

I have a loop which

1. copies a template workbook - vba
2 opens it as an Excel object etc
3 assigns hyperlinks to cells

ETC

n. quits the application
n+1 sets application to nothing


This works perfectly for the first loop iteration

next time round it does 1 and 2 but fails on 3

oWrkSh.Cells(i + 6, "F") = Employees(i)
RangeCells = "H" & CStr(i + 6) & ":I" & CStr(i + 6)
oWrkSh.Range(RangeCells).Select

FAILS NEXT STATEMENT even though it was ok for the 1st iteration - workbook

oWrkSh.Hyperlinks.Add Anchor:=Selection, _
Address:="", SubAddress:="'" & Employees(i) & " (Leads)'" & "!A1",
TextToDisplay:="Leads Report"

error says automation error - can't add hyperlink or thereabouts

Any ideas?

TIA Michael

 

[Alex Dybenko]

Hi,
try to either use

oExcelApp.Selection instead of Selection
where oExcelApp is reference to excel application object

or just use oWrkSh.Range(RangeCells) instead of Selection

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[ML]

Thanks for ideas - but solved my own problem. It is an Excel "feature"

Each of the workbooks I was creating had the same filename - different
paths - but eventually xxxx.xls

Even closing the application between workbook creations seemed to leave some
trace of xxxx.xls in "excel memory" - so it failed at really any statement
which referred to a worksheet.

The workaround - bit of a nuisance - Each filename for each new workbook, is
different.

This works .

An aside.

It is the most incredible sight - a "few" lines of code and workbooks and
multi worksheets creating in their multitudes in front of your eyes!


regards,
Michael