Solved

Insert Total Line in Spreadsheet from Access

Posted on 2012-03-17
4
333 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 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Top 1 of each supplier 55 56
SQL Group on First occurrence 9 25
is Microsoft Access going to Die? 9 50
Criteria for Date for DCount 4 23
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

777 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