Reports output in Excel

[Gasman]

You had that for the first query once as well.?

Please post the whole module.

 

[Gasman]

I am assuming "InputSheet2" exists in the template?

 

[fat controller]

Yeah, it did it once, and then hasn't chucked its toys since;

The code:

 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
        Dim qdf As DAO.QueryDef
        
    
        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 = "\UNDISCLOSED_SERVER_PATH\4WeeklyMasterTemplate.xls"
            Set qdf = CurrentDb.QueryDefs(strTQName)
            qdf![Forms!PayrollPeriodExport!txtPerDate] = [Forms]![PayrollPeriodExport]![txtPerDate]
            Set rst = qdf.OpenRecordset()
            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("A2").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\NicolR\Desktop\TEST\FourWeeksEnding_" & Format(Now(), "yyyymmdd") & ".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

I have put the bit in caps to avoid posting the company server details; it is finding the file to begin with anyway.

One thing I don't quite get - if it runs the first query, saves it with the desired filename and quits excel, THEN goes on to do the same for the second query, will it not over-write the first file saved with the second one?

 

[Gasman]

Yes, it will overwrite, but you are putting the second query data on another sheet, so not problem there.

I don't know why that line gives an error once in a while. I was thinking it was due to the line

Set xlWSh = xlWBk.Worksheets(strSheetName)

not executing correctly, but then I'd expect that to raise an error.

very hard to debug like this. I'd be looking in the Locals window to see what was assigned.

I don't believe that A1 select line is necessary anyway as we are saving and closing the fille, so you could try commenting it out for now at least. Uncomment the call of the first query in the button and try both together.

Almost there now.

 

[fat controller]

I may go silent for a bit - about to get in the car; 14 hours of this place is enough for any man

Will pop back on when I get indoors; don't mince your head with this too much - I have done the payroll manually today, so have another 27 days until I need to do it again

 

[fat controller]

Yes, it will overwrite, but you are putting the second query data on another sheet, so not problem there.

I don't know why that line gives an error once in a while. I was thinking it was due to the line

Set xlWSh = xlWBk.Worksheets(strSheetName)
[/code[
 
not executing correctly, but then I'd expect that to raise an error.
 
very hard to debug like this. I'd be looking in the Locals window to see what was assigned.
 
I don't believe that A1 select line is necessary anyway as we are saving and closing the fille, so you could try commenting it out for now at least. Uncomment the call of the first query in the button and try both together.
 
Almost there now. :D[/QUOTE]
 
Hmmm - if it overwrites the file, then will I not lose the sheet it has just saved?

 

[fat controller]

OK - that works, sort of. No errors, and it 'appears' to run both queries, but the second one blows out the data from the first one.

Is it possible, as the querydefs are the same, would it be possible to run the second one within the module, and then save it to a range further down the same sheet (say starting at A30 to be safe?) - - so the first query pastes in from A2, the second from A30, and then I can link the rest up to that one input sheet?

 

[Gasman]

I *thought* we'd agreed to put each query on it's own sheet, in case the second query data overwrote the first query data.?

So the call of the second query should be using another sheet.?

 

[Gasman]

If you wish to put the data in one sheet, then I'd be looking at passing in the starting cell as well as a parameter, keeping the Excel module intact as it is.

If we start hard coding names (remember we are passing in the query name) then you may as well do the code in the button, but then we'd be repeating code.

This way, one piece of code works for both queries.

Up to you, it's your DB and your process.

 

[fat controller]

It is currently using another sheet, but the saving process replaces one workbook with another if that makes sense?

So, it runs the query, opens the template file, and puts the data into the sheet 'ControllerInput', then saves the workbook. Immediately after, it runs the second query, opens the template file again, and saves the data into the sheet 'GSAInput', then it saves the workbook - - this last save overwrites the first workbook saved, so I end up with only data on the GSAInput sheet.

I can absolutely guarantee that their are never going to be more than 20 staff for the first query, and never more than 10 for the second query - - so, I was wondering if it might work to call one function that runs one query, then the next with the same parameters (running the second one within the module), put all the data in a different range on the one sheet (or the other sheet, makes no odds) and then save the workbook once.

Hope that makes sense, as I will admit that I am pretty knackered now and probably not firing on all cylinders.....

 

[Gasman]

Doh! Yes, got it now. That is because we are opening one file and saving as another, so using one sheet is not going to fix that as it is working now.

What is the reasoning behind saving the file with a date.?

 

[fat controller]

The finished workbook goes off and is used for a process outside our business, and needs to be dated to identify it, as these sheets are saved presumably for audit. In reality, it should probably be called 'Four Weeks Ending' and then a date which is actually the date contained in one of the fields in each query +27 (field is a slightly different name in each query though)

If I can get the data from both queries into the same workbook, no matter how many sheets are needed within that workbook, I am pretty much sorted at that point - I can link all the other sheets up accordingly.

I have worked out why the links were blown out in the first try - - instead of deleting the rows of data that I pasted in for the manual payroll process today, I (stupidly) deleted all the rows, thus killing the links. Doh!

 

[Gasman]

We need a way of identifying the same input file on each run of the module.

I'd be inclined to copy the master to a dated version (todays date, as we do in the saveas) and then use that in the module each time.

You could use the FileCopy function in the button?

FileCopy "C:\Users\Ron\SourceFolder\Test.xlsx", "C:\Users\Ron\DestFolder\Test_" & Format(Now(), "yyyymmdd") & ".xlsx"

Then open that in the module. We do not need the Sheet anymore so we can use the parameter for the cell to paste to.?
Then just save the file each time.

Have a think about it. I need to get ready as I have an interview with an agency tomorrow for a possible job.

Will check in later tomorrow to see your thoughts.

 

[fat controller]

I *think* I am getting your idea, but am so tired now its not really 'clicking' if you know what I mean.

I need to eat too, and get to bed - got another stupid o'clock start for another 14-16hr day tomorrow.

Good luck with the interview, and hope to hear good news when you get back tomorrow.

Thanks again for all your help

 

[fat controller]

Right, first things first - how did the interview go Gasman?

I am a bit more alert than I was last night (only done 11 hours so far, so only just starting to flag a bit )

I've been thinking about this - could we open the template, put the desired data in and then save it as Template2.xls, and close it; then, have the function open Template2.xls, copy the data from the second query into the other sheet before saving with the filename and date in the other folder at the end of the process?

If my thinking is correct, the template will always be available with those sheets blank, so no chance of carrying data forward in error, and Template2 will simply be overwritten each time (and can be left in the hidden folder where the template lives), with the end result to the user being that they have their finished spreadsheet saved as desired?

 

[Gasman]

It was only an interview with the agency to see if they would put me forward to the client for an interview, and sign all the relevant papers, eligible to work in UK etc etc.

The problem with your idea is that we need to hard code that Template2.xls and only use it the second time.

I was thinking of this.
1. Use Filecopy to copy the Master to a Workfile and add the date. (Now we know what the file will be called.)
2. In the SendToExcel module open that with different start cells.

I've already tested it in my DB and it works OK.

To start with we can just hard code the first part of the work filename, but I was thinking of passing that name in as well, so slowly we would have a routine that would

Use any query
Save in any file
Start in any cell

Now that at present would have to be for the same parameter name for the query (your form value), but if that was changed to a common name like Param1 then it would work for all queries with the one parameter.

However that is looking quite a way ahead.

Let's just get it working for these two queries.

Another benefit of my way is that you can run it multiple times during the day (if any problems with data) and it will overwrite each time, as we start afresh with a new workfile loaded from the master.

Do you see my idea.?

 

[fat controller]

Well, I am an employing manager, and I would give you a job

I follow your principle, and can give it a go as I am at my work PC right now. I am unsure if the benefit of your idea will apply in my case - no harm in giving it a crack though. Bear with me and I will get it fired up - be back in a min

 

[Gasman]

Well it simplifies the code in the Excel module as we run exactly the same code each time.

Are you OK with the file names?

In the button code we want

FileCopy "sourcefile", "targetfile"

The target file is going to have Format(Now(), "yyyymmdd") & ".xlsx" appended to the filename minus the extension.

Instead of the second parameter in the call being sheetname we now change that to cell name. eg "C8"

Then in SendToExcel module we will change the following
Rename strInputSheet to strStartCell on the first line (this is just to give the parameter a meaningful name)
Change the line strPath = to be the target file name
Change Line Set xlWSh = xlWBk.Worksheets(strSheetName) to use whatever sheet name you want

On the line with xlWSh.Range("C8").CopyFromRecordset rst change to xlWSh.Range(strStartCell).CopyFromRecordset rst

Comment out the xlWBk.SaveAs line and add
xlWBk.SaveAs strPath

below it.

That should be it I reckon.

Happy with that.?

 

[fat controller]

I am struggling with our network at the moment, so its a bit slow for me to post, sorry.

So, under the button, do the file copy (filenames I am happy with), and then call the function?

When calling the function, it is now Call SendToExcel("Queryname", "A2")? - A2 being the cell I want it to start pasting the data into

And the rest looks fine

I will get started on it now

 

[Gasman]

I am struggling with our network at the moment, so its a bit slow for me to post, sorry.

So, under the button, do the file copy (filenames I am happy with), and then call the function?

When calling the function, it is now Call SendToExcel("Queryname", "A2")? - A2 being the cell I want it to start pasting the data into

And the rest looks fine

I will get started on it now

Exactly that.
Probably a good idea to paste both snippets of code, so I can just check I have not missed anything, then we can give it a run.