Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type mismatch using rnge as Range with Type 8 Input Box | Excel Programming | |||
Type mismatch on range name | Excel Programming | |||
Type mismatch in VBA LinEst function if range too large | Excel Programming | |||
setting ctl to array of checkboxes yields type mismatch error. | Excel Programming | |||
setting ctl to array of checkboxes yields type mismatch error. | Excel Programming |