VB Excel Populate Cells from List

[GUYV3R]

Hi,

I have a list of sales figures pulled from a DB to excel: ie


Name Company Value
John - company - 120.00
Mary - company - 200.00
John - company - 457.58
Mark - company - 7889.55
Mary - company - 436.00

I have a tab for each person in the work book and need to populate the sheets with their individual sale from the above list.

Only problem is i have no idea where to start. I use VB alot in Access, but excel is a different kettle of fish. Ive been looking at Do loops, but not sure if this is the right way to go about it.

Can anyone point me in the right direction or have any suggestions ?

Many thanks
Guy.

[danp129]

Hi,

I have a list of sales figures pulled from a DB to excel: ie


Name Company Value
John - company - 120.00
Mary - company - 200.00
John - company - 457.58
Mark - company - 7889.55
Mary - company - 436.00

I have a tab for each person in the work book and need to populate the sheets with their individual sale from the above list.

Only problem is i have no idea where to start. I use VB alot in Access, but excel is a different kettle of fish. Ive been looking at Do loops, but not sure if this is the right way to go about it.

Can anyone point me in the right direction or have any suggestions ?

Many thanks
Guy.

Excel IMO is easier than access because you can record a macro and then look at how it works for learning purposes.

Here's a start for you with some explanations in comments. I didn't make a recordset so it's not fully tested.

Private Sub Command1_Click()
    'Requires reference: Microsoft Excel (version) Object Library
    Dim XLapp As New Excel.Application
    XLapp.Visible = True
    Dim wbkSales As Excel.Workbook
    Dim wksCurSheet As Excel.Worksheet
    Dim iSheet As Long
    Dim sReportFile As String
    Dim rngMyCell As Range
    
    'Path to existing sales report... app.path is path to this SAVED project or compiled exe
    sReportFile = App.Path & "\" & "SalesReport.xls"
    
    'Open sales file and set reference to it as wbkSales
    Set wbkSales = XLapp.Workbooks.Open(sReportFile)
    
    
    'This is the loop you need to loop through your recordset
    'You'll need to replace 'rs' with the name of your recordset
    ' and ("fieldname") with the name of the field
    While Not rs.EOF
        Set wksCurSheet = getWorksheet(wbkSales, rs("Name"))
        With wksCurSheet
            'find first empty cell AFTER the last cell in column A that has data
            If .Range("a1").Value <> Empty Then
                If .Range("a65536").Value <> "" Then
                    MsgBox "Sheet is full!", vbCritical
                    Exit Sub
                End If
                Set rngMyCell = .Range("a65536").End(xlUp).Offset(1, 0)
            Else
                Set rngMyCell = .Range("a1")
            End If
        End With
        'We now have rngMyCell set to the first empty cell after the last cell that has data
        
        'Set cell to company name
        rngMyCell.Value = rs("company")
        
        'set cell that's 0 rows down and 1 column to the right to the 'value'
        rngMyCell.Offset(0, 1).Value = rs("value")
    
        'move to next record
        rs.movenext
    Wend
    rs.Close


End Sub

Function getWorksheet(oWorkBook As Workbook, sSheetName As String) As Worksheet
    'Returns existing worksheet with name specified, if not found new sheet is created
    Dim oSheet As Worksheet
    For Each oSheet In oWorkBook.Sheets
        If LCase(oSheet.Name) = LCase(sSheetName) Then
            getWorksheet = oSheet
            Exit Function
        End If
    Next
    Set getWorksheet = oWorkBook.Sheets.Add(, oWorkBook.Sheets(oWorkBook.Sheets.Count))
    getWorksheet.Name = sSheetName
End Function

[danp129]

This was a VB6 example btw. You can use it in VBA but app.path can't be used I don't think.

[GUYV3R]

Thank you, I will have a look at it when I get back to work ;)