OK, first the button code (note the second call is commented out for now)
Code:
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
FileCopy "SERVER-ADDRESS\4WeeklyMasterTemplate.xls", "SERVER-ADDRESS\4WeeklyMasterTemplate_" & Format(Now(), "ddmmyyyy") & ".xls"
Call SendToExcel("PayDirectControllers", "A2")
'Call SendToExcel("GSADirectExportQuery", "GSAInput")
End Sub
Just reading it through before I start, how does it now know where to put the data from the second query, as we are not telling it to use a different sheet?
Function SendToExcel(strTQName As String, strStartCell 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 = "SERVER-ADDRESS\4WeeklyMasterTemplate_" & Format(Now(), "ddmmyyyy") & ".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(Input)
rst.MoveFirst
xlWSh.Range(strStartCell).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
xlWbk.SaveAs strPath
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
Looks great. Let's be brave and give it a go. You will need to uncomment out the second call, as when we run this code we start with a fresh empty template each time. So amend the second call for your choice of cell.
make sure the template has that "Input" sheet, as last night is was "InputSheet1"
Really sorry about this - our network/internet problems have now became severe; a speedtest has just given a download speed of 0.12 and an upload of 0.39 Mbps respectively;
The issue is internal I think, as internal systems are barely running at all.
I am going to have to wait until I get home and try this there, as I reckon it will fall flat on its nose if I try anything with it at the moment.
Thanks for your patience, and I promise I will be back on as soon as I get to a working internet connection.
No probs. I'd probably leave it until tomorrow, otherwise you'll be changing paths and we might miss something. No rush, you said you have until the next run.
Ours has only just come back up about an hour ago - - allegedly some sort of issue between the primary and secondary firewalls. BT make up some right old boswelox
OK, I am going to try and get this sorted now - bear with me and I will get everything fired up
Let's get this right. I knew the post count was high 128, but this thread has had 1,503 views. Every man and his dog must have taken a look at this due to the high post count.
I have just recopied the code from the post above, and upon entry (other code running) this line is highlighted in red. Does the Input need quotation marks?
All I need to do now is re-link the other four sheets (only because I bent my template out of shape the other night), and work out how to change the 'Save As' file path so it selects the current users My Documents (or Desktop) regardless of who the user is.
Also a couple of other wee 'niggles' to sort out, but once they are I do believe I have reached the end of this stage in my project.
All that remains now is to replicate this another 9 times for other business units......
Great.
Some things to consider.?
Perhaps hide the Input sheet in the template and try again. I don't think it should cause any problems, but as your other sheets are based on that data, worth doing I think, especially as it is going out of the company?
For the output file look at getting the Userprofile with the ENVIRON function
When I was testing I hate having strings all over the place and tend to use variables. That way if I need to change it then it is changed in one place. less chance of errors.
The strworkfile variable was initially hard coded like yours, but can easily be changed.
This will put the output file in my Docments folder.
My code in my button is
Code:
Private Sub Command0_Click()
Dim strMaster As String, strWorkFile As String
strMaster = "C:\temp\AccessExport.xls"
strWorkFile = Environ("Userprofile") & "\Documents\AccessExport_" & Format(Now(), "yyyymmdd") & ".xls"
FileCopy strMaster, strWorkFile
Call SendToExcel("qryTest", strWorkFile, "A2")
Call SendToExcel("qryTest", strWorkFile, "Z2")
End Sub
You could also comment out the line with ApXL.Visible = True, as we close Excel immediately after now, so as to be able to run the second query.
That thinking ahead I mentioned before, is for situations like those other 9 units. If they needed the same functionality, then I'd be looking to get that SendToExcel to be able to handle all of them.
Well done though.
Don't forget to thank Gina for the code that started all of this and good luck with the rest of it.
I owe you both many, many cyber drinks; in fact, there are a good few people on here that have helped me immensely.
When I started this current project, I hadn't touched VBA - yet slowly, and surely I have learned little bits to get me where I am now. I am still very much a novice, particularly when compared to the knowledge of others such as yourself and Gina, but at least now I am not frightened to have a go at something, even if it does mean I have to come here and ask for help.
The best bit of all has been the learning - - not just being given an answer to a problem, but being guided to understand WHAT that answer is, and HOW it is working; you can't put a price on that.
I just hope that by lurking around here in the coming weeks and months that I might be able to pay some of this back by helping someone else out, even if it is relatively simple stuff.
And then, of course, there is bound to be another project.....
Let's get this right. I knew the post count was high 128, but this thread has had 1,503 views. Every man and his dog must have taken a look at this due to the high post count.
Great.
Some things to consider.?
Perhaps hide the Input sheet in the template and try again. I don't think it should cause any problems, but as your other sheets are based on that data, worth doing I think, especially as it is going out of the company?
For the output file look at getting the Userprofile with the ENVIRON function
When I was testing I hate having strings all over the place and tend to use variables. That way if I need to change it then it is changed in one place. less chance of errors.
The strworkfile variable was initially hard coded like yours, but can easily be changed.
This will put the output file in my Docments folder.
My code in my button is
Code:
Private Sub Command0_Click()
Dim strMaster As String, strWorkFile As String
strMaster = "C:\temp\AccessExport.xls"
strWorkFile = Environ("Userprofile") & "\Documents\AccessExport_" & Format(Now(), "yyyymmdd") & ".xls"
FileCopy strMaster, strWorkFile
Call SendToExcel("qryTest", strWorkFile, "A2")
Call SendToExcel("qryTest", strWorkFile, "Z2")
End Sub
You could also comment out the line with ApXL.Visible = True, as we close Excel immediately after now, so as to be able to run the second query.
That thinking ahead I mentioned before, is for situations like those other 9 units. If they needed the same functionality, then I'd be looking to get that SendToExcel to be able to handle all of them.
Well done though.
Don't forget to thank Gina for the code that started all of this and good luck with the rest of it.
Well, it transpired that the firewall problem is not the only issue as our servers also seem to be in need of sickbay.... very shortly after my last post, we lost pretty much everything (couldn't even open a 150kb excel file over the network), so I had to give up and come home.
I am going to copy everything onto my home network server and give it a bit of a soak testing on various PC's over the course of this weekend, and make the final tweaks.
I will post back how it goes hiding the input sheet etc
A massive thank you to both Gina and Gasman, and to anyone reading this who has turned it up on a search engine - stick with the ladies and gents of this forum and you will do well
I have been peeking in to see the progress and was so pleased to see *We have lift off!* Taking my cyber drink and moving on to the next person in line!
I had a lot of assistance on here when I was asked to create a database in my last workplace. I knew what I wanted to do, just not how to do it.
I also had a lot of help with a database I created for my Bibby Gazette, where all the pdf files are generated from the database. A LOT of data entry went into that.
So it is good to give something back, I have also learnt more from this thread and as I said, it has been fun for me.
I owe you both many, many cyber drinks; in fact, there are a good few people on here that have helped me immensely.
When I started this current project, I hadn't touched VBA - yet slowly, and surely I have learned little bits to get me where I am now. I am still very much a novice, particularly when compared to the knowledge of others such as yourself and Gina, but at least now I am not frightened to have a go at something, even if it does mean I have to come here and ask for help.
The best bit of all has been the learning - - not just being given an answer to a problem, but being guided to understand WHAT that answer is, and HOW it is working; you can't put a price on that.
I just hope that by lurking around here in the coming weeks and months that I might be able to pay some of this back by helping someone else out, even if it is relatively simple stuff.
And then, of course, there is bound to be another project.....
