Setting range object--Type mismatch if template not used

[cbender]

Hi All;

I'm a bit baffled. I have the following code that works fine as long
as I use a template to create the workbook:

Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = objXL.Workbooks.Add
("\\SomeServer\Reporting\test.xlt")

Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME

End With
objWkb.SaveAs (pstrWorkbookLoc)
Set rngCurr = Nothing
lngRtnErr = lngCreateTable(objSht)
....(skip into lngCreateTable..see below

Public Function lngCreateTable(objSht As Excel.Worksheet) As Long

Dim rngCurr As Range

Set rngCurr = objSht.Range("A3")


The line above causes a Type Mismatch error unless I use a Template
when creating the Workbook. Can anyone tell me why?

Thanks in advance
Cheryl

 

[Jim Cone]

Cheryl,
Your code worked for me from Excel.
If you are automating Excel from Word or elsewhere then
the rngCurr declaration is ambivalent.
Word has a Range object as well as Excel.

replace...
Dim rngCurr as Range
with
Dim rngCurr as Excel.Range
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(free Excel add-ins plus Special Sort)



Hi All;
I'm a bit baffled. I have the following code that works fine as long
as I use a template to create the workbook:

Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = objXL.Workbooks.Add
("\\SomeServer\Reporting\test.xlt")

Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME

End With
objWkb.SaveAs (pstrWorkbookLoc)
Set rngCurr = Nothing
lngRtnErr = lngCreateTable(objSht)
....(skip into lngCreateTable..see below

Public Function lngCreateTable(objSht As Excel.Worksheet) As Long
Dim rngCurr As Range
Set rngCurr = objSht.Range("A3")
The line above causes a Type Mismatch error unless I use a Template
when creating the Workbook. Can anyone tell me why?
Thanks in advance
Cheryl

 

[cbender]

Jim;

Thanks--declaring the range as an Excel.Range object did the trick. I
had forgotten to mention that I was automating this from Access.

regards
Cheryl