I am new to this forum and was wondering if someone could help me with setting a Range object to a named range. The following code is in vbscript using WSH.

In Excel I have named a group of contiguous cells (B3:E6) with the range name "le_adm_rng". I have tried various constructs in vbscript to set a Range object to this named range, none of which are working. I have included those examples in the code below.

Any help would be greatly appreciated!


Here is the script:
' Purpose: Update the raw data worksheet in a spreadsheet with data from a csv file 

' Input: 
' Excel: Name of excel spreadsheet to be updated 
' Worksheet: Name of worksheet to update 
' Csv: Name of csv file from which data will be copied 

' Example: 
' cscript updExcelData.vbs /excel:oce_100k_template.xls /worksheet:le_b /csv:list_element_admin.csv 


' Constants 
Const xlAppStr = "Excel.Application" 

excelFileName = WScript.Arguments.Named.Item("Excel") 
csvFileName = WScript.Arguments.Named.Item("CSV") 
worksheetName = WScript.Arguments.Named.Item("Worksheet") 

' Connect to Excel 
Set ExcelObj = CreateObject("Excel.Application") 
ExcelRunning = IsAppRunning(xlAppStr) 
If ExcelRunning Then 
Set xlApp = GetObject(, xlAppStr) 
Set xlApp = CreateObject(xlAppStr) 
End If 

' Open or create the excel file to modify 
Set FSObj = CreateObject("Scripting.FileSystemObject") 
excelFileName = FSObj.GetAbsolutePathName(excelFileName) 

If FSObj.FileExists(excelFileName) Then 
xlApp.Workbooks.Open excelFileName 
Set editWB = xlApp.ActiveWorkbook 
Set editWB = xlApp.Workbooks.Add 
editWB.SaveAs excelFileName 
End If 

Set editOrigWS = editWB.ActiveSheet 
Set editWND = xlApp.ActiveWindow 

' Create/Update the worksheet with the CSV file data 

csvFileName = FSObj.GetAbsolutePathName(csvFileName) 
xlApp.Workbooks.Open csvFileName 
Set rawWB = xlApp.ActiveWorkbook 

Set objFrRange = rawWB.ActiveSheet.Range("A1:D8") 

Set editWS = FindWorksheet(editWB, worksheetName) 

' -----This is the original code and this works fine, I would like to replace the B3:E26 with a named range  
Set objToRange = editWS.Range("B3:E26")

' -----Here are the examples of what I have tried that didn't work 
' ----- Set objToRange = editWS.Range("le_adm_rng") 
' ----- Set objToRange = editWS.Range.Name("le_adm_rng") 
' ----- Set objToRange = editWB.Names("le_adm_rng") 
' ----- Set objToRange = editWB.Names.Range.Name("le_adm_rng")

objToRange.PasteSpecial -4163,-4142,False,False 

editWB.Close True 
Set editWB = Nothing 
rawWB.Close True 
Set rawWB = Nothing 

If Not ExcelRunning Then xlApp.Quit 
Set xlApp = Nothing 

Function IsAppRunning(app) 
Dim xlApp 
On Error Resume Next 
Set xlApp = GetObject(, app) 
IsExcelRunning = (Err.Number = 0) 
Set xlApp = Nothing 
End Function 

' Find or create the worksheet 
Function FindWorksheet (workbook, wsName) 
ws = Empty 
For Each w In workbook.Worksheets 
If w.Name = wsName Then 
Set ws = w 
Exit For 
End If 
If IsEmpty(ws) Then 
Set ws = workbook.Worksheets.Add 
ws.Name = wsName 
End If 
Set FindWorksheet = ws 
End Function

Last edited by shabbir; 20Oct2008 at 11:13.. Reason: Code block