Go4Expert (http://www.go4expert.com/)
-   Visual Basic ( VB ) (http://www.go4expert.com/forums/visual-basic/)
-   -   Request for Help in Vbscript Settiing Range Object to Named Range (http://www.go4expert.com/forums/request-help-vbscript-settiing-range-t14633/)

Dahlia 20Oct2008 07:17

Request for Help in Vbscript Settiing Range Object to Named Range

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

All times are GMT +5.5. The time now is 00:33.