Request Help to Set Range Object to Named Range

[DayLily]

Hi

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

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!

Thanks,
Karen

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 /excelce_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)
Else
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
Else
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("A18")
objFrRange.Copy

editWND.Activate
Set editWS = FindWorksheet(editWB, worksheetName)
editWS.Activate

' -----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.Select
objToRange.PasteSpecial -4163,-4142,False,False

editWB.Sheets(1).Select
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
Err.Clear
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
Next
If IsEmpty(ws) Then
Set ws = workbook.Worksheets.Add
ws.Name = wsName
End If
Set FindWorksheet = ws
End Function

 

[NWBeaver]

You say that you have a named range "le_adm_rng" in Excel, are you sure that named range exists on the correct worksheet?
It appears to me that your script can create a new workbook and or worksheet. If so, I don't see how the named range would exist. The range B3:E26 would exist on any worksheet, but a named range would still need to be created on the new worksheet before you can reference it.
Perhaps I'm missing something, but that seems like it could be a problem.