Producing Metrics and charts

[PaulJR]

Hi, I am having some difficuly trying to find the easiest way of producing charts from my data in Access.

Initially, I created pivotcharts in Access, only to find that getting these into reports was a little tricky, and I found the charts behave slightly differently to Excel (need exactly the same format for my reports).....this caused me to abandon this idea. I also want to keep the charts simple so anyone can go in and add to them etc....as can easily be done in Excel.

Therefore, my second attempt was to export results of queries to Excel (great....everyone can use Excel around here) using the macro command TransferSpreadsheet. This put the results of a query into an Excel sheet...lets call it MonthlyTestYield. On Sheet 1, I then set up a nice chart showing my monthly yields. Well....until the next time I exported the data from Access to Excel when I found the chart had lost its links!!! . This might be ok for one chart, but I have several!

My current work around is to write visual basic code that will sift through the relevent data from the imported sheets and place it in sheet 2 in a consistent formet, from which the charts will automatically update to the latest data. This works beautifully on one chart I tried, but I will have to write a lot of visual basic code to complete the project, and it doesn't offer flexibility should we wish to make additions or changes.

Being relatively new to Access, am I going about this all the wrong way? I'm sure I can't be alone here and I wondered if somone could kindly share any other perspectives on this.

 

[DCrake]

Simple Software Solutions

When you created your first version in Excel everthing was fine, I suspect the next time you ran it you overwrote the previous version , thats why you lost the graphs.

Why not go into Excel and connect to Access using Get Remote Data. If this is not acceptable then you will have to use VBA to open up the existing spreadsheet and paste in the data onto a nominated sheet/range. Then refresh the graph contents.

CodeMaster:

 

[PaulJR]

You suspect correctly, when the import sheets are overwritten the charts lose their link. This was a trap I literally dived into head first!

Never come across Get Remote Data before, but this might be difficult to use as most of the queries are generated from forms where the user selects date range, product type etc.

Appreciate your feedback, VBA would appear to be the best way of getting the data into a nominated sheet.

 

[DCrake]

Simple Software Solutions

I thought that was it. Anyway here is a solution that might prove useful to you.

Note: I am using ADODB you could use DAO if that how you work

Function RTT(AnyPeriod As String)

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim xlBookName As String
Dim xlSQL As String
Dim xlResizeRange As Excel.Range
Dim rCount As Integer
Dim iCnt As Integer
Dim xlArray(1000, 8)


Dim rs As New ADODB.Recordset

    xlSQL = "Select * from QryRTTByPeriod Where Period ='" & AnyPeriod & "'"
    
With MasterDbConn

rs.Open xlSQL, MasterDbConn, adOpenStatic, adLockReadOnly, adCmdText
    If Not rs.EOF And Not rs.BOF Then
        rCount = rs.RecordCount - 1
        
        xlBookName = SysProgPath & "\Templates\RTTTemplate.xls"
        
        Set xlApp = CreateObject("Excel.Application")
        Set xlBook = xlApp.Workbooks.Open(xlBookName)
        
        If Dir(SysDataPath & "\Submissions\RTT" & AnyPeriod & ".xls") <> "" Then
            If MsgBox("A previous version of this submission already exists." & vbCrLf & vbCrLf & "Do you want to overwrite the existing version?", vbYesNo + vbQuestion + vbDefaultButton1, "Submission already exists") = vbYes Then
                
                If IsXLBookOpen(SysProgPath & "\Submissions\RTT" & AnyPeriod & ".xls") Then
                    MsgBox "A copy of this spreadsheet is currently open. Cannot delete at this point. Close this spreadsheet and Excel then retry.", vbExclamation + vbOKOnly, "File already open"
                    xlBook.Close
                    xlApp.Quit
                    Set xlBook = Nothing
                    Set xlApp = Nothing
                    Exit Function
                Else
                
                    Kill SysProgPath & "\Submissions\RTT" & AnyPeriod & ".xls"
                End If
            Else
                xlBook.Close
                xlApp.Quit
                Set xlBook = Nothing
                Set xlApp = Nothing
                Exit Function
            End If
        End If
        
        xlBook.SaveAs SysDataPath & "\Submissions\RTT" & AnyPeriod & ".xls"
        Set xlSheet = xlBook.Worksheets("Returns")
            xlSheet.PageSetup.LeftHeader = "Prepared By: " & LetSenderAdr1 & " on " & Format(Now, "dd/mm/yyyy") & " at " & Format(Now(), "h:nn am/pm")
        xlSheet.Range("B4").Value = AnyPeriod
        xlSheet.Range("A9").CopyFromRecordset rs
        For x = 9 To rCount + 9
            xlSheet.Range("K" & x).Value = ""
        Next
        

        xlApp.Visible = True
        xlApp.UserControl = True
    End If
        rs.Close
End With
Set rs = Nothing

            
        
End Function

Explanation:
I call this function from a form that asks the user for a period, such as "May 2008". This is apssed to the RTT function.
First it set the recordset using the parameters as filters.
Checks for any records
Checks if a previous version of this xls fiel exists
Check if the file is currently open
Opens a blank template that contains the formatted columns and graphs
Appends the data to the nominated sheet using the CopyFromRecordset procedure.
Changes the header details and saves the spreadheet and Excel
Closes the recordset

This is about all you need

David

 

[PaulJR]

Ummm, unfortunately ADODB and DAO are alien to me at present. Whilst I appreciate the code I will have to learn how to implement Access functions and pass variables to them. My experience with Access is less than 6 months.

Definitely something for me to find out more about so thanks. For now though my Excel Visual Basic will do the job - thanks again for that advise too!