Access populating Excel worksheet Q

[mikeg8]

Just how ambitious am I being, bearing in mind that I'm very new to not only Access but VBA as well:
I'm trying to work out how I would get Access to populate certain cells in an Excel workbook with data from a dataset produced by a query.

1. Is such a thing possible? I think it must be, I just have no idea how to do so!

2. Is such a thing so incredibly advanced that it would take a team of ten experts a month of Sundays to do?

Anyone any ideas on how to do this? I don't want to import data, I want the db user to click on a button and Access to start Excel, open the workbook and populate particular cells within the sheets with data, then close the workbook and Excel, all in the background.

Mike

 

[edtab]

It is possible to link an excel spreadsheet into Access. Once linked, you should be able to treat is as if its a native table ie. append, update, reports, etc.

 

[pdx_man]

1. Yes
2. No.

You just have to understand the Excel Object Model. It is not very complicated. Rather intuitive, actually, if you have ever worked in Excel past the basics.

Here is a snippet of code that I have used:

'Count the number of fields or column
    MyFieldCount = rs.Fields.Count
    
    'Fill the first line with the name of the fields
    For MyIndex = 0 To MyFieldCount - 1
        ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields(MyIndex).name   'Write Title to a Cell
        ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True
        ApExcel.Cells(InitRow, (MyIndex + 1)).Interior.ColorIndex = 36
        ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True
    Next
    ApExcel.Cells(InitRow, MyIndex + 1).Formula = "Header1"
    ApExcel.Cells(InitRow, MyIndex + 1).Interior.ColorIndex = 40
    ApExcel.Cells(InitRow, MyIndex + 2).Formula = "Header2"
    ApExcel.Cells(InitRow, MyIndex + 2).Interior.Color = RGB(130, 130, 255)
    ApExcel.Cells(InitRow, MyIndex + 3).Formula = "Header3"
    ApExcel.Cells(InitRow, MyIndex + 3).Interior.ColorIndex = 42
    ApExcel.Cells(InitRow, MyIndex + 4).Formula = "Header4"
    ApExcel.Cells(InitRow, MyIndex + 4).Interior.ColorIndex = 43
    
    'Draw border on the title line
    MyLetter = Switch((MyIndex + 4) \ 26 = 0, "A", (MyIndex + 4) \ 26 = 1, "B", (MyIndex + 4) \ 26 = 2, "C", (MyIndex + 4) \ 26 = 4, "D", (MyIndex + 4) \ 26 = 5, "E")
    MyLetter = MyLetter & Chr((64 + (MyIndex + 4) Mod 26)) & InitRow
    With ApExcel.Range("A" & InitRow & ":" & MyLetter).Borders(xlEdgeBottom)
        .Color = RGB(0, 0, 0)
        .LineStyle = xlContinuous
        .Weight = xlThick
    End With
    
    MyRecordCount = 1 + InitRow

    'Fill the excel book with the values from the database
    Do While rs.EOF = False
        For MyIndex = 1 To MyFieldCount
            ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs((MyIndex - 1)).Value     'Write Value to a Cell
            ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False 'Format the Cell
        Next
        TDrst.Open "Select PoolID,TurndownCode,Comments,TurndownDate FROM tbl_Turndowns WHERE Loan_ID = '" & rs(0).Value & "'", cn
        i = 0
        Do While TDrst.EOF = False
            ApExcel.Cells(MyRecordCount, MyIndex + i).Formula = TDrst((0)).Value
            ApExcel.Cells(MyRecordCount, MyIndex + i).Interior.ColorIndex = 40
            ApExcel.Cells(MyRecordCount, MyIndex + i).WrapText = False
            ApExcel.Cells(MyRecordCount, MyIndex + i + 1).Formula = TDrst((1)).Value
            ApExcel.Cells(MyRecordCount, MyIndex + i + 1).Interior.Color = RGB(130, 130, 255)
            ApExcel.Cells(MyRecordCount, MyIndex + i + 1).WrapText = False
            ApExcel.Cells(MyRecordCount, MyIndex + i + 2).Formula = TDrst((2)).Value
            ApExcel.Cells(MyRecordCount, MyIndex + i + 2).Interior.ColorIndex = 42
            ApExcel.Cells(MyRecordCount, MyIndex + i + 2).WrapText = False
            ApExcel.Cells(MyRecordCount, MyIndex + i + 3).Formula = TDrst((3)).Value
            ApExcel.Cells(MyRecordCount, MyIndex + i + 3).Interior.ColorIndex = 43
            ApExcel.Cells(MyRecordCount, MyIndex + i + 3).WrapText = False
            i = i + 4
            TDrst.MoveNext
        Loop
        MyRecordCount = MyRecordCount + 1
        TDrst.Close
        rs.MoveNext
         'If MyRecordCount > 50 Then
          '  Exit Do
        'End If
    Loop
    
    'Suggest to the user to save it's work
 '   Response = MsgBox("Save the Excel Sheet and clik OK", vbOKOnly, "Save your file")
    
    'Close the connection with the DB
    rs.Close
    ApExcel.Visible = True