How to find range of an excel sheet???????

[vijayasankarb]

Hi All,

I am developing one application in VBScript which automatically generates excel charts. But, I have a problem here.
I need to get the range value(like A1:B50) before calling the Activechart.setSourcedata function.....
In the below code I am asking the user to enter the range.
But, It is not requiremnet, The user just gives the .xls file. We need to get the range of the data.
Please give me the code which gets the range of an excel sheet.
thanx in advance....................

------------------------------VBCode--------------------

AutoChart()

sub AutoChart()

dim app, chartType
set app = createobject("Excel.Application")

'Make it visible
app.Visible = true

'Ask for file name
sFilename = app.GetOpenFilename("All files (*.*), *.*")

'To Exit if Cancel was pressed
If sFilename = "False" Then Exit Sub


' Add a new workbook
dim wb
set wb = app.workbooks.open(sFilename)

'Read the range
Dim inRng
inRng = InputBox("Enter the Range ( Ex: A1:F50 )", "Range")

'Get the range
dim rng
set rng = wb.ActiveSheet.Range(inRng)


'Add a new chart
wb.Charts.Add
'Assgin the chart type
wb.ActiveChart.ChartType = 65

'Set the SourceData
wb.ActiveChart.SetSourceData rng, 2

' Give the user control of Excel
app.UserControl = true

end sub

-----------------------------end----------------------------

[vijayasankarb]

Hi I have tried with the following code .............
But it only works when the data starts from A1..... otherwise it fails....

--------------------
Dim col, row, i, j
col = ""
j=1
for i = 1 to 50
If app.Cells(i, j).Value <> "" then
j=j+1
else Exit For
end if
Next

j=j-1
select case j
case 1
col = "A"
case 2
col = "B"
case 3
col = "C"
case 4
col = "D"
case 5
col = "E"
case 6
col = "F"
case 7
col = "G"
case 8
col = "H"
case 9
col = "I"
case 10
col = "J"
case 11
col = "K"
case 12
col = "L"
case 13
col = "M"
case 14
col = "N"
case 15
col = "O"
case 16
col = "P"
case 17
col = "Q"
case 18
col = "R"
case 19
col = "S"
case 20
col = "T"
case 21
col = "U"
case 22
col = "V"
case 23
col = "W"
case 24
col = "X"
case 25
col = "Y"
case 26
col = "Z"

end select

j=1
for i = 1 to 3000
If app.Cells(i, j).Value <> "" then
i=i+1
else Exit For
end if
Next
row = i-1

Dim range
range = "A1:"&col&row

msgbox range
---------------------------

[kadghar]

Hi I have tried with the following code .............
But it only works when the data starts from A1..... otherwise it fails....

Working on your very code, try using a DO instead of a FOR, and use two variables...

dim row1 as long
dim row2 as long
 
row1=1
do
    if cells(row1,1) <>"" then exit do
    row1=row1+1
loop until row1 >= 65000 'i dont remember the number of rows in a sheet
row2 = row1+1
do
    if cells(row2,1) = "" then exit do
    row2=row2+1
loop until row2 >= 65000 'Same here

And i also recomend you to create a function that gives you back the Excel range, use ASCII code and the CHR and ASC functions for that instead of a case select.

HTH

**PS: remember that your range will go from row1 to (row2 - 1) since row2 is already an empty one.