Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert Total Line in Spreadsheet from Access

Posted on 2012-03-17
4
Medium Priority
?
342 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 34

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 34

Expert Comment

by:Norie
ID: 37733349
Jon

No problem

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

719 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