Hi All,

I'm trying to define name to a excel range and make it as a table to retrive the data at run time. When i try to manually define the excel range and use the name as table name, it retrives the data from excel using select sql statement. Same when i try to define name at run time it throws a error. It is because the range is defined in a object and the table name need to be passed as a string. Is their any way we can define a string name to a range of column in the excel using VB script.

filePath = "C:\ExcelStore.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
' This part of code define the excel range and store in a object reference. Is their any way we can ' define the same range corresponding to a string reference. and range specified keeps waring for ' result to result
oRangeName = oExcel.Range("G12:I17").Select
objrange = oRangeName
'call GetArrayFromStore(TestArr, oRangeName, "C:\ExcelStore.xls")
Set Connection = CreateObject("ADODB.Connection")
Connection.ConnectionString = "DBQ=" + filePath + _
";Driver={Microsoft Excel Driver (*.xls)}" + _
";DriverId=790;FIL=excel 8.0;MaxBufferSize=2048" + _
";MaxScanRows=8;PageTimeout=5;ReadOnly=1" + _
";SafeTransactions=0;Threads=3;UserCommitSync= Yes"

Set ConnRs = CreateObject("ADODB.Recordset")
ConnRs.CursorType = 3

' The code will work fine is objrange value is a string reference and not as object
query = "select * from "& objrange
'Call ConnRs.Open("select * from " + TableName+" Where Designation = 'Tester'", Connection)
Call ConnRs.Open(query, Connection)
fieldcount = ConnRs.Fields.Count
Msgbox fieldcount