Load Access data into formatted Excel sheet using VB

[TedRSki]

I have an Excel spreadsheet already created with Names in column A and then Thursday's date in Column D, Friday's date in Column E, and so on... with a Total Column with formula in Column K, then Rate of Pay in the next column, and calculated Payoll in Column M. I have the data in Access where the query shows Name, Date, Total Hours for that date. What I need to figure out is how to run VBA code in my Access system using the query against the spreadsheet to find the name, then find the date, and fill in the hours. I know how to do it with spreadsheet to spreadsheet. I just don't know how to read the next record in the query and then check it against the sheet. I can't just transfer the spreadsheet as not everyone works every week. Can anyone point me in the right direction?

 

[dhookom]

Have you considered creating an Excel pivot table connected to the Access records as External Data?

Duane
Hook'D on Access
MS Access MVP

 

[TedRSki]

dhookom, Thanks for the response. The issue is that the Excel spreadsheet has already been created by the Accounting Department. I was able to piece together code from about four or five different threads and I can write to a spreadsheet. Now I just have to work out the VB code and loops to load the data. I am working on it one step at a time so it is taking longer than I had hoped but if it is to be done correctly, it is going to take time. Thank you again for your response and insight.

 

[PHV]

You may consider the Excel.Range.CopyFromRecordset method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TedRSki]

PHV, thank you for your response. I have been able to work with the a spreadsheet but my problem now is that the sheet starts off blank. I put names on the sheet then run through code to insert the hours but I get just the hours on the sheet. This is the start of my code, could you tell me how to not start with an empty sheet but to leave the formatting and other cells alone:

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Select * from EMP_WRK_SHEET2 WHERE TTYPE='D' ORDER BY EMP_INFO_CAT, EMPNAME, SDATE, HCATEG DESC", dbOpenDynaset, dbReadOnly)
Set XL = CreateObject("Excel.Application") '
Set ExcelSheet = CreateObject("Excel.Sheet")
Set ExcelSheet = XL.Workbooks.Add("F:\ARAMARK\PDP.xlsx")

 

[PHV]

So, you want to use F:\ARAMARK\PDP.xlsx as a template ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TedRSki]

Thank you both for your input to work my issue. I was able to load the data using the following code which worked well with multiple sheets (SName from one query) and JobCat from another query.

    SName = RSL("LENTRY")
    RS.MoveFirst
    
    Do Until RS.EOF
        If (RS("LENTRY") = SName) Then
        JobCat = RS("LREF")
        Select Case JobCat
            Case "Controller/Office Managers"
                CntrlRow = CntrlRow + 1
                ExcelSheet.Sheets(SName).Cells(CntrlRow, 3).Value = RS("EMPNAME")
        .
        .
        .
        End Select
        End If
        'Move to next record
        RS.MoveNext
    Loop

RSL.MoveNext

Loop