467,919 Members | 1,139 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,919 developers. It's quick & easy.

Create Dynamic Range in Excel


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
  • viewed: 2408
Share:

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by IMS.Rushikesh | last post: by
9 posts views Thread by Kishor | last post: by
3 posts views Thread by James Wong | last post: by
3 posts views Thread by Administrator | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.