I am trying to draw a graph in Excel programmatically.
For this, I am importing data into Excel and then trying to draw
graph.
But my graph is not showing any graph it is empty except X and Y axis.

I tried to reformat the data cause I have notice the data in Excel
sheet is left justified since it is all number, it should be right
justified.
I high light the data and change the format from General to Number,
but no success.
Only two ways I can fix the problem.
1) Save the data in csv or test format and reopen the file, which will
fix this problem.
2) Manually go every cell double click the mouse and move to the next
cell which also fix the problem.

Is there any one out there who can help me in this problem?

I am pasting the code here if some one is interested to see what I am
going.

Sub PopViprWeeklyReport()

Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim sConn As String
Dim iCol As Integer
Set cn = New ADODB.Connection

l_dsn = "XYZ"
l_uid = "abc"
l_pwd = "abc"

cn.Open "DSN=" & l_dsn & ";UID=" & l_uid & ";PWD=" & l_pwd

sSQL = "select * from vipr_weekly_usage_tmp"

Set rs = cn.Execute(sSQL)

Debug.Print rs.RecordCount

' Copy field names to the first row of the worksheet
For iCol = 1 To rs.Fields.Count
ActiveSheet.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next

' Populate the data
ActiveSheet.Cells(2, 1).CopyFromRecordset rs
'ActiveWorkbook.SaveAs Filename:="D:\Work\NewReports\test.csv",
FileFormat:=xlCSV, CreateBackup:=False

rs.Close
Set rs = Nothing

Range("A1").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A1:H13"), PlotBy:=xlRows
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "VIPR Weekly Transaction
Records"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Dates"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values"
End With
ActiveChart.HasDataTable = False
'ActiveSheet.Shapes("Chart 4").IncrementLeft -192.75
'ActiveSheet.Shapes("Chart 4").IncrementTop 72.75
'ActiveSheet.Shapes("Chart 4").ScaleWidth 1.69, msoFalse,
msoScaleFromTopLeft
'ActiveSheet.Shapes("Chart 4").ScaleHeight 1.13, msoFalse,
msoScaleFromTopLeft
End Sub

Khalid -

Could you populate a VBA array with the recordset data, then loop through the array
to run this:

MyArray(i,j) = Val(MyArray(i,j))

to assure that the array is storing numerical values? You may need to use two
arrays, the original populated from the DB, and a second to hold the converted values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Khalid wrote:

I am trying to draw a graph in Excel programmatically.
For this, I am importing data into Excel and then trying to draw
graph.
But my graph is not showing any graph it is empty except X and Y axis.

I tried to reformat the data cause I have notice the data in Excel
sheet is left justified since it is all number, it should be right
justified.
I high light the data and change the format from General to Number,
but no success.
Only two ways I can fix the problem.
1) Save the data in csv or test format and reopen the file, which will
fix this problem.
2) Manually go every cell double click the mouse and move to the next
cell which also fix the problem.

Is there any one out there who can help me in this problem?

I am pasting the code here if some one is interested to see what I am
going.

Sub PopViprWeeklyReport()

Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim sConn As String
Dim iCol As Integer
Set cn = New ADODB.Connection

l_dsn = "XYZ"
l_uid = "abc"
l_pwd = "abc"

cn.Open "DSN=" & l_dsn & ";UID=" & l_uid & ";PWD=" & l_pwd

sSQL = "select * from vipr_weekly_usage_tmp"

Set rs = cn.Execute(sSQL)

Debug.Print rs.RecordCount

' Copy field names to the first row of the worksheet
For iCol = 1 To rs.Fields.Count
ActiveSheet.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next

' Populate the data
ActiveSheet.Cells(2, 1).CopyFromRecordset rs
'ActiveWorkbook.SaveAs Filename:="D:\Work\NewReports\test.csv",
FileFormat:=xlCSV, CreateBackup:=False

rs.Close
Set rs = Nothing

Range("A1").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A1:H13"), PlotBy:=xlRows
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "VIPR Weekly Transaction
Records"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Dates"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values"
End With
ActiveChart.HasDataTable = False
'ActiveSheet.Shapes("Chart 4").IncrementLeft -192.75
'ActiveSheet.Shapes("Chart 4").IncrementTop 72.75
'ActiveSheet.Shapes("Chart 4").ScaleWidth 1.69, msoFalse,
msoScaleFromTopLeft
'ActiveSheet.Shapes("Chart 4").ScaleHeight 1.13, msoFalse,
msoScaleFromTopLeft
End Sub

Jon Peltier wrote:

Could you populate a VBA array with the recordset data, then loop
through the array to run this:

MyArray(i,j) = Val(MyArray(i,j))

to assure that the array is storing numerical values?

Much easier to do the work on the server to cast the values as numeric.
I don't think the OP advised what the database product is (a DSN hides
so much) but if it is SQL-92 compliant e.g.

SELECT
CAST(my_text_col AS FLOAT) AS converted_numeric_col
FROM vipr_weekly_usage_tmp;

But if it is Jet then use the VBA5 cast functions e.g.

SELECT
CDbl(my_text_col) AS converted_numeric_col
FROM vipr_weekly_usage_tmp;

Jamie.

--