VBA code wouldn't populate data when creating workbooks

I  need to populate templates while using a access form. THis VBA coding was done by an ex-employee, I have made changes to it so that it can use latest data to populate templates. All the queries referred in the code are working fine but somehow procedure creates empty workbooks (no population of data).  Here is some of the coding for reference (I can send you the whole code if you email me at tarrysingh15@gmail.com):
ublic Function ExportRequest() As String
   On Error GoTo err_Handler
   
   ' Excel object variables
   Dim appExcel As Excel.Application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet

   Dim sTemplate As String, sWorkbook As String
   Dim TabName As String
   Dim CourtNum As Byte, FY As String, FYnum As Integer
   Dim dbs As DAO.Database
   Dim qdf As DAO.QueryDef, qdf1a1 As DAO.QueryDef, qdf1a2 As DAO.QueryDef, qdf2 As DAO.QueryDef, qdf3 As DAO.QueryDef, qdf4 As DAO.QueryDef, qdf5 As DAO.QueryDef, qdf6 As DAO.QueryDef
   Dim qdf7a As DAO.QueryDef, qdf7b As DAO.QueryDef, qdf7c As DAO.QueryDef ', qdf7d As DAO.QueryDef
   Dim qdf8a As DAO.QueryDef, qdf8b As DAO.QueryDef, qdf8c As DAO.QueryDef, qdf8d As DAO.QueryDef, qdf8e As DAO.QueryDef
   Dim qdf9a As DAO.QueryDef, qdf9b As DAO.QueryDef, qdf9c As DAO.QueryDef, qdf10 As DAO.QueryDef
   Dim rst As DAO.Recordset
   Dim lRecords As Long
   Dim iRow As Long, fundRow As Long, costcenterRow As Long, iCol As Long
   Dim tctfCol As Byte, ntctfCol As Byte
   Dim CourtName As String
   
   
   DoCmd.Hourglass True
     
   For CourtNum = Forms!frmICRPpopulation1718TS!cboCourtFirst To Forms!frmICRPpopulation1718TS!cboCourtLast
   Me.lblMsg.Caption = "running query"
   Me.Repaint
   ' identify the template file
   
   FY = "FY1718"
     
   sTemplate = "G:\Budget Services\17-18 Budget Services\Indirect Cost Rate Proposal (ICRP)\Template\00-CourtICRP1718.xlsx"
   
   If CourtNum < 10 Then
   sWorkbook = "G:\Budget Services\17-18 Budget Services\Indirect Cost Rate Proposal (ICRP)\To Courts\C0" & CourtNum & "ICRP1718.xlsx"
   Else
   sWorkbook = "G:\Budget Services\17-18 Budget Services\Indirect Cost Rate Proposal (ICRP)\To Courts\C" & CourtNum & "ICRP1718.xlsx"
   End If
   
   On Error GoTo err_Handler
   
   ' Create the Excel Application, open the ICRP template
   Set appExcel = CreateObject("Excel.Application")
   ' appExcel.Visible = True
   Set wbk = appExcel.Workbooks.Open(sTemplate)
   appExcel.ActiveWorkbook.SaveAs Filename:=sWorkbook
   Set dbs = CurrentDb
   Set qdf = dbs.QueryDefs("qryCourtName")
   Set qdf1a1 = dbs.QueryDefs("qry1a1")
   Set qdf1a2 = dbs.QueryDefs("qry1a2")
   Set qdf2 = dbs.QueryDefs("qry3a")
   Set qdf3 = dbs.QueryDefs("qry5a")
   Set qdf4 = dbs.QueryDefs("qry5b")
   Set qdf5 = dbs.QueryDefs("qry6")
   Set qdf6 = dbs.QueryDefs("qryICRPws")
   Set qdf7a = dbs.QueryDefs("qryQFSd16")
   Set qdf7b = dbs.QueryDefs("qryQFSd18")
   Set qdf7c = dbs.QueryDefs("qryQFSd23")
   'Set qdf7d = dbs.QueryDefs("qryQFSd26")
   Set qdf8a = dbs.QueryDefs("qry7a")
   Set qdf8b = dbs.QueryDefs("qry7b")
   Set qdf8c = dbs.QueryDefs("qry7c")
   Set qdf8d = dbs.QueryDefs("qry7d")
   Set qdf8e = dbs.QueryDefs("qry7e")
   Set qdf9a = dbs.QueryDefs("qryFTEfilledDirect&Indirect1718TS")
   Set qdf9b = dbs.QueryDefs("qrySch1ExpForICRPtab6General")
   Set qdf9c = dbs.QueryDefs("qrySch1ExpForICRPtab6SpecialRev")
   Set qdf10 = dbs.QueryDefs("qryFTEfilledDirect&Indirect1516TS")
               
   ' Tab 1
   TabName = "TAB 1-ICRP Calculation Form"
   qdf.Parameters(0) = CourtNum
   Set rst = qdf.OpenRecordset
   If rst.EOF = True Then GoTo 4
   
   If Not rst.BOF Then rst.MoveFirst
   
   CourtName = rst.Fields(1)
   Set wks = appExcel.Worksheets(TabName)
   wks.Activate
   wks.Unprotect Password:="joan"
           
      lRecords = lRecords + 1
      Me.lblMsg.Caption = "Exporting record #" & lRecords & " to FY 2017-18 ICRP workbook"
      Me.Repaint
     
      wks.Cells(6, 2) = "COUNTY OF:  " & CourtName
      wks.Cells.Locked = True
      wks.Protect Password:="joan"
       
4   rst.Close
    Set rst = Nothing
Tarry SinghAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Jesus, so many variables (SRP violation in the work I guess).

Well, this code doesn't show much, beside one value from the database is supposed to be copied to the cell B6 of tab "TAB 1-ICRP Calculation Form".
One weird thing is that the workbook is saved as soon as it is created (and before data are copied to it), and never saved again.
No surprise that your data arn't showing up.

So, make sure the workbook is saved before closing it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertFabrice LambertCommented:
No replies from the asker
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.