[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA: Checking Rage Exists or Not

Posted on 2006-03-24
6
Medium Priority
?
306 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


0
Comment
Question by:Shiju Sasidharan
  • 3
4 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 1000 total points
ID: 16279080
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
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16279092
assuming you do this directly from asp
0
 
LVL 26

Assisted Solution

by:EDDYKT
EDDYKT earned 1000 total points
ID: 16279288
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
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16583225
delete no refund
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question