Reports output in Excel

[Gasman]

Yes,

I have just taken that code and amended it.

I created a query to get some data and called the function.

The data appears in the sheet as shown in the attached pic.

My Access did hang twice, but I *think* that was because I did not realise that the sheet was open elsewhere.

Not much has changed, just the file names and I commented out the second .CopyFromRecordset (xlWSh.Range("D8").CopyFromRecordset rst) as the recordset would not have changed and we'd just be pasting to the next row.

You are not doing anything with that date in the code for the button, so can only presume it is being used in your query?

Do you know how to debug code, setting breakpoints and stepping through code line by line?

My only problem is getting a decent name in the Save As line, but the data is good.

Option Compare Database
Option Explicit

Function SendToExcel(strTQName As String, strSheetName 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 DAO.Field
        Dim lngMaxRow As Long
        Dim lngMaxCol As Long
        Dim lngLastRow As Long
        Dim N As String
        Dim strPath As String
    
        Const xlCenter As Long = -4108
        Const xlBottom As Long = -4107
        Const xlUp As Long = -4162
        Const xlDown As Long = -4121
        
        On Error GoTo Err_Handler
    
            strPath = "C:\Temp\AccessExport.xls"
        
            Set rst = CurrentDb.OpenRecordset(strTQName)
            Set ApXL = CreateObject("Excel.Application")
        
        
            Set xlWBk = ApXL.Workbooks.Open(strPath)
            'Moved to end so Excel doesn't open before the report is finished rendering
            'ApXL.Visible = True
                
            Set xlWSh = xlWBk.Worksheets(strSheetName)
             
            rst.MoveFirst
            xlWSh.Range("C8").CopyFromRecordset rst
'            xlWSh.Range("D8").CopyFromRecordset rst
            ' selects the first cell to unselect all cells
            xlWSh.Range("A1").Select
    
        rst.Close
        Set rst = Nothing
        'Remove prompt to save file
        ApXL.DisplayAlerts = False
        xlWBk.SaveAs "C:\Temp\" & strTQName & ".xls", 51
        ApXL.DisplayAlerts = True
        'Open after report is completes
        ApXL.Visible = True
        'ApXL.Quit
    
        Exit Function
Err_Handler:
        DoCmd.SetWarnings True
        MsgBox Err.Description, vbExclamation, Err.Number
        ApXL.Quit
        Set ApXL = Nothing
        Exit Function
 End Function

 

[fat controller]

Hmmm - I have just tried it again, after commenting out as you did and get the same result - 3061 Too few parameters, Expected1

I have a feeling that I am calling the function incorrectly?

The date is purely for the query; the query will run if I have that form open, add the date, and then run the query -- works spot on in fact. However, when I try and call the function, it doesn't work, hence suspecting that this is where the problem lies:

Dim PerDate

PerDate = Me.cboDateSelect.Column(1)
If IsNull(cboDateSelect) Then
MsgBox "Oops! You need to select a period commencing date to continue", vbOKOnly, "Oops!...."
End If

Me.txtPerDate.Value = PerDate

Call SendToExcel("ControllersDirectExportQuery", "InputSheet1")

"ControllersDirectExportQuery" is the query, and "InputSheet1" is the sheet I would like the query to paste the data into - - is that the right way to call the function?

 

[Gasman]

No, the syntax looks good. You have two parameters in the call. The first the query name and the second the sheet name.

Try this.

Press Alt + F10 to open the VBA window. Double click the module to open it and then click in the left hand border of the code window. You should see a red dot. That is the breakpoint. Do it on this line

strPath = <Your filename and path here>

Click it again after all this testing to remove it.

If we do not get that far, then you are not entering the function. I am presuming you are copying and pasting code snippets and not rewriting them here?

Perhaps post your existing function as it is, just to check.?

If you get to that line you can then press F8 to step through the code line by line. if any error it will jump to the label Err_Handler: and display the error. It is that line befor the jump that we are interested in.

 

[fat controller]

OK, when I run the code, it gets to the line and highlights it in yellow, and there is a yellow right pointing arrow on top of the red spot on the left

OK first press of F8 took me to Set rst = CurrentDb.OpenRecordset(strTQName), then the next press takes me right to the Err_Handler line DoCmd.SetWarnings True

 

[Gasman]

Great, just F8 on each line.

Sounds like the error will be on the SaveAs line now.

Almost there

 

[fat controller]

Sorry, I should have replied and not edited the above post.

It only gets one step further and then jumps to the error handler, set warnings true line

 

[Gasman]

And that line is ?

(Just in case you have changed more than needed)

 

[fat controller]

The line highlighted before it jumps is: Set rst = CurrentDb.OpenRecordset(strTQName) (highlights yellow, so presume this is OK?)

It then jumps to the first line of the Error Handler: DoCmd.SetWarnings True


EDIT - got that wrong - the next line is:Set ApXL = CreateObject("Excel.Application")

 

[Gasman]

We are looking for the last line BEFORE it jumps to the Set Warnings line.

To start all over again, click Run on the menu and Reset, then start again.

From what you had already said it was looking like the query name was missing.

If you hover over variable that has been used, with the mouse, it will show you the contents of that variable.

We just need to pinpoint which line, then look at that line to see what it could be.

 

[fat controller]

Hovering over the line: Set rst = CurrentDb.OpenRecordset(strTQName) it shows rst = Nothing

Do I need to add a line before calling the function to open the query?

 

[Gasman]

No, it will work as is.

It will do. At that time you have not executed the line, so the variables will not be set.

Just press F8 on each line and take note of which one before it jumps to set warnings. That will pinpoint the error.

I'm *guessing* at present it is when trying to open the template.

Please post your code (all of it).

 

[fat controller]

Bear with me a mo - I *think* I might have dropped a clog on the queries (or one of them); be back in a minute or two

 

[fat controller]

Sorted the query problem, but it still doesn't work.

OK - the code:

Button on one screen simply opens a form:

DoCmd.OpenForm "PayrollPeriodExport", acNormal

That form opens fine, and allows me to choose the date that forms the criteria for the query

Private Sub OK_Click()
Dim PerDate

PerDate = Me.cboDateSelect.Column(1)
If IsNull(cboDateSelect) Then
MsgBox "Oops! You need to select a period commencing date to continue", vbOKOnly, "Oops!...."
End If

Me.txtPerDate.Value = PerDate

'DoCmd.OpenQuery "ControllersDirectExportQuery", acViewNormal
'DoCmd.OpenQuery "GSADirectExportQuery", acViewNormal

Call SendToExcel("ControllersDirectExportQuery", "InputSheet1")
Call SendToExcel("GSADirectExportQuery", "InputSheet2")


End Sub

If I remove the comment marks from the two commented out lines, both queries open fine and are returning the data desired.

And finally, the code as it is in the module (modUtilites):

Function SendToExcel(strTQName As String, strSheetName 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 DAO.Field
        Dim lngMaxRow As Long
        Dim lngMaxCol As Long
        Dim lngLastRow As Long
        Dim N As String
        Dim strPath As String
    
        Const xlCenter As Long = -4108
        Const xlBottom As Long = -4107
        Const xlUp As Long = -4162
        Const xlDown As Long = -4121
        
        On Error GoTo Err_Handler
    
            strPath = "C:\Users\Russell\Documents\Payroll Test\Template\4WeeklyMasterTemplate.xls"
        
            Set rst = CurrentDb.OpenRecordset(strTQName)
            Set ApXL = CreateObject("Excel.Application")
        
        
            Set xlWBk = ApXL.Workbooks.Open(strPath)
            'Moved to end so Excel doesn't open before the report is finished rendering
            'ApXL.Visible = True
                
            Set xlWSh = xlWBk.Worksheets(strSheetName)
             
            rst.MoveFirst
            xlWSh.Range("C8").CopyFromRecordset rst
            'xlWSh.Range("D8").CopyFromRecordset rst
            ' selects the first cell to unselect all cells
            xlWSh.Range("A1").Select
    
        rst.CLOSE
        Set rst = Nothing
        'Remove prompt to save file
        ApXL.DisplayAlerts = False
             xlWBk.SaveAs "C:\Users\Russell\Documents\Payroll Test\Template\FourWeeksEnding" & Date & ".xlsx", 51
        ApXL.DisplayAlerts = True
        'Open after report is completes
        ApXL.Visible = True
        'ApXL.Quit
    
        Exit Function
Err_Handler:
        DoCmd.SetWarnings True
        MsgBox Err.Description, vbExclamation, Err.Number
        ApXL.Quit
        Set ApXL = Nothing
        Exit Function
 End Function

 

[Gasman]

Ok, cannot see anything obvious.

Comment out the second call of the function for now in the form, so we know which one we are working with. Put the break point on the same lines as before and then run the form and F8 until we jump.

 

[fat controller]

Attached are screenshots of the three steps;

It looks like it is not finding the name for the query?

 

[Gasman]

Yes it does, but I'm damned if I can see the error.

Reset, run again. As soon as it hits the break point, hover over the names of the two variables (in bold) in the first line. That will show you the values.

Function SendToExcel(strTQName As String, strSheetName As String)

Edit: Also insert these two lines in the module before anything else in that module.

Option Compare Database
Option Explicit

 

[fat controller]

Screenshots attached; silly question time - could this be one of those things where Access is wanting single quotes or square brackets or something?

Thanks for your persistence and patience

Edit - those two lines are already in the module, and in fact are used throughout my project.

 

[Gasman]

I do not believe so, as the code runs perfectly well for me.

The parameters look good.

Have you added those two lines and tried again.

I must admit I'm stumped to see what the error could be. Everything looks OK, but obviously not. :-(

 

[fat controller]

I have double checked that Option Compare Database and Option Explicit are at the start of the module - still nothing.

I am now going to try by replacing the queries with new ones, just in case they are corrupt.

 

[Gasman]

Try running with the Option Explict line at the top of the module.

Have you used DAO recordsets before?

I'm thinking a reference is missing.?

To confirm we need that line OR take out the DAO. where it exists so we only have

Dim rst As Recordset
Dim fld as Field

Edit: Scrap that, it falls over when I try that