Jenkins
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,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
looks like I overlapped with fanpages.
His solutions looks more thorough!
&e
His solutions looks more thorough!
&e
ASKER
fanpages,
Thanks so much. Works great. You're a genius.
Thanks so much. Works great. You're a genius.
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
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