UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Need button that launches excel and creates a graph based on query data    
 
   
Accipiter22
post Jun 30 2009, 05:35 PM
Post #1

UtterAccess Guru
Posts: 537
From: Boston, MA
I have a form that is based on a query. If I wanted to open excel and have the numbers from the query plotted out onto a graph, how would I do this? There's a bunch of AC commands that I've tried using in the onclick property for a button on the form, but none have worked so far.
Go to the top of the page
 
+
Accipiter22
post Jul 1 2009, 04:17 PM
Post #2

UtterAccess Guru
Posts: 537
From: Boston, MA
no ideas? Is there any way to just open excel from Access via form button?
Go to the top of the page
 
+
Bob_L
post Jul 1 2009, 04:20 PM
Post #3

Utterly Banned
Posts: 7,038
QUOTE
no ideas? Is there any way to just open excel from Access via form button?

You could have a graph already built and reference a sheet. Then you could use this code to send the query results to that sheet for the graph.
Go to the top of the page
 
+
mazeworx
post Jul 1 2009, 05:33 PM
Post #4

UtterAccess Member
Posts: 35
From: Burlington, Ontario, Canada
You can create a graph report in access. There is no need to call excel to do this. Use the report wizard for details
Go to the top of the page
 
+
WildBird
post Jul 1 2009, 08:45 PM
Post #5

UtterAccess VIP
Posts: 3,050
From: Perth, Australia
What sort of data is it? Is it fixed number of fields and records? I.e 12 months data for 5 items, or is it random number of fields? Can make a difference on sheet as to the source data, but like Bob said, could export to a sheet in Excel, and have a graph setup to look at that sheet. Personally would not use the graphs in Access, Excel is much more powerful.
Go to the top of the page
 
+
mazeworx
post Jul 2 2009, 11:16 PM
Post #6

UtterAccess Member
Posts: 35
From: Burlington, Ontario, Canada
I use the graphs in access on a regular basis ... if access provides a solution sufficient enough for the purpose why not use it?. If access can't provide the advance graphing required then go to excel.
Go to the top of the page
 
+
WildBird
post Jul 2 2009, 11:30 PM
Post #7

UtterAccess VIP
Posts: 3,050
From: Perth, Australia
By all means if you can use just Access graphs, then use them. I find though that most users prefer to use Excel as they can do alot more with it, and do their own formatting. Apart from a report that prints barcodes, I havent used any Access reports for maybe 10 years, I just export to Excel and let users do their own extra formatting and calcs etc if they want to. I get sick of having to change a report because a user doesnt like a particular font, or anther user wants a different colour heading etc.
Go to the top of the page
 
+
Accipiter22
post Jul 7 2009, 04:50 PM
Post #8

UtterAccess Guru
Posts: 537
From: Boston, MA
QUOTE
What sort of data is it? Is it fixed number of fields and records? I.e 12 months data for 5 items, or is it random number of fields? Can make a difference on sheet as to the source data, but like Bob said, could export to a sheet in Excel, and have a graph setup to look at that sheet. Personally would not use the graphs in Access, Excel is much more powerful.


It'll be a random number of items over a time period that the user sets. I experimented with it some over this weekend (using the graphs in Access). I'm probably going to end up using Excel.
Go to the top of the page
 
+
Accipiter22
post Jul 7 2009, 09:21 PM
Post #9

UtterAccess Guru
Posts: 537
From: Boston, MA
To be honest I have no idea how to get a function to run. I opened the VBA editor, pasted in the code, changed the placeholder names in the code to match the names of the sheets/forms involved....now what? I tried making a form button with a macro to run the code, but all I get is "The expression you entered has a function name that Access can't find"


edit: I have it saved as a module, not sure if that is right or not. As you can tell I know nothing about VBA.

Edited by: Accipiter22 on Tue Jul 7 22:24:10 EDT 2009.


Also, here's the code with my edits made to it. The query I'm using is QryGraph, the Excel sheet is called GraphSheet, it's part of a workbook named GraphTest.xlsx

CODE
Public Function SendTQ2ExcelSheet(QryGraph As String, GraphSheet As String)

' strTQName is the name of the table or query you want to send to Excel

' strSheetName is the name of the sheet you want to send it to





  Print

    Dim rst As DAO.Recordset

    Dim ApXL As Object

    Dim xlWBk As Object

    Dim xlWSh As Object

    Dim fld As Field



  Dim strPath As String





    Const xlCenter As Long = -4108

    Const xlBottom As Long = -4107



    On Error GoTo err_handler







  strPath = "C:\Documents and Settings\Matt\Desktop\GraphFolder\GraphTest.xlsx"

    Set rst = CurrentDb.OpenRecordset(QryGraph)



    Set ApXL = CreateObject("Excel.Application")





    Set xlWBk = ApXL.Workbooks.Open(strPath)

    ApXL.Visible = True

      Print

    Set xlWSh = xlWBk.Worksheets(GraphSheet)

  



  Print xlWSh.Range("A1").Select





    For Each fld In rst.Fields

        ApXL.ActiveCell = fld.Name

        ApXL.ActiveCell.Offset(0, 1).Select

    Next



    rst.MoveFirst

    xlWSh.Range("A2").CopyFromRecordset rst

    xlWSh.Range("1:1").Select

  ' This is included to show some of what you can do about formatting.  You can comment out or delete

  ' any of this that you don't want to use in your own export.

    With ApXL.Selection.Font

        .Name = "Arial"

        .Size = 12

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

    End With

    ApXL.Selection.Font.Bold = True

    With ApXL.Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .MergeCells = False

    End With

    ' selects all of the cells

    ApXL.ActiveSheet.Cells.Select

    ' does the "autofit" for all columns

    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

    ' selects the first cell to unselect all cells

    xlWSh.Range("A1").Select



    rst.Close

    Set rst = Nothing



    Exit Function

err_handler:

    DoCmd.SetWarnings True

    MsgBox Err.Description, vbExclamation, Err.Number

    Exit Function



End Function


Edited by: Accipiter22 on Tue Jul 7 22:28:45 EDT 2009.
Go to the top of the page
 
+
WildBird
post Jul 8 2009, 03:05 AM
Post #10

UtterAccess VIP
Posts: 3,050
From: Perth, Australia
What is the code on the button you are using? What is name of module, cant be same as function. And if you are saying that the sheet name is GraphSheet, then you are using a variable, not the string literal.

Try on the click event of the command button

SendTQ2ExcelSheet ("QryGraph","GraphSheet")

and change the code to be something liek. Needs a bit more tidying up - seems to be a 'work in progress' bit of code, but should work now I think

CODE
Public Function SendTQ2ExcelSheet(byval strQryGraph As String, byval strGraphSheet As String) as boolean

' strTQName is the name of the table or query you want to send to Excel

' strSheetName is the name of the sheet you want to send it to





  'Print

    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As Field
    Dim strPath As String

    Const xlCenter As Long = -4108

    Const xlBottom As Long = -4107



    On Error GoTo err_handler

  strPath = "C:\Documents and Settings\Matt\Desktop\GraphFolder\GraphTest.xlsx"

    Set rst = CurrentDb.OpenRecordset(strQryGraph)

    Set ApXL = CreateObject("Excel.Application")

    Set xlWBk = ApXL.Workbooks.Open(strPath)

    ApXL.Visible = True

    Set xlWSh = xlWBk.Worksheets(strGraphSheet)

    For Each fld In rst.Fields

        ApXL.ActiveCell = fld.Name

        ApXL.ActiveCell.Offset(0, 1).Select

    Next

    rst.MoveFirst

    xlWSh.Range("A2").CopyFromRecordset rst

    xlWSh.Range("1:1").Select

  ' This is included to show some of what you can do about formatting.  You can comment out or delete

  ' any of this that you don't want to use in your own export.

    With ApXL.Selection.Font

        .Name = "Arial"

        .Size = 12

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

    End With

    ApXL.Selection.Font.Bold = True

    With ApXL.Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .MergeCells = False

    End With

    ' selects all of the cells

    ApXL.ActiveSheet.Cells.Select

    ' does the "autofit" for all columns

    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

    ' selects the first cell to unselect all cells

    xlWSh.Range("A1").Select

    rst.Close

    Set rst = Nothing

    Exit Function

err_handler:

    DoCmd.SetWarnings True

    MsgBox Err.Description, vbExclamation, Err.Number

    Exit Function

End Function
Go to the top of the page
 
+
Accipiter22
post Jul 8 2009, 10:07 PM
Post #11

UtterAccess Guru
Posts: 537
From: Boston, MA
I put "SendTQ2ExcelSheet ("QryGraph","GraphSheet")" in the OnClick of the button, but when I click it, it still errors out. It says "access can't find the object SendTQ2ExcelSheet ("QryGraph","GraphSheet"). I named the module ExcelSend, and the sheet is GraphSheet.

Edited by: Accipiter22 on Wed Jul 8 23:08:56 EDT 2009.

Edited by: Accipiter22 on Wed Jul 8 23:15:00 EDT 2009.
Go to the top of the page
 
+
WildBird
post Jul 8 2009, 10:14 PM
Post #12

UtterAccess VIP
Posts: 3,050
From: Perth, Australia
Post the whole code behind the button
Go to the top of the page
 
+
Accipiter22
post Jul 8 2009, 10:23 PM
Post #13

UtterAccess Guru
Posts: 537
From: Boston, MA
it worked!

CODE
Private Sub Command0_Click()
Run SendTQ2ExcelSheet("QryGraph", "GraphSheet")
End Sub


I must've closed it the first time without saving the 'run' portion.

When I go back into Access though there's an error: Run-time error '2517': Microsoft Access can't find the procedure 'False' then when I click debug it highlights: Run SendTQ2ExcelSheet("QryGraph", "GraphSheet")
Go to the top of the page
 
+
WildBird
post Jul 8 2009, 10:30 PM
Post #14

UtterAccess VIP
Posts: 3,050
From: Perth, Australia
Try

CODE

Private Sub Command0_Click()
    SendTQ2ExcelSheet "QryGraph", "GraphSheet"
End Sub
Go to the top of the page
 
+
WildBird
post Jul 8 2009, 10:32 PM
Post #15

UtterAccess VIP
Posts: 3,050
From: Perth, Australia
The bit about false may have something to do with the formatting bit. Try compiling to see what line it is on, probably th e formatting section which you dont need. Comment it out if it is that.
Go to the top of the page
 
+
Accipiter22
post Jul 10 2009, 07:49 PM
Post #16

UtterAccess Guru
Posts: 537
From: Boston, MA
weird....It's when I step into the top line of the code I've posted here.

Exit Function

err_handler:

DoCmd.SetWarnings True

MsgBox Err.Description, vbExclamation, Err.Number

Exit Function

End Function





Also, this is incredibly bizarre:

When I click the button, sometimes the qry field names come out as the column headings in excel...other times they get slapped into random cells, always side by side. So like H15 & I15, or D7&E7. Now THAT is weird.
Go to the top of the page
 
+
« Next Oldest · Access Forms · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 22nd May 2013 - 01:30 PM