K
Khalid
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 Where:=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
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 Where:=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