1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Request for Help in Vbscript Settiing Range Object to Named Range

Discussion in 'Visual Basic ( VB )' started by Dahlia, Oct 20, 2008.

  1. Dahlia

    Dahlia New Member

    Oct 20, 2008
    Likes Received:
    Trophy Points:

    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) 
    [COLOR=DarkRed]' -----This is the original code and this works fine, I would like to replace the B3:E26 with a named range [/COLOR] 
    Set objToRange = editWS.Range("B3:E26")
    [COLOR=DarkRed]' -----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")[/COLOR]
    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 a moderator: Oct 20, 2008

Share This Page