sorry, here's the rest of the code. I remind you that I'm
working with Access 2000 I don't know if these works with
97
----- start code -------------
Option Compare Database
Option Explicit
'Variables globales
Public gobjExcel As Excel.Application
Function CreateExcelObj() As Boolean
'Launch excel
On Error GoTo CreateExcelObj_Err
CreateExcelObj = False
Set gobjExcel = New Excel.Application
CreateExcelObj = True
CreateExcelObj_Exit:
Exit Function
CreateExcelObj_Err:
MsgBox "Couldn't Launch Excel!!",
vbCritical, "Warning"
CreateExcelObj = False
Resume CreateExcelObj_Exit
End Function
Function CreateRecordset(rstData As ADODB.Recordset,
strTableName As String)
On Error GoTo CreateRecordset_Err
rstData.CursorType = adOpenStatic
rstData.Open strTableName, Options:=adCmdTable
CreateRecordset = True
CreateRecordset_Exit:
Exit Function
CreateRecordset_Err:
MsgBox "Error " & Err.Number & ": " &
Err.Description
Resume CreateRecordset_Exit
End Function
////// here is part it's quite big, so i'm sending you
only a small part (the rest my confuse you and is useless
to your post) you only need to fix the if-else-end if and
with/end with's as well ///////////////////////
Sub SendToExcel(strQryName As String, intNo As Integer)
On Error GoTo Err_SendToExcel
Dim rstData As ADODB.Recordset
Dim rng As Excel.Range
Dim objWS As Excel.Worksheet
Dim strPath As String
Dim strSQL As String
Dim strFiltro As String
Set rstData = New ADODB.Recordset
rstData.ActiveConnection = CurrentProject.Connection
strPath = "I:\01\Drives\PIBDE General\Access
Dbases\Service\Reports\ServiceMeasurements.xls"
strSQL = "GenDataID Like " & Me.cboService
If (intNo = 1 Or intNo = 2 Or intNo = 3 Or intNo = 4 Or
intNo = 5) Then
If CreateRecordset(rstData, strQryName) Then
rstData.Filter = strSQL
If rstData.RecordCount = 0 Then ' el
recordset esta vacio
MsgBox "There is no data to
send!!", vbCritical, "Warning"
GoTo Salir_Sub
End If ' rstData count
If CreateExcelObj() Then
gobjExcel.Workbooks.Open
strPath
gobjExcel.Visible = True
Set objWS =
gobjExcel.ActiveSheet
objWS.Range
("A2").CopyFromRecordset rstData
'Dar formato
With gobjExcel
.Range("A1").Select
.ActiveCell.CurrentRegi
on.Select
Set rng = .Selection
rng.NumberFormat = "#,
##0.000"
'Crear la grafica
If (intNo = 1) Then
'Nominal Data
chart
.Columns
("A:B").Delete
.Columns
("E").Delete
.Range
("A1").Value = "MEASURE"
.Range
("B1").Value = "START 130% TORQUE"
.Range
("C1").Value = "RATED SPEED"
.Range
("D1").Value = "MAX. SPEED"
.Rows
(1).Font.Bold = True
.Rows
(1).Font.Color = 10040115
rng.EntireColumn.AutoFit
.Range
(rng.Address).Select
Set rng
= .Selection
.Charts.Add
'Chart Format
With
gobjExcel.ActiveChart
.PlotBy
= xlColumns
.Locati
on xlLocationAsNewSheet
.HasTit
le = True
With .ChartTitle
.Characters.Text = "Nominal Data"
.Font.FontStyle = "Tahoma"
.Font.Bold = True
.Font.Size = 16
End
With
With .ChartArea
.Border.Weight = 1
.Shadow = True
End
With
.Legend
..Font.Size = 8
.Legend
..Position = xlLegendPositionBottom
'
Rotate the x-axis labels to a 45-degree angle
.Axes
(xlCategory).TickLabels.Orientation = 45
' the
legend
.HasLeg
end = True
End With
////// the code above creates the recordset, filters it,
sends it to Excel to a specific cell, make a chart in a
new worksheet, gives a name to the Data sheet (where the
recordset was sent) and also to the Chart sheet /////
Now i'm leaving from office, but if you need any further
info please repost and gladly I will answer you tomorrow.
By the way I send a post in Queries newsgroup (jan 19
4:34am), maybe you can help me
thx.
-----Original Message-----
Thanks GR,
I think you might have left out some referenced functions
that I need to understand what is going on - eg
CreateExcelObj()?. I don't get all the goobj references
too? Further help required please...
