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:

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:

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.