Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Insert Total Line in Spreadsheet from Access

Posted on 2012-03-17
4
Medium Priority
?
343 Views
Last Modified: 2012-03-17
Hi, I have cobbled together some code which createa a spreadsheet and does some simple formatting. What I'm stuck on is putting the work "Totals" and the bottom of each sheet (the number of rows is variable), and then inserting a sum of each column. This relies on calculating the number of occupied rows and inserting a total at the bottom of each. The code I have so far is:

Sub xPort2XL()

Dim xlObj As Object, xlSht As Object, xlPath
Dim SNum As String

SNum = Forms!frmES_Export!StoreReport
xlPath = Forms!frmES_Export!FolderLocation & SNum & "\" & SNum & ".xls"
If Dir(xlPath) <> "" Then Kill xlPath
'export to excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryES_Export", Forms!frmES_Export!FolderLocation & SNum & "\" & SNum & ".xls", True

'open the excel file and format
Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open xlPath
   
    With xlObj
        .Range("A1:AZ1").Select
        .Selection.EntireRow.Font.Bold = True
        .ActiveWorkbook.Save
    With xlObj
        .Range("A1:AZ1").Select
        .Selection.EntireColumn.AutoFit
        .ActiveWorkbook.Save
    With xlObj
        .Range("E2:AQ2").Select
        .Selection.EntireColumn.NumberFormat = "#,##0.00"
        .ActiveWorkbook.Save
               
    End With
    End With
    End With

    xlObj.Quit
    Set xlObj = Nothing
           
End Sub
0
Comment
Question by:jonlake
  • 2
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 37733166
You don't actually need to calculate the no of rows for the totals formulas, but you need to find the last row to put them in the right place.

Anyway, try this.

It adds totals for each column, but in only formats from column E (field 5) onwards as "#,##0.00".

If either of those need changed that should be straightforward.

Sub xPort2XL()

Dim xlObj As Object
Dim xlWB As Object
Dim xlSht As Object
Dim xlRng As Object
Dim xlPath As String
Dim SNum As String
Dim LastRow As Long
Dim NoCols As Long
Const xlUp = -4162
Const xlToLeft = -4159

    SNum = Forms!frmES_Export!StoreReport
    xlPath = Forms!frmES_Export!FolderLocation & SNum & "\" & SNum & ".xls"
    If Dir(xlPath) <> "" Then Kill xlPath
    'export to excel

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryES_Export", xlPath, True

    'open the excel file and format
    Set xlObj = CreateObject("Excel.Application")

    Set xlWB = xlObj.Workbooks.Open(xlPath)

    Set xlSht = xlWB.Worksheets(1)

    With xlSht

        LastRow = .Range("A" & xlSht.Rows.Count).End(xlUp).Row
        NoCols = .Cells(1, xlSht.Columns.Count).End(xlToLeft).Column

        With .Range("A1").Resize(, NoCols)

            .Font.Bold = True
            .EntireColumn.AutoFit
        End With


        .Cells(2, "E").Resize(LastRow - 1, NoCols - 5).EntireColumn.NumberFormat = "#,##0.00"

        Set xlRng = .Cells(LastRow + 1, 1).Resize(, NoCols)

        xlRng.Formula = "=SUM(R2C:R[-1]C)"


    End With


    xlWB.Close SaveChanges:=True

    ' xlObj.Visible = True

    xlObj.Quit

    Set xlObj = Nothing

End Sub

Open in new window

0
 

Author Comment

by:jonlake
ID: 37733201
That works well but places a total at the bottom of every column. I would like the word "Totals" to appear in column A at the bottom, with the totals beginning from column E onwards.

Just one more thing, can that last row be formatted in bold?
0
 

Author Closing Comment

by:jonlake
ID: 37733267
I got there! By using your elegant solution I was able to create the remaining title, and formatting. Thank you so much, I've been struggling most of the day to work it out.

Regards, Jon
0
 
LVL 35

Expert Comment

by:Norie
ID: 37733349
Jon

No problem

I kind of knew there would be other things to do.:)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question