By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Create Dynamic Range in Excel

P: n/a

The code below takes the data from the S column and grabs all the data
and graphs it. The problem is this example grabs 500 cells worth of
data and some of these cells = nothing. How do I rescope a range to
prevent the data that is = nothing not to display....

Dim app As New Excel.Application
' app.Visible = True
Dim Doc As Excel.Workbook =
app.Workbooks.Open("C:\Filename.csv")
Dim Sheet As Excel.Worksheet = Doc.Sheets(1)
app.DisplayAlerts = False

app.Range("S1:S500").Select()
Dim r As Excel.Range = app.Selection
Dim s As String
Dim int As Integer
For j As Integer = 1 To 500
s = r(j, 1).value
' txtImport.Text &= s & vbTab
If s = String.Empty Or s = Nothing Or s = " " Then
int = j
Exit For
End If
Next

Dim Chart As Excel.Chart = Doc.Charts.Add(, Sheet)
Chart.Name = "TX Graph"

Chart.ChartType = Excel.XlChartType.xlLine.xlLine
Chart.SetSourceData(Sheet.Range("S1:S400"))

' Copy the chart to a PictureBox on this form, using the
clipboard.
Chart.ChartArea.Select()
Chart.ChartArea.Copy()

' Retrieve the picture.
Dim ChartImage As Image =
CType(Clipboard.GetDataObject().GetData(DataFormat s.Bitmap), Bitmap)
ChartImage.Save("Test.jpg",
System.Drawing.Imaging.ImageFormat.Jpeg)

' Shrink the picture to fit the form.
Dim ScalingRatio As Single = PictureBox1.Width /
ChartImage.Width
ChartImage = ChartImage.GetThumbnailImage(ChartImage.Width *
ScalingRatio, ChartImage.Height * ScalingRatio, Nothing, Nothing)

PictureBox1.Image = ChartImage

' Modify the chart so it appears alongside the Excel table,
' instead of in a separate window.
' Chart.Location(Excel.XlChartLocation.xlLocationAsO bject,
Sheet)
app.Workbooks.Close()

Nov 21 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.