Solved

Insert Total Line in Spreadsheet from Access

Posted on 2012-03-17
4
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 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 33

Expert Comment

by:Norie
ID: 37733349
Jon

No problem

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

756 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