Query to Excel. Please Help.

[Adam McReynolds]

I have a query I need to populate and Excel template. Basically we log in repairs into Access and then we populate and print a receiving page in Excel. The work is subcontracted so we have to work with our contractor's Excel template. I have found Bob Larson's template for how to do this but I am lost on how to make it work.

First, the query uses a parameter to pull selected records by RMA. Second, after I have this queried data, how do I call the function?

I have attached a picture of the Excel template. You can see the first field that needs to be populated is B12. Also those fields are merged B+C columns.

Here is my code from bob larson:

Public Function SendTQ2ExcelSheet(A_QRY_RF_SHORT As String, Alpha_Template As String)

' strTQName (A_QRY_RF_SHORT) is the name of the table or query you want to send to Excel
' strSheetName (ALPHA_Template) 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 DAO.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\warehouse\Desktop\IN WORK DATABASES\APS Pickup Test\ALPHA-Pickup_test_EXCEL\ALPHA_Pickup_Template.xlsx"
Set rst = CurrentDb.OpenRecordset(A_QRY_RF_SHORT)
Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strPath)
    ApXL.Visible = True
       
    Set xlWSh = xlWBk.Worksheets(Alpha_Template)
  
    xlWSh.Activate
    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

Any help would be appreciated. Thanks.

 

[pr2-eugin]

Parameter Queries cannot be used in Open Recordset unfortunately. You can edit the SQL dynamically though. Look up on Query Defs. That would be the start..

 

[Cronk]

And to answer the OP's question "how do I call the function?'

Put a button on your form and in the ON_Click event, add the code and click the button.

var = SendTQ2ExcelSheet("YourQueryName", "YourWorksheetName")

 

[Adam McReynolds]

And to answer the OP's question "how do I call the function?'

Put a button on your form and in the ON_Click event, add the code and click the button.

var = SendTQ2ExcelSheet("YourQueryName", "YourWorksheetName")

Thanks for that. It helped me at least know my data can move from Access to Excel. Now my problem is now to get a parameter query to do this. Maybe I will need a new method? Gonna look into the suggestion of Paul.

 

[Adam McReynolds]

Parameter Queries cannot be used in Open Recordset unfortunately. You can edit the SQL dynamically though. Look up on Query Defs. That would be the start..

Gonna look into this and I will get back with my results. Thanks.

 

[Cronk]

You can set a reference to the query with say
dim qdf as querydef
set qdf = db.querydefs("YourQueryName")
and then set the parameter value.

Alternatively, just open the rst based on the SQL
set rst = db.openrecordset("SELECT <field list> FROM yourTable WHERE CustomerID=" & me.CustomerID)

 

[Adam McReynolds]

You can set a reference to the query with say
dim qdf as querydef
set qdf = db.querydefs("YourQueryName")
and then set the parameter value.

Alternatively, just open the rst based on the SQL
set rst = db.openrecordset("SELECT <field list> FROM yourTable WHERE CustomerID=" & me.CustomerID)

I am having trouble with the SQL method because the module that holds the function is not on the form so using "Me." won't work.

I will get back to this tomorrow and also try out the querydef method. Thanks again!

 

[Cronk]

In what module is the function in? You still need the value of the parameter value stored somewhere. Or are you intending to prompt the user to input a value?

 

[Adam McReynolds]

In what module is the function in? You still need the value of the parameter value stored somewhere. Or are you intending to prompt the user to input a value?

Thanks for the reply. The function is in a standard module. The parameter value on the query is called "aps_rma" and it is an identifier for the batch of repairs related to one customer.

I would want an input box for the user to enter this identifier into and then get the excel sheet, but also I would like it to also reference a text box like "txt_rma" that the user can just click a button if they are on a current from with that "aps_rma" already in the text box.

Really I just want to get either method working so that I can then scale up the functionality from there.

Thanks again!

 

[Cronk]

Then your code would be

set rst = db.openrecordset("SELECT <field list> FROM yourTable WHERE BatchID=" & Forms!YourForm!txt_rma)

or

str = Inputbox("What is the batch no?"
set rst = db.openrecordset("SELECT <field list> FROM yourTable WHERE BatchID=" & str)

NB you'll need to wrap quotes around the variable if BatchID is a text field.