UtterAccess Discussion Forums > Need button that launches excel and creates a graph based on query data

Full Version: Need button that launches excel and creates a graph based on query data

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

Accipiter22

Jun 30 2009, 05:35 PM

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.

Accipiter22

Jul 1 2009, 04:17 PM

no ideas? Is there any way to just open excel from Access via form button?

Bob_L

Jul 1 2009, 04:20 PM

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.

mazeworx

Jul 1 2009, 05:33 PM

You can create a graph report in access. There is no need to call excel to do this. Use the report wizard for details

WildBird

Jul 1 2009, 08:45 PM

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.

mazeworx

Jul 2 2009, 11:16 PM

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.

WildBird

Jul 2 2009, 11:30 PM

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.

Accipiter22

Jul 7 2009, 04:50 PM

QUOTE

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.

Accipiter22

Jul 7 2009, 09:21 PM

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.

WildBird

Jul 8 2009, 03:05 AM

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

Accipiter22

Jul 8 2009, 10:07 PM

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.

WildBird

Jul 8 2009, 10:14 PM

Post the whole code behind the button

Accipiter22

Jul 8 2009, 10:23 PM

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")

WildBird

Jul 8 2009, 10:30 PM

Try

CODE

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

WildBird

Jul 8 2009, 10:32 PM

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.

Accipiter22

Jul 10 2009, 07:49 PM

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.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.