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
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
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
Set xlWSh = xlWBk.Worksheets(GraphSheet)
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
' 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.
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
ApXL.Selection.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
' selects all of the cells
' does the "autofit" for all columns
' selects the first cell to unselect all cells
Set rst = Nothing
MsgBox Err.Description, vbExclamation, Err.Number
Edited by: Accipiter22 on Tue Jul 7 22:28:45 EDT 2009.