Setting range object--Type mismatch if template not used
 

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

Setting range object--Type mismatch if template not used
 

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)



wrote in message
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

Setting range object--Type mismatch if template not used
 

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

Jim Cone wrote:
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)



wrote in message
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