BayEnder111
New Member
- Joined
- Feb 22, 2013
- Messages
- 12
Hi Guys,
I was wondering if you could help please. I'm trying to transfer from a Database Table --> Excel based on an Excel Template.
The data to extract is coming from a query called "qryCodeFix" in which I want to display certain fields based on what is selected on a form, the criteria used is:
Go-Live Date in SMPx Week x
SMP is basically a time period split down into weeks. E.g there would be SMP07 Week 1, 2, 3 & 4.
The qryCodeFix is as follows:
As an example SMP07 Week 1 is between 15/09/2014 and 21/09/2014 however the SQL above will not work for the 15/09/2014 for some reason. When I run the query in Access it will however display info from the rest of the timeframe.
I'm trying to display the results in an Excel Template using the following VBA code:
However when I select SMP07 Week 1 run the code it will not display any information - it doesn't give any errors when running and saves the excel sheet to the relevant folder.
Which makes me think it's not picking up my query somehow therefore won't display any data? Or I've messed up somewhere on the SMP and Week function....
As you can tell I'm a complete novice (but eager to learn!) and any help would be greatly appreciated.
Many thanks
I was wondering if you could help please. I'm trying to transfer from a Database Table --> Excel based on an Excel Template.
The data to extract is coming from a query called "qryCodeFix" in which I want to display certain fields based on what is selected on a form, the criteria used is:
Go-Live Date in SMPx Week x
SMP is basically a time period split down into weeks. E.g there would be SMP07 Week 1, 2, 3 & 4.
The qryCodeFix is as follows:
Code:
SELECT tblRI.[RI Number], tblRI.[RI Title], tblRI.Application, tblRI.[Start Date/Time], tblRI.[End Date/Time], tblRI.[Go-Live Date], tblRI.[Release Type?], tblRI.[RI Scope - Code Fix]
FROM tblRI
WHERE (((tblRI.[Go-Live Date])>[Week Start] And (tblRI.[Go-Live Date])<[Week End]));
As an example SMP07 Week 1 is between 15/09/2014 and 21/09/2014 however the SQL above will not work for the 15/09/2014 for some reason. When I run the query in Access it will however display info from the rest of the timeframe.
I'm trying to display the results in an Excel Template using the following VBA code:
Code:
Private Sub cmdpostrelease_Click()
Dim xlApp As New Excel.Application
Dim xlWrkBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim slaRec As DAO.Recordset
Dim rowNo As Integer 'stores the row number to write data to
rowNo = 2
Dim DomainStart As String 'stores the start of the domain name
Dim SMP As String
Dim app As String
Dim qdf As QueryDef
Dim SMPStart As Date
Dim Col As String
'Obtain the Query to Use
Set qdf = CurrentDb.QueryDefs("qryCodeFix")
cmdpostrelease.Caption = "In Progress"
''''''''''''''''''''''
'Ensure an SMP/Week is entered
If txtweek.Value = "" Or IsNull(txtweek.Value) Or cmb_SMP.Value = "" Or IsNull(cmb_SMP.Value) Then
MsgBox "You must enter a Week AND SMP to run the report", vbOKOnly, "Error"
Else
SMP = cmb_SMP.Column(0)
'''''''''''''''''''''
' Get the SMP dates
'''''''''''''''''''''
Set smpRec = CurrentDb.OpenRecordset("tblSMP")
smpRec.MoveFirst
With smpRec
Do While Not .EOF
If CInt(smpRec.Fields("SMP#").Value) = CInt(cmb_SMP.Column(1)) Then
SMPStart = smpRec.Fields("SMP Start Date") ' Start Date
Exit Do
Else
.MoveNext
End If
Loop
End With
'calculate start and end dates
qdf.Parameters("Week Start") = DateAdd("d", (Int(txtweek.Value) - 1) * 7, SMPStart)
qdf.Parameters("Week End") = DateAdd("d", (Int(txtweek.Value) - 1) * 7, SMPStart) + 7
'Set Template Location and Sheet names
Set xlWrkBk = xlApp.Workbooks.Open(Application.CurrentProject.Path & "\Templates\Post Release Problem Cleanup.xlsx")
Set xlShtCodeFix = xlWrkBk.Worksheets("Code Fix")
Set xlShtDataRepair = xlWrkBk.Worksheets("Data Repair")
xlApp.Application.DisplayAlerts = False
xlApp.Application.ScreenUpdating = True
' Show spreadsheet on screen
xlApp.Application.Visible = True
'Clear the template fields to ensure no hangover from last report
xlShtCodeFix.Range("A2", "H20") = ""
xlShtDataRepair.Range("A2", "H20") = ""
'Set the week number
xlShtCodeFix.Range("A25").Value = Mid(SMP, 4, 2)
xlShtCodeFix.Range("B25").Value = txtweek.Value
'calculate start and end dates
qdf.Parameters("Week Start") = Format(SMPStart, "dd/mm/yyyy")
qdf.Parameters("Week End") = Format(SMPEnd, "dd/mm/yyyy")
Set myRec = qdf.OpenRecordset
If myRec.RecordCount <> 0 Then
myRec.MoveFirst
With myRec
Do While Not .EOF
'Update Code Fix Sheet
xlShtCodeFix.Cells(rowNo, "A") = myRec.Fields("RI Number") ' RI Number
xlShtCodeFix.Cells(rowNo, "B") = myRec.Fields("RI Title") ' RI Title
xlShtCodeFix.Cells(rowNo, "C") = myRec.Fields("Application") ' Application
xlShtCodeFix.Cells(rowNo, "D") = myRec.Fields("Start Date/Time") ' Start Date/Time
xlShtCodeFix.Cells(rowNo, "E") = myRec.Fields("End Date/Time") ' End Date/Time
xlShtCodeFix.Cells(rowNo, "F") = myRec.Fields("Go-Live Date") ' RI Status
xlShtCodeFix.Cells(rowNo, "G") = myRec.Fields("Release Type?") ' Closure Requested?
xlShtCodeFix.Cells(rowNo, "H") = myRec.Fields("RI Scope - Code Fix") ' Date closure Requested?
rowNo = rowNo + 1
.MoveNext
Loop
End With
End If
'Save Workbook and close Template
xlWrkBk.SaveAs Application.CurrentProject.Path & "\Reports\" & "Post Release Problem Cleanup" & "_" & Format(Now, "yymmdd") & ".xlsx", FileFormat:=51
xlWrkBk.Close SaveChanges:=False
' Turn prompting OFF and save the sheet with original name
xlApp.Application.DisplayAlerts = True
xlApp.Application.Quit
' Release objects
Set xlSht = Nothing
Set xlWrkBk = Nothing
Set xlApp = Nothing
End If
End Sub
However when I select SMP07 Week 1 run the code it will not display any information - it doesn't give any errors when running and saves the excel sheet to the relevant folder.
Which makes me think it's not picking up my query somehow therefore won't display any data? Or I've messed up somewhere on the SMP and Week function....
As you can tell I'm a complete novice (but eager to learn!) and any help would be greatly appreciated.
Many thanks