f



Opening Excel from Access 2000

I have a form with a TextBox that holds the path to an Excel File and a 
command button that opens the excel file. The Excel file basically contains 
about 5 maps, one on each worksheet scatter charts on the maps showing 
locations. I had to use Excel as I wanted each point of the scatter chart 
labeled and used Bob Bovey's Chart Labler addition to Excel.

The data for the scatter charts come from the database ( x & y positions and 
the appropriate labels
If the database is closed the Excel file opens perfectly every time. With 
the DB open again 100% success. However, if I open the file using the 
command button it appears to open OK and then 1 of 2 problems. As soon as I 
click the mouse on a chart, Excel crashes. If I click on a blank cell first 
and then click on the chart normally there is no problem but sometimes I get 
the message "The database has been placed in a state bu user 'admin' on 
machine 'ABC-DESKTOP' that prevents it being opened or locked" and then goes 
on to indicate the query linking the Excel to the DB won't run.

Here is the code

Private Sub Storage_Click()

    On Error GoTo Err_Storage_Click

    Dim ExcelApp As Object
    Dim ExcelWasNotRunning As Boolean           ' Flag for final release
    Dim XLFilePath As String
    Dim XLPath As String                        ' Excel path name from Paths
    Dim XLName As String                        ' Excel file name from Paths
    Dim Mydb As Database
    Dim Msg As String
    Dim QCoInfoPathsSet As Recordset

    ' Find the normal path
    Set Mydb = CurrentDb
    Set QCoInfoPathsSet = Mydb.OpenRecordset("QCoInfoPaths")
    With QCoInfoPathsSet
        .MoveFirst
        XLPath = !ExcelPath
        XLName = Right(XLPath, Len(XLPath) - InStrRev(XLPath, "\"))

        If Dir(ExcelFileName) <> XLName Then          ' Not found
            XLFilePath = FindFile("C:\Documents and Settings\Phil\My 
Documents\Access\MDB\WFYC\", ExcelFileName)
            Msg = "The name of the file you have selected is " & vbCrLf
            Msg = Msg & XLFilePath & vbCrLf
            Msg = Msg & "but the original file was " & vbCrLf
            Msg = Msg & XLPath & vbCrLf
            Msg = Msg & "Do you want to use the new name in future?"
            If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
                .Edit
                !ExcelPath = XLFilePath
                .Update
            End If
            ExcelFileName = XLFilePath
        End If
        .Close
    End With
    Set QCoInfoPathsSet = Nothing
    Set Mydb = Nothing

    If ExcelFileName.DefaultValue <> ExcelFileName Then
        ExcelFileName.DefaultValue = ExcelFileName
        DoCmd.Save acForm, Me.Name
    End If

    '*************** The bit above is just to check the path is OK
    On Error Resume Next
    Set ExcelApp = CreateObject("Excel.Application")
    'ExcelApp.Visible = True

    If Err.Number <> 0 Then ExcelWasNotRunning = True
    Err.Clear    ' Clear Err object in case error occurred.

    ' Set the object variable to reference the file you want to see.
    Set ExcelApp = GetObject(ExcelFileName)

    ' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the ExcelApp object reference.
    ExcelApp.Application.Visible = True
    ExcelApp.Parent.Windows(1).Visible = True

Exit_Storage_Click:
    Exit Sub

Err_Storage_Click:
    MsgBox Err.Description
    Resume Exit_Storage_Click

End Sub


Any Ideas gratefully received

Thanks

Phil



0
phil (1005)
8/14/2007 9:16:41 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

10 Replies
919 Views

Similar Articles

[PageSpeed] 25

3 thoughts...
 1. Simplify the system until the error goes away. For example, modify
your code to open a different (perhaps empty) .xls file and see if
still crashes. If it doesn't crash, then slowly add functionality back
to the .xls file until you start crashing again. That might help you
isolate and identify the problem.

 2. Have you separated your code and data mdbs?  Just a wild guess,
but you might want to have Excel read from a different mdb from the
one with the code in it. Perhaps that will alleviate what appears to
be some type of locking problem.

 3.  Worst case: export the data from your mdb before Excel consumes
it. Put the relevant data into a CSV or even an .xls file, then start
Excel and run your maps.

Bill


On Aug 14, 5:16 pm, "Phil Stanton" <p...@stantonfamily.co.uk> wrote:
> I have a form with a TextBox that holds the path to an Excel File and a
> command button that opens the excel file. The Excel file basically contains
> about 5 maps, one on each worksheet scatter charts on the maps showing
> locations. I had to use Excel as I wanted each point of the scatter chart
> labeled and used Bob Bovey's Chart Labler addition to Excel.
>
> The data for the scatter charts come from the database ( x & y positions and
> the appropriate labels
> If the database is closed the Excel file opens perfectly every time. With
> the DB open again 100% success. However, if I open the file using the
> command button it appears to open OK and then 1 of 2 problems. As soon as I
> click the mouse on a chart, Excel crashes. If I click on a blank cell first
> and then click on the chart normally there is no problem but sometimes I get
> the message "The database has been placed in a state bu user 'admin' on
> machine 'ABC-DESKTOP' that prevents it being opened or locked" and then goes
> on to indicate the query linking the Excel to the DB won't run.
>
> Here is the code
>
> Private Sub Storage_Click()
>
>     On Error GoTo Err_Storage_Click
>
>     Dim ExcelApp As Object
>     Dim ExcelWasNotRunning As Boolean           ' Flag for final release
>     Dim XLFilePath As String
>     Dim XLPath As String                        ' Excel path name from Paths
>     Dim XLName As String                        ' Excel file name from Paths
>     Dim Mydb As Database
>     Dim Msg As String
>     Dim QCoInfoPathsSet As Recordset
>
>     ' Find the normal path
>     Set Mydb = CurrentDb
>     Set QCoInfoPathsSet = Mydb.OpenRecordset("QCoInfoPaths")
>     With QCoInfoPathsSet
>         .MoveFirst
>         XLPath = !ExcelPath
>         XLName = Right(XLPath, Len(XLPath) - InStrRev(XLPath, "\"))
>
>         If Dir(ExcelFileName) <> XLName Then          ' Not found
>             XLFilePath = FindFile("C:\Documents and Settings\Phil\My
> Documents\Access\MDB\WFYC\", ExcelFileName)
>             Msg = "The name of the file you have selected is " & vbCrLf
>             Msg = Msg & XLFilePath & vbCrLf
>             Msg = Msg & "but the original file was " & vbCrLf
>             Msg = Msg & XLPath & vbCrLf
>             Msg = Msg & "Do you want to use the new name in future?"
>             If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
>                 .Edit
>                 !ExcelPath = XLFilePath
>                 .Update
>             End If
>             ExcelFileName = XLFilePath
>         End If
>         .Close
>     End With
>     Set QCoInfoPathsSet = Nothing
>     Set Mydb = Nothing
>
>     If ExcelFileName.DefaultValue <> ExcelFileName Then
>         ExcelFileName.DefaultValue = ExcelFileName
>         DoCmd.Save acForm, Me.Name
>     End If
>
>     '*************** The bit above is just to check the path is OK
>     On Error Resume Next
>     Set ExcelApp = CreateObject("Excel.Application")
>     'ExcelApp.Visible = True
>
>     If Err.Number <> 0 Then ExcelWasNotRunning = True
>     Err.Clear    ' Clear Err object in case error occurred.
>
>     ' Set the object variable to reference the file you want to see.
>     Set ExcelApp = GetObject(ExcelFileName)
>
>     ' Show Microsoft Excel through its Application property. Then
> ' show the actual window containing the file using the Windows
> ' collection of the ExcelApp object reference.
>     ExcelApp.Application.Visible = True
>     ExcelApp.Parent.Windows(1).Visible = True
>
> Exit_Storage_Click:
>     Exit Sub
>
> Err_Storage_Click:
>     MsgBox Err.Description
>     Resume Exit_Storage_Click
>
> End Sub
>
> Any Ideas gratefully received
>
> Thanks
>
> Phil


0
8/17/2007 12:46:29 PM
Thanks for your thoughts, Bill.

With regard to point 2, the data is held in a BE database while the query 
that "powers" the Excel file is in the FE using linked tables. The Excel 
file opens fine by opening Excel directly and then opening the correct 
workbook, but it would just be nice to open it from a command button in the 
FE database.

Will try simplyfying things

Thanks again

Phil


"Bill Nicholson - Cincinnati, OH" <namewitheldbyrequest@gmail.com> wrote in 
message news:1187354789.167487.147600@l22g2000prc.googlegroups.com...
>3 thoughts...
> 1. Simplify the system until the error goes away. For example, modify
> your code to open a different (perhaps empty) .xls file and see if
> still crashes. If it doesn't crash, then slowly add functionality back
> to the .xls file until you start crashing again. That might help you
> isolate and identify the problem.
>
> 2. Have you separated your code and data mdbs?  Just a wild guess,
> but you might want to have Excel read from a different mdb from the
> one with the code in it. Perhaps that will alleviate what appears to
> be some type of locking problem.
>
> 3.  Worst case: export the data from your mdb before Excel consumes
> it. Put the relevant data into a CSV or even an .xls file, then start
> Excel and run your maps.
>
> Bill
>
>
> On Aug 14, 5:16 pm, "Phil Stanton" <p...@stantonfamily.co.uk> wrote:
>> I have a form with a TextBox that holds the path to an Excel File and a
>> command button that opens the excel file. The Excel file basically 
>> contains
>> about 5 maps, one on each worksheet scatter charts on the maps showing
>> locations. I had to use Excel as I wanted each point of the scatter chart
>> labeled and used Bob Bovey's Chart Labler addition to Excel.
>>
>> The data for the scatter charts come from the database ( x & y positions 
>> and
>> the appropriate labels
>> If the database is closed the Excel file opens perfectly every time. With
>> the DB open again 100% success. However, if I open the file using the
>> command button it appears to open OK and then 1 of 2 problems. As soon as 
>> I
>> click the mouse on a chart, Excel crashes. If I click on a blank cell 
>> first
>> and then click on the chart normally there is no problem but sometimes I 
>> get
>> the message "The database has been placed in a state bu user 'admin' on
>> machine 'ABC-DESKTOP' that prevents it being opened or locked" and then 
>> goes
>> on to indicate the query linking the Excel to the DB won't run.
>>
>> Here is the code
>>
>> Private Sub Storage_Click()
>>
>>     On Error GoTo Err_Storage_Click
>>
>>     Dim ExcelApp As Object
>>     Dim ExcelWasNotRunning As Boolean           ' Flag for final release
>>     Dim XLFilePath As String
>>     Dim XLPath As String                        ' Excel path name from 
>> Paths
>>     Dim XLName As String                        ' Excel file name from 
>> Paths
>>     Dim Mydb As Database
>>     Dim Msg As String
>>     Dim QCoInfoPathsSet As Recordset
>>
>>     ' Find the normal path
>>     Set Mydb = CurrentDb
>>     Set QCoInfoPathsSet = Mydb.OpenRecordset("QCoInfoPaths")
>>     With QCoInfoPathsSet
>>         .MoveFirst
>>         XLPath = !ExcelPath
>>         XLName = Right(XLPath, Len(XLPath) - InStrRev(XLPath, "\"))
>>
>>         If Dir(ExcelFileName) <> XLName Then          ' Not found
>>             XLFilePath = FindFile("C:\Documents and Settings\Phil\My
>> Documents\Access\MDB\WFYC\", ExcelFileName)
>>             Msg = "The name of the file you have selected is " & vbCrLf
>>             Msg = Msg & XLFilePath & vbCrLf
>>             Msg = Msg & "but the original file was " & vbCrLf
>>             Msg = Msg & XLPath & vbCrLf
>>             Msg = Msg & "Do you want to use the new name in future?"
>>             If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
>>                 .Edit
>>                 !ExcelPath = XLFilePath
>>                 .Update
>>             End If
>>             ExcelFileName = XLFilePath
>>         End If
>>         .Close
>>     End With
>>     Set QCoInfoPathsSet = Nothing
>>     Set Mydb = Nothing
>>
>>     If ExcelFileName.DefaultValue <> ExcelFileName Then
>>         ExcelFileName.DefaultValue = ExcelFileName
>>         DoCmd.Save acForm, Me.Name
>>     End If
>>
>>     '*************** The bit above is just to check the path is OK
>>     On Error Resume Next
>>     Set ExcelApp = CreateObject("Excel.Application")
>>     'ExcelApp.Visible = True
>>
>>     If Err.Number <> 0 Then ExcelWasNotRunning = True
>>     Err.Clear    ' Clear Err object in case error occurred.
>>
>>     ' Set the object variable to reference the file you want to see.
>>     Set ExcelApp = GetObject(ExcelFileName)
>>
>>     ' Show Microsoft Excel through its Application property. Then
>> ' show the actual window containing the file using the Windows
>> ' collection of the ExcelApp object reference.
>>     ExcelApp.Application.Visible = True
>>     ExcelApp.Parent.Windows(1).Visible = True
>>
>> Exit_Storage_Click:
>>     Exit Sub
>>
>> Err_Storage_Click:
>>     MsgBox Err.Description
>>     Resume Exit_Storage_Click
>>
>> End Sub
>>
>> Any Ideas gratefully received
>>
>> Thanks
>>
>> Phil
>
> 


0
phil (1005)
8/18/2007 7:46:48 AM
Phil from what I see, you using Access just to open an Excel file?
One thing - if you open Excel from within Access, how do you clean
up?
Does the user eventually quit the Excel App?
>From within Access, you should still eventually have something like
ExcelApp.Quit
Set ExcelApp = nothing

If you just want to start up the Excel app, you don't have to
instantiate an object, you could use fHandleFile. (available on the
Access Web I think - Dev Ashish wrote it)  It will open Excel in a
separate session.

If you still have problems, I do scatter charts successfully  - with
VBA code for adding labels - differently.  I open a new blank Excel
session, push the data down, then drive Excel from within Access to
produce the chart, then quit Excel and reopen the XLS with
fHandleFile.

Get back to me if you're still stuck, I'll post details

Terry Bell

0
8/18/2007 8:32:31 AM
Thanks Terry.

Will give fHandleFile a try.

Have noted your remarks about quitting Excel. Normally the user closes the 
Excel application manually, so guess they will not be required, but will 
incorporate them into the OnClose of the form anyway.

In the mean time I would be very interested to see any code / examples of 
your chart labelling routines. What I am doing is overlaying the location of 
boats on a map. Rob Bovey's XY Chart Labeller in Excel allows me to attach a 
lable for each point giving the name and owner of the boat. Also allows you 
to select font, orientation and position relative to the point in question. 
Would love to do that in Access rather than having to leave Access and start 
Excel.

Thanks for coming back

Phil

<dreadnought8@hotmail.com> wrote in message 
news:1187425951.348763.24390@i38g2000prf.googlegroups.com...
> Phil from what I see, you using Access just to open an Excel file?
> One thing - if you open Excel from within Access, how do you clean
> up?
> Does the user eventually quit the Excel App?
>>From within Access, you should still eventually have something like
> ExcelApp.Quit
> Set ExcelApp = nothing
>
> If you just want to start up the Excel app, you don't have to
> instantiate an object, you could use fHandleFile. (available on the
> Access Web I think - Dev Ashish wrote it)  It will open Excel in a
> separate session.
>
> If you still have problems, I do scatter charts successfully  - with
> VBA code for adding labels - differently.  I open a new blank Excel
> session, push the data down, then drive Excel from within Access to
> produce the chart, then quit Excel and reopen the XLS with
> fHandleFile.
>
> Get back to me if you're still stuck, I'll post details
>
> Terry Bell
> 


0
phil (1005)
8/19/2007 3:55:23 PM
"Phil Stanton" <phil@stantonfamily.co.uk> wrote in message 
news:13cgpv5qal7ku80@corp.supernews.com...
>
> In the mean time I would be very interested to see any code / examples of 
> your chart labelling routines. What I am doing is overlaying the location 
> of boats on a map. Rob Bovey's XY Chart Labeller in Excel allows me to 
> attach a lable for each point giving the name and owner of the boat. Also 
> allows you to select font, orientation and position relative to the point 
> in question. Would love to do that in Access rather than having to leave 
> Access and start Excel.
>

Here's an example of code I use to manipulate graphs in Excel from Access. 
You might be able to adapt some of it.  It uses a diaog box to choose the 
Excel file.

Keith.
www.keithwilby.com

On Error GoTo err_trap

DoCmd.Hourglass True

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant

With fd
    .AllowMultiSelect = False
    .Filters.Add "Excel files", "*.xls"
    .Title = "Select an Excel file to populate."
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
        strFilePath = vrtSelectedItem
        If Right(strFilePath, 3) <> "xls" Then
            MsgBox "The file chosen is not an Excel file. Please choose a 
valid Excel file.", vbCritical, "File type error."
            Exit Sub
        End If
        Next vrtSelectedItem

            'Set up variables
            Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String, 
intRowNumber As Integer

            'Excel
            Dim objXL As Excel.Application
            Dim objWkb As Excel.Workbook
            Dim objSht As Excel.Worksheet

            'Open the Excel spreadsheet if it is not already open
            If Not IsXLBookOpen(strFilePath) Then
                Set objXL = New Excel.Application
                Set objWkb = objXL.Workbooks.Open(strFilePath)
            Else
                Set objXL = Excel.Application
                Set objWkb = objXL.ActiveWorkbook
            End If

            objXL.Visible = False

            'Add a sheet if there is not one with the required name in the 
workbook
            If libSheetExists(objWkb, objSht, "MySheet") = False Then
                objXL.Sheets.Add
                objXL.ActiveSheet.Name = "MySheet"
            End If

            Set objSht = objWkb.Worksheets("MySheet")
            objSht.Activate

            'Clear existing data from spreadsheet
            Dim cell As Range
            Set cell = objSht.Range("AI2")

            If Not IsEmpty(cell.Offset(1, 0)) Then
                 With objSht.Cells.Range("AI2", 
objSht.Range("AI2").End(xlDown))
                    .Select
                    .Clear
                End With
            End If

            Set cell = objSht.Range("AJ2")

            If Not IsEmpty(cell.Offset(1, 0)) Then
                 With objSht.Cells.Range("AJ2", 
objSht.Range("AJ2").End(xlDown))
                    .Select
                    .Clear
                End With
            End If

            Err.Clear

            'Add a chart if necessary

            On Error Resume Next 'Error number is used in this block

            objSht.ChartObjects(1).Select
            If Err <> 0 And objSht.Type <> 4 Then
                objXL.Charts.Add
                objXL.ActiveChart.Location WHERE:=xlLocationAsObject, 
Name:="MySheet"
            Else
            End If
            Err = 0

            On Error GoTo err_trap

            'Set up and run the query
            Set db = CurrentDb
            strSQL = "Select * from qryMyQuery"
            Set rs = db.OpenRecordset(strSQL)

            intRowNumber = 2

            'Add the data to the workbook
            'Data is added way over to the right so that it is out of sight 
when viewing the graph

            'Legends
            objSht.Cells(1, 35).Value = "WeekNo"
            objSht.Cells(1, 36).Value = "Count"

            'ECR counts
            With rs
                'check that there are some records
                If .BOF = False And .EOF = False Then
                    .MoveFirst
                    Do While rs.EOF = False
                        objSht.Cells(intRowNumber, 35).Value = 
rs.Fields("WeekNo").Value
                        objSht.Cells(intRowNumber, 36).Value = 
rs.Fields("CountOfECR_NO").Value
                        rs.MoveNext
                        intRowNumber = intRowNumber + 1
                    Loop
                End If
            End With

            'Tell Excel where to put the graph and give it formatting 
instructions
            objXL.ActiveChart.ChartType = xlLine
            objXL.ActiveChart.SetSourceData 
Source:=objXL.Sheets("MySheet").Range("AJ2", 
objSht.Range("AJ2").End(xlDown))
            objXL.ActiveChart.SeriesCollection(1).XValues = 
objSht.Range("AI2", objSht.Range("AI2").End(xlDown))
            objXL.ActiveChart.Location WHERE:=xlLocationAsObject, 
Name:="Metric"
            With objXL.ActiveChart
                .HasTitle = True
                .ChartTitle.Characters.Text = "My Title"
                .Axes(xlCategory, xlPrimary).HasTitle = True
                .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = 
"Week No"
                .Axes(xlValue, xlPrimary).HasTitle = False
                .PlotArea.Interior.ColorIndex = xlNone
            End With

            'Save the workbook
            objXL.ActiveWorkbook.Save

            DoCmd.Hourglass False

            'Give the user the option to view the workbook
            If MsgBox("Data exported. Do you want to view the graph?", 
vbYesNo, "Data exported") = vbNo Then
                objXL.Quit
            Else
                objSht.Activate
                objXL.Visible = True
            End If

            'TidyUp
            Set objSht = Nothing
            Set objWkb = Nothing
            Set objXL = Nothing

            rs.Close
            Set rs = Nothing
            Set db = Nothing

    'The user pressed Cancel.
    Else
        If MsgBox("You did not select a file. Do you want to cancel the 
operation?", _
            vbYesNo + vbQuestion, "No File Selected") = vbYes Then
            Set fd = Nothing
            DoCmd.Hourglass False
            Exit Sub
        Else
            Call cmdExport_Click
        End If
    End If
End With

Set fd = Nothing

Exit Sub

err_trap:
If Err.Number = 91 Then Resume Next
If Err.Number = 462 Then Resume Next
If Err.Number = 1004 Then Resume Next

MsgBox "An unexpected error occurred and the data may be invalid." & vbCrLf 
& "Error " & Err.Number & vbCrLf & Err.Description

objXL.Quit
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing


0
here9 (1031)
8/20/2007 7:25:58 AM
Here's my final code after further work.  It opens a form where the
user selects two columns; then creates a temp table with the two
columns, puts the data in a new Excel session, and produces the chart.
I don't use fHandleFile any more.  When the function completes,  the
user is dropped into Excel to view/save/refine the chart at will, or
click back on the Access session, or simply close the Excel session,
to continue.  This way they can produce many charts, each in a
separate session with different columns.
Good luck, sounds like an interesting job, beats Accounting
applications ...
Terry Bell
Function ScatterChart()
Dim frm As Form, rst As Recordset, tempRiskData As Recordset, Ctl As
Control
Dim LastBarChartPath, ExcelFileName As String
Dim ColumnSet As String, Column1 As String, Column2 As String, j As
Integer
'Choose one
'-------------------------------------------------------------------------------------
    Dim ObjXL As Object                    'Late binding
     'Dim ObjXL As Excel.Application         'Early binding - use for
debugging for intellisense
'
'   VERY IMPORTANT
'   If Copying vba from Excel code, include the ObjXL. prefix to all
references, if you have Excel registered as a reference
'   If you don't, Access still executes, but the Excel session will
hang and you'll have to kill it ...
'-------------------------------------------------------------------------------------
Dim intMaxCol As Integer, SQL, WorksheetNumber As Integer
Dim intMaxRow As Integer, StartingRow As Integer, StartingColumn As
Integer, EndingRow As Integer, EndingColumn As Integer
Dim ThisPageRecordSource As Recordset
Dim rs As Recordset, ProdGroupName, OwnerName
Dim SpreadsheetUnit As Recordset
Dim RowCount, i
Dim ColCount
Dim Dummy
Dim TheLabel As String
Dim ChartTitle As String, XTitle As String, YTitle As String,
MarkerSize
Dim YAxisOrder
If NotViewingFrmEcoData Then Exit Function

On Error GoTo ErrorTrap

'Check for blank rows

'Get the two chosen columns for graphing
DoCmd.OpenForm "frmChooseScoreColumns", , , , , acDialog, 2
If Not IsLoaded("frmChooseScoreColumns") Then Exit Function

Column1 = Form_frmChooseScoreColumns.cboXAxisVariable
Column2 = Form_frmChooseScoreColumns.cboYAxisVariable
ChartTitle = NZ(Form_frmChooseScoreColumns.txtChartTitle, "")
XTitle = NZ(Form_frmChooseScoreColumns.txtXTitle, "")
YTitle = NZ(Form_frmChooseScoreColumns.txtYTitle, "")
MarkerSize = Form_frmChooseScoreColumns.cboMarkerSize
YAxisOrder = Form_frmChooseScoreColumns.frmYAxisOrder
DoCmd.Close acForm, "frmChooseScoreColumns"

Set frm = Forms("frmEcoData")!frmEcoDataWorksub.Form
Set rst = frm.RecordsetClone

'Get the two columns to be charted out of the recordsetclone and store
in temp table
Buildtemptable "tempRiskData", "Select cdbl(0) as " & Column1 &
",cdbl(0) as " & Column2 & ", '' as Description from tblSystem where
false"
Set tempRiskData = CurrentDb().OpenRecordset("tempRiskData")
If rst.RecordCount > 255 Then
    MsgBox "Too many points" & vbCrLf & vbCrLf & "This chart plots
each point as a separate series, and there is a maximum of 255"
    GetRidOf rst
    Exit Function
End If
With tempRiskData
rst.MoveFirst
While Not rst.EOF
    If Not IsNull(rst.Fields(Column1)) And Not
IsNull(rst.Fields(Column2)) Then
        .AddNew
        tempRiskData.Fields(Column1) = rst.Fields(Column1)
        tempRiskData.Fields(Column2) = rst.Fields(Column2)
        tempRiskData!Description = rst!Description
        .Update
    End If
    rst.MoveNext
Wend
End With
GetRidOf rst, tempRiskData

Set rs = CurrentDb().OpenRecordset("SELECT * from tempRiskData")
If rs.EOF Then
    MsgBox "No Data!  Note: null values are ignored"
    Exit Function
End If

'The data is in table tempRiskData, now chart it

ShowMsg "Preparing the chart ..."
Set ObjXL = CreateObject("Excel.Application")       'Open Excel
'ObjXL.Visible = True
ObjXL.Application.DisplayAlerts = False
ObjXL.Workbooks.Add
ObjXL.ActiveSheet.PageSetup.PrintGridlines = True
ObjXL.Sheets("Sheet1").Select
ObjXL.ActiveSheet.Name = "Scatter Chart"
intMaxCol = rs.Fields.Count
rs.MoveLast
rs.MoveFirst
StartingRow = 2
StartingColumn = 1
EndingRow = 1 + rs.RecordCount
EndingColumn = rs.Fields.Count - 1 'Not the description

'Copy the data into the spreadsheet into rows 2 on
ObjXL.Range(ObjXL.Cells(StartingRow, StartingColumn),
ObjXL.Cells(EndingRow, EndingColumn)).CopyFromRecordset rs

'Put the headings in column 1
With ObjXL
    For i = 0 To rs.Fields.Count - 1
        .Worksheets(1).Cells(1, i + 1).Value = rs.Fields(i).Name
        .Columns(i + 1).Select
        .Selection.NumberFormat = "General"
    Next
End With

RowCount = rs.RecordCount
ColCount = rs.Fields.Count - 1
rs.Close
Set rs = Nothing
'The data is in the spreadsheet, now chart it
With ObjXL
    .Charts.Add
    .ActiveChart.LOCATION Where:=2, Name:="Scatter
Chart"                                    '2 = xlLocationAsObject
    .ActiveChart.ChartType = -4169  'xlXYScatter
    .ActiveSheet.Shapes("Chart 1").ScaleWidth 1.42, 0, 2
'msoScaleFromBottomRight
    .ActiveSheet.Shapes("Chart 1").ScaleHeight 1.4, 0, 0
'msoScaleFromTopLeft
    .ActiveSheet.Shapes("Chart 1").ScaleWidth 1.22, 0, 0
'msoScaleFromTopLeft

    .ActiveChart.ChartArea.Select
    .Selection.AutoScaleFont = True
    .Selection.Font.Name = "Times New Roman"
    .Selection.Font.Size = 12

    'Add a series for each row in the recordset.
    For i = 1 To RowCount
        .ActiveChart.SeriesCollection.NewSeries
        .ActiveChart.SeriesCollection(i).XValues = "='Scatter Chart'!
R" & i + 1 & "C1"
        .ActiveChart.SeriesCollection(i).Values = "='Scatter Chart'!R"
& i + 1 & "C2"
        .ActiveChart.SeriesCollection(i).Name = "='Scatter Chart'!R" &
i + 1 & "C3"
        .ActiveChart.SeriesCollection(i).ApplyDataLabels Type:=4,
AutoText:=True, LegendKey:=False
        'This bit adds a label to each point as it goes in
        .ActiveChart.SeriesCollection(i).DataLabels.Select
        .ActiveChart.SeriesCollection(i).Points(1).DataLabel.Select
        .Selection.Characters.text
= .ActiveChart.SeriesCollection(i).Name
         ShowMsg "Processing " & .ActiveChart.SeriesCollection(i).Name
        .Selection.HorizontalAlignment = -4131 'xlHAlignLeft
        'This bit sets the colour of the label to the colour of the
Marker
        .ActiveChart.SeriesCollection(i).Points(1).MarkerBackgroundColorIndex
= 3 + (i Mod 53)    'recycle after 53 or crash
        .Selection.Font.ColorIndex = 3 + (i Mod 53)
        .ActiveSheet.Range("C" & i + 1).Font.ColorIndex = 3 + (i Mod
53)
        .ActiveChart.SeriesCollection(i).MarkerSize = MarkerSize
    Next
    .ActiveChart.Legend.Select
    .Selection.Delete   'Don't want a legend with point labels

    'Formatting
    .ActiveChart.HasTitle = True
    .ActiveChart.ChartTitle.Characters.text = ChartTitle
    .ActiveChart.Axes(1, 1).HasTitle = True
    .ActiveChart.Axes(1, 1).AxisTitle.Characters.text = XTitle  'from
the form
    .ActiveChart.Axes(2, 1).HasTitle = True
    .ActiveChart.Axes(2, 1).AxisTitle.Characters.text = YTitle  'from
the form

    If Len(ChartTitle) > 0 Then
        .ActiveChart.ChartTitle.AutoScaleFont = True
        .ActiveChart.ChartTitle.Font.Name = "Times New Roman"
        .ActiveChart.ChartTitle.Font.FontStyle = "Bold"
        .ActiveChart.ChartTitle.Font.Size = 14
    End If


    If YAxisOrder = 1 Then          'from the form
        'Reverse the values on the Y Axis
        .ActiveChart.Axes(2).ReversePlotOrder = True
    End If

    'Set quadrant style cross hairs

    .ActiveChart.Axes(1).HasMajorGridlines = True
    .ActiveChart.Axes(1).MajorUnit
= .ActiveChart.Axes(1).MaximumScale / 2
    .ActiveChart.Axes(1).HasMinorGridlines = False

    .ActiveChart.Axes(2).HasMajorGridlines = True
    .ActiveChart.Axes(2).MajorUnit
= .ActiveChart.Axes(2).MaximumScale / 2
    .ActiveChart.Axes(2).HasMinorGridlines = False

    'Move the chart to better position
    .ActiveChart.PlotArea.Left = 22
    .ActiveChart.PlotArea.Top = 38
    .ActiveChart.Axes(1).AxisTitle.Left = 336
    .ActiveChart.Axes(1).AxisTitle.Top = 429

End With

ObjXL.Visible = True
Set ObjXL = Nothing     'This leaves user viewing the chart

ShowMsgOff

ExitIt:
    Exit Function
ErrorTrap:
    Dim msg
    msg = Err.Number & " " & Err.Description
    LogErr Err.Number, Err.Description, "ScatterChart"
    Resume ExitIt
    Resume
End Function

0
8/21/2007 9:56:35 AM
Works a treat. Terry. Is it possible to do the same thing with the graph 
object winin Access? I am using A2k so it is a graph9 object.

Thanks again

Phil


<dreadnought8@hotmail.com> wrote in message 
news:1187690195.834009.237800@x35g2000prf.googlegroups.com...
> Here's my final code after further work.  It opens a form where the
> user selects two columns; then creates a temp table with the two
> columns, puts the data in a new Excel session, and produces the chart.
> I don't use fHandleFile any more.  When the function completes,  the
> user is dropped into Excel to view/save/refine the chart at will, or
> click back on the Access session, or simply close the Excel session,
> to continue.  This way they can produce many charts, each in a
> separate session with different columns.
> Good luck, sounds like an interesting job, beats Accounting
> applications ...
> Terry Bell
> Function ScatterChart()
> Dim frm As Form, rst As Recordset, tempRiskData As Recordset, Ctl As
> Control
> Dim LastBarChartPath, ExcelFileName As String
> Dim ColumnSet As String, Column1 As String, Column2 As String, j As
> Integer
> 'Choose one
> '-------------------------------------------------------------------------------------
>    Dim ObjXL As Object                    'Late binding
>     'Dim ObjXL As Excel.Application         'Early binding - use for
> debugging for intellisense
> '
> '   VERY IMPORTANT
> '   If Copying vba from Excel code, include the ObjXL. prefix to all
> references, if you have Excel registered as a reference
> '   If you don't, Access still executes, but the Excel session will
> hang and you'll have to kill it ...
> '-------------------------------------------------------------------------------------
> Dim intMaxCol As Integer, SQL, WorksheetNumber As Integer
> Dim intMaxRow As Integer, StartingRow As Integer, StartingColumn As
> Integer, EndingRow As Integer, EndingColumn As Integer
> Dim ThisPageRecordSource As Recordset
> Dim rs As Recordset, ProdGroupName, OwnerName
> Dim SpreadsheetUnit As Recordset
> Dim RowCount, i
> Dim ColCount
> Dim Dummy
> Dim TheLabel As String
> Dim ChartTitle As String, XTitle As String, YTitle As String,
> MarkerSize
> Dim YAxisOrder
> If NotViewingFrmEcoData Then Exit Function
>
> On Error GoTo ErrorTrap
>
> 'Check for blank rows
>
> 'Get the two chosen columns for graphing
> DoCmd.OpenForm "frmChooseScoreColumns", , , , , acDialog, 2
> If Not IsLoaded("frmChooseScoreColumns") Then Exit Function
>
> Column1 = Form_frmChooseScoreColumns.cboXAxisVariable
> Column2 = Form_frmChooseScoreColumns.cboYAxisVariable
> ChartTitle = NZ(Form_frmChooseScoreColumns.txtChartTitle, "")
> XTitle = NZ(Form_frmChooseScoreColumns.txtXTitle, "")
> YTitle = NZ(Form_frmChooseScoreColumns.txtYTitle, "")
> MarkerSize = Form_frmChooseScoreColumns.cboMarkerSize
> YAxisOrder = Form_frmChooseScoreColumns.frmYAxisOrder
> DoCmd.Close acForm, "frmChooseScoreColumns"
>
> Set frm = Forms("frmEcoData")!frmEcoDataWorksub.Form
> Set rst = frm.RecordsetClone
>
> 'Get the two columns to be charted out of the recordsetclone and store
> in temp table
> Buildtemptable "tempRiskData", "Select cdbl(0) as " & Column1 &
> ",cdbl(0) as " & Column2 & ", '' as Description from tblSystem where
> false"
> Set tempRiskData = CurrentDb().OpenRecordset("tempRiskData")
> If rst.RecordCount > 255 Then
>    MsgBox "Too many points" & vbCrLf & vbCrLf & "This chart plots
> each point as a separate series, and there is a maximum of 255"
>    GetRidOf rst
>    Exit Function
> End If
> With tempRiskData
> rst.MoveFirst
> While Not rst.EOF
>    If Not IsNull(rst.Fields(Column1)) And Not
> IsNull(rst.Fields(Column2)) Then
>        .AddNew
>        tempRiskData.Fields(Column1) = rst.Fields(Column1)
>        tempRiskData.Fields(Column2) = rst.Fields(Column2)
>        tempRiskData!Description = rst!Description
>        .Update
>    End If
>    rst.MoveNext
> Wend
> End With
> GetRidOf rst, tempRiskData
>
> Set rs = CurrentDb().OpenRecordset("SELECT * from tempRiskData")
> If rs.EOF Then
>    MsgBox "No Data!  Note: null values are ignored"
>    Exit Function
> End If
>
> 'The data is in table tempRiskData, now chart it
>
> ShowMsg "Preparing the chart ..."
> Set ObjXL = CreateObject("Excel.Application")       'Open Excel
> 'ObjXL.Visible = True
> ObjXL.Application.DisplayAlerts = False
> ObjXL.Workbooks.Add
> ObjXL.ActiveSheet.PageSetup.PrintGridlines = True
> ObjXL.Sheets("Sheet1").Select
> ObjXL.ActiveSheet.Name = "Scatter Chart"
> intMaxCol = rs.Fields.Count
> rs.MoveLast
> rs.MoveFirst
> StartingRow = 2
> StartingColumn = 1
> EndingRow = 1 + rs.RecordCount
> EndingColumn = rs.Fields.Count - 1 'Not the description
>
> 'Copy the data into the spreadsheet into rows 2 on
> ObjXL.Range(ObjXL.Cells(StartingRow, StartingColumn),
> ObjXL.Cells(EndingRow, EndingColumn)).CopyFromRecordset rs
>
> 'Put the headings in column 1
> With ObjXL
>    For i = 0 To rs.Fields.Count - 1
>        .Worksheets(1).Cells(1, i + 1).Value = rs.Fields(i).Name
>        .Columns(i + 1).Select
>        .Selection.NumberFormat = "General"
>    Next
> End With
>
> RowCount = rs.RecordCount
> ColCount = rs.Fields.Count - 1
> rs.Close
> Set rs = Nothing
> 'The data is in the spreadsheet, now chart it
> With ObjXL
>    .Charts.Add
>    .ActiveChart.LOCATION Where:=2, Name:="Scatter
> Chart"                                    '2 = xlLocationAsObject
>    .ActiveChart.ChartType = -4169  'xlXYScatter
>    .ActiveSheet.Shapes("Chart 1").ScaleWidth 1.42, 0, 2
> 'msoScaleFromBottomRight
>    .ActiveSheet.Shapes("Chart 1").ScaleHeight 1.4, 0, 0
> 'msoScaleFromTopLeft
>    .ActiveSheet.Shapes("Chart 1").ScaleWidth 1.22, 0, 0
> 'msoScaleFromTopLeft
>
>    .ActiveChart.ChartArea.Select
>    .Selection.AutoScaleFont = True
>    .Selection.Font.Name = "Times New Roman"
>    .Selection.Font.Size = 12
>
>    'Add a series for each row in the recordset.
>    For i = 1 To RowCount
>        .ActiveChart.SeriesCollection.NewSeries
>        .ActiveChart.SeriesCollection(i).XValues = "='Scatter Chart'!
> R" & i + 1 & "C1"
>        .ActiveChart.SeriesCollection(i).Values = "='Scatter Chart'!R"
> & i + 1 & "C2"
>        .ActiveChart.SeriesCollection(i).Name = "='Scatter Chart'!R" &
> i + 1 & "C3"
>        .ActiveChart.SeriesCollection(i).ApplyDataLabels Type:=4,
> AutoText:=True, LegendKey:=False
>        'This bit adds a label to each point as it goes in
>        .ActiveChart.SeriesCollection(i).DataLabels.Select
>        .ActiveChart.SeriesCollection(i).Points(1).DataLabel.Select
>        .Selection.Characters.text
> = .ActiveChart.SeriesCollection(i).Name
>         ShowMsg "Processing " & .ActiveChart.SeriesCollection(i).Name
>        .Selection.HorizontalAlignment = -4131 'xlHAlignLeft
>        'This bit sets the colour of the label to the colour of the
> Marker
> 
> .ActiveChart.SeriesCollection(i).Points(1).MarkerBackgroundColorIndex
> = 3 + (i Mod 53)    'recycle after 53 or crash
>        .Selection.Font.ColorIndex = 3 + (i Mod 53)
>        .ActiveSheet.Range("C" & i + 1).Font.ColorIndex = 3 + (i Mod
> 53)
>        .ActiveChart.SeriesCollection(i).MarkerSize = MarkerSize
>    Next
>    .ActiveChart.Legend.Select
>    .Selection.Delete   'Don't want a legend with point labels
>
>    'Formatting
>    .ActiveChart.HasTitle = True
>    .ActiveChart.ChartTitle.Characters.text = ChartTitle
>    .ActiveChart.Axes(1, 1).HasTitle = True
>    .ActiveChart.Axes(1, 1).AxisTitle.Characters.text = XTitle  'from
> the form
>    .ActiveChart.Axes(2, 1).HasTitle = True
>    .ActiveChart.Axes(2, 1).AxisTitle.Characters.text = YTitle  'from
> the form
>
>    If Len(ChartTitle) > 0 Then
>        .ActiveChart.ChartTitle.AutoScaleFont = True
>        .ActiveChart.ChartTitle.Font.Name = "Times New Roman"
>        .ActiveChart.ChartTitle.Font.FontStyle = "Bold"
>        .ActiveChart.ChartTitle.Font.Size = 14
>    End If
>
>
>    If YAxisOrder = 1 Then          'from the form
>        'Reverse the values on the Y Axis
>        .ActiveChart.Axes(2).ReversePlotOrder = True
>    End If
>
>    'Set quadrant style cross hairs
>
>    .ActiveChart.Axes(1).HasMajorGridlines = True
>    .ActiveChart.Axes(1).MajorUnit
> = .ActiveChart.Axes(1).MaximumScale / 2
>    .ActiveChart.Axes(1).HasMinorGridlines = False
>
>    .ActiveChart.Axes(2).HasMajorGridlines = True
>    .ActiveChart.Axes(2).MajorUnit
> = .ActiveChart.Axes(2).MaximumScale / 2
>    .ActiveChart.Axes(2).HasMinorGridlines = False
>
>    'Move the chart to better position
>    .ActiveChart.PlotArea.Left = 22
>    .ActiveChart.PlotArea.Top = 38
>    .ActiveChart.Axes(1).AxisTitle.Left = 336
>    .ActiveChart.Axes(1).AxisTitle.Top = 429
>
> End With
>
> ObjXL.Visible = True
> Set ObjXL = Nothing     'This leaves user viewing the chart
>
> ShowMsgOff
>
> ExitIt:
>    Exit Function
> ErrorTrap:
>    Dim msg
>    msg = Err.Number & " " & Err.Description
>    LogErr Err.Number, Err.Description, "ScatterChart"
>    Resume ExitIt
>    Resume
> End Function
> 


0
phil (1005)
8/22/2007 10:41:29 AM
Dunno. I used the Graph object some years ago in another project and
it worked reasonably well - I think there were a few clunky bits with
it, and I can't recall the differences to driving Excel.  Since then
I've realised that most of my Chart-savy customers these days are
satisfied these days if I get the basic chart formatted and up in
front of them on the screen, they're happy to take over and apply the
final formatting, in Excel, for finishing touches.  I have a feeling
that it's safer to use Excel given it's strategic importance to
Microsoft ... whereas if the graph object plays up who cares ...
Tell me more about these boats
Terry



On Aug 22, 8:41 pm, "Phil Stanton" <p...@stantonfamily.co.uk> wrote:
> Works a treat. Terry. Is it possible to do the same thing with the graph
> object winin Access? I am using A2k so it is a graph9 object.
>
> Thanks again
>
> Phil
>

0
8/22/2007 1:30:25 PM
Hi Terry

For my sins, I am Treasurer and also run the DB at our local Yacht Club. 
Have been developing this DB for various organisations ever since Access 
came out (Version 1.1). DB contains all the usual bits about the members, 
Fees paid each year Boats (Both Cruisers in the local marinas and on swing 
moorings in the Backwaters and Dinghies largely on Club premises). We also 
store sails, outboard motors etc. Pretty enormous DB with 50 tables and 
uncountable queries, forms and modules. Front end is about 7MB.

The bit that you kindly helped me with is to do with boats and other 
equipment stored on Club Premises for which of course we make a charge. What 
I now have is a plan of each storage area (Dinghy Park, Outboard Shed, Main 
Pontoon, Dinghy Rack, etc) which I have drawn and set as the background to a 
chart. Each space has a number and pointer to the location as well as an X & 
Y position. I have a query that has the Storage Area, X & Y positions and a 
combination of Dinghy Name (if available) and the name of the person who has 
rented the space.I do a little bit of messing about in Excel so that each 
area appears on a separate worksheet and other than the original problem of 
Excel crashing, which I have still to resolve, all is working fine.

Why the interest in boats

Phil


<dreadnought8@hotmail.com> wrote in message 
news:1187789425.794280.223470@q3g2000prf.googlegroups.com...
> Dunno. I used the Graph object some years ago in another project and
> it worked reasonably well - I think there were a few clunky bits with
> it, and I can't recall the differences to driving Excel.  Since then
> I've realised that most of my Chart-savy customers these days are
> satisfied these days if I get the basic chart formatted and up in
> front of them on the screen, they're happy to take over and apply the
> final formatting, in Excel, for finishing touches.  I have a feeling
> that it's safer to use Excel given it's strategic importance to
> Microsoft ... whereas if the graph object plays up who cares ...
> Tell me more about these boats
> Terry
>
>
>
> On Aug 22, 8:41 pm, "Phil Stanton" <p...@stantonfamily.co.uk> wrote:
>> Works a treat. Terry. Is it possible to do the same thing with the graph
>> object winin Access? I am using A2k so it is a graph9 object.
>>
>> Thanks again
>>
>> Phil
>>
> 


0
phil (1005)
8/22/2007 3:03:34 PM
"Phil Stanton" <phil@stantonfamily.co.uk> wrote

 > Why the interest in boats

Can't speak for Terry, but here's a possibility: 
http://uk.answers.yahoo.com/question/index?qid=20060617143804AA93hbD 
<SMILE>

  Larry 


0
bouncer (4168)
8/22/2007 6:48:55 PM
Reply: