We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

VBA: Checking Rage Exists or Not

Medium Priority
346 Views
Last Modified: 2010-04-07
Hi Experts

I am using following asp code

strCnxnString="Provider=MSDASQL.1;Persist Security Info=False;Extended Properties='Excel 9.0';DBQ="& Server.MapPath(strFileName) & ";Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 9.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=8;UID=admin;UserCommitSync=Yes;"";User Id=admin;"

Set cnnXL= server.CreateObject("ADODB.Connection")
Set objXL=server.CreateObject("ADODB.RecordSet")
sqlXL="SELECT * FROM Range9"     '>> This range is to be checked whether it exists
cnnXL.Open strCnxnString,3,3
objXL.Open SqlXL,cnnXL,3,3

My question is
>>sqlXL="SELECT * FROM Range9"
if there is no such range  "Range9" it will give error
is there any direct way to check whether specified range Exists or not ?

Thanks
Shiju


Comment
Watch Question

Freelance
Top Expert 2006
Commented:
Hello shijusn,

since you can't do anything when it errors [no data will be returned] you could add an errorhandler catching the specific error, only when you have a direct handle to an excel sheet you could check through the object if a range existed don't know of a way through ADO

----------
on error goto Errhandle

Set cnnXL= server.CreateObject("ADODB.Connection")
Set objXL=server.CreateObject("ADODB.RecordSet")
sqlXL="SELECT * FROM Range9"     '>> This range is to be checked whether it exists
cnnXL.Open strCnxnString,3,3
objXL.Open SqlXL,cnnXL,3,3

'do all other work

exit sub
Errhandle:
  if Err.Number = ?? then
    'do something here
  else
    'another error
  end if
  'cleanup objects
  exit sub
end sub
----------

hope this helps a bit
bruintje

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Brian MulderFreelance
Top Expert 2006

Commented:
assuming you do this directly from asp
Commented:
OR

on error resume next
Set cnnXL= server.CreateObject("ADODB.Connection")
Set objXL=server.CreateObject("ADODB.RecordSet")
sqlXL="SELECT * FROM Range9"     '>> This range is to be checked whether it exists
if err.number = 0 then
cnnXL.Open strCnxnString,3,3
objXL.Open SqlXL,cnnXL,3,3
end if
Brian MulderFreelance
Top Expert 2006

Commented:
delete no refund
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.