Link to home
Start Free TrialLog in
Avatar of Jenkins
JenkinsFlag for United States of America

asked on

Format an Excel Spreadsheet from Access

In Access, I have a form with a command button that transfers an entire table's data into an Excel spreadsheet. The code I'm using for that is:

DoCmd.TransferSpreadsheet acExport, acspreadsheetTypeExcel19, "tblData", "c:\tblData.xls"

The spreadsheet looks something like this after the transfer:

Employee      Category1     Category2
Dave                 100             40
Dave                   50             30
Dave                   25            100
Steve                  50            200
Steve                 200             65
Steve                  80              90

I'd like the spreadsheet to automatically appear like this after the transfer:

(the following to appear on Sheet1 or File1 --- whichever is easier)
Employee: Dave
 
  Category1     Category2
         100             40
           50             30
           25            100

Total: 175            170

(the following to appear on Sheet2 or File2 --- whichever is easier)
Employee: Steve
 
   Category1     Category2
           50            200
          200             65
           80              90

Total:  330          355

As you may be able to tell by looking at the example data above, I'm looking to have a "Total" line inserted below the data which will total the data in each of the columns.  I'm also looking to eliminate the first column since it contains repetitive Employee name data. Instead, I'd like to have the Employee name appear just one time above the header line. Finally, I'd like to have each employee's data appear on a seperate sheet ( or seperate file entirely... whichever is easier).   Thanks for your anticipated help,


ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try:

Option Compare Database
Option Explicit

Public Sub CopyToOtherSheets()

    Dim wb As Workbook
    Dim ssData As Worksheet
    Dim ssNew As Worksheet
    Dim lRow As Long
    Dim lNewRow As Long
    Dim lCat1 As Long
    Dim lCat2 As Long
    Dim lCat1Tot As Long
    Dim lCat2Tot As Long
    Dim strName As String
    Dim strNewName As String
    Dim AppExcel As New Excel.Application
   
    Set AppExcel = New Excel.Application
    AppExcel.Visible = True
   
    Set wb = AppExcel.Workbooks.Open("H:\Book1.xls")
    Set ssData = wb.Sheets("Sheet1")
   
    lRow = 2
    strNewName = "temp"
    Do Until strNewName = ""
        'find out next name
        strName = ssData.Cells(lRow, 1)
       
        'add a new sheet with the name
        Set ssNew = wb.Sheets.Add
        ssNew.Name = strName
        ssNew.Cells(1, 1) = strName
        ssNew.Cells(3, 1) = "Category1"
        ssNew.Cells(3, 2) = "Category2"
       
        'now copy the data
        lNewRow = 4
        lCat1Tot = 0
        lCat2Tot = 0
        strNewName = strName
        Do Until Not strNewName = strName
            lCat1 = ssData.Cells(lRow, 2)
            lCat2 = ssData.Cells(lRow, 3)
            lCat1Tot = lCat1Tot + lCat1
            lCat2Tot = lCat2Tot + lCat2
            ssNew.Cells(lNewRow, 1) = lCat1
            ssNew.Cells(lNewRow, 2) = lCat2
            lNewRow = lNewRow + 1
            lRow = lRow + 1
            strNewName = ssData.Cells(lRow, 1)
        Loop
       
        'add totals
        ssNew.Cells(lNewRow + 1, 1) = "Totals:"
        ssNew.Cells(lNewRow + 2, 1) = lCat1Tot
        ssNew.Cells(lNewRow + 2, 2) = lCat2Tot
    Loop
   
End Sub


 &e
looks like I overlapped with fanpages.

His solutions looks more thorough!

&e
Avatar of Jenkins

ASKER

fanpages,
 
     Thanks so much.  Works great.  You're a genius.
Avatar of [ fanpages ]
[ fanpages ]

:)  You're too kind.

Thanks for the points/grading.

BFN,

fp.
[ http://NigelLee.info ]