Avatar of johnmadigan
johnmadigan
Flag for United States of America asked on

Calculate totals from highlighted cell in Excel file from ms access

I have an access query that I output to an excel spreadsheet.  When the excel file opens there is code in Access the does some formatting - bold text, auto size etc.

This is a report that shows all the vendor invoices.  The CFO would like to be able to highlight cells of the report yellow for the invoices he is thinking of paying.  So as he changes the cell yellow he would like to have a summary cell at the top that shows the amount going up as he highlights the cells.

He has to see what he can pay with current cash flow.

Didn't know the best way to do this.

I have tried to do the export from access to a .xtml template but could not get it to work - I don't think you can export to a template macro enabled.

I found some excel code that puts in a function formula in a module - when you highlight a cell yellow it will sum that cell.  So I thought about having code in access to create a new module with the function in excel.

Have not found a way to do this.

So not sure the best way to proceed?

Any suggestions?

Thanks,
Microsoft AccessMicrosoft ExcelVBA

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ryan Chong

>>I have an access query that I output to an excel spreadsheet
>>So I thought about having code in access to create a new module with the function in excel.

What's the method you're using to export the access query to Excel? We probably need to format the excel file after it's exported.
johnmadigan

ASKER
Here is the code for doing the export - I have a button that the user clicks - it exports the query to spreadsheet - then formats the open spreadsheet:


Private Sub btnAEC_Click()

'Export AEC AP spreadsheet
On Error Resume Next

Forms![AP_Aging]![SORT] = "00"

DoCmd.SetWarnings False

DoCmd.OpenQuery "AP_Aging_MT"

DoCmd.OpenQuery "AP_Aging_Totals_CT"

DoCmd.OpenQuery "AP_Aging_Totals_MT"

DoCmd.OpenQuery "AP_Aging_Subtotals_MT"

DoCmd.OpenQuery "AP_Aging_Report_Final_MT"

DoCmd.OpenQuery "AP_Aging_Grand_Totals_Append"

DoCmd.SetWarnings True

Dim filePath As String
Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
On Error Resume Next

    DoCmd.OutputTo acOutputQuery, "AP_Aging_Report_Export", acFormatXLSX, "AP_Aging-AEC.xlsx"

    Application.SetOption "Show Status Bar", True

    '========Formating VBA=========

    filePath = "c:\temp\AP_Aging-AEC.xlsx"

    Set xl = New Excel.Application

    Set xlBook = xl.Workbooks.Open("AP_Aging-AEC.xlsx")

    Set xlSheet = xlBook.Worksheets(1)

    xl.Visible = True

    With xlSheet
     
       
        For Each c In .Range(.Range("B1"), .Range("B" & .Rows.Count).End(xlUp))
            If Left(c, 6) = "Totals" Then
                c.EntireRow.Font.Bold = True
                .Range(c, c.Offset(0, 12)).Interior.ColorIndex = 8
               
            End If
        Next
       
       
         For Each c In .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
            If Right(c, 5) = "Total" Then
                c.EntireRow.Font.Bold = True
                .Range(c, c.Offset(0, 13)).Interior.ColorIndex = 4
               
            End If
        Next
       
       

        .Rows("1:1").Font.Bold = True
        .Range("A1").AutoFilter
        .Cells.Columns.AutoFit
       
       
    End With
   
    With xl
        .Goto xlSheet.Range("A1"), True
        .Goto xlSheet.Range("A2"), False
        .ActiveWindow.FreezePanes = True
        .Range("A1").Select
    End With


End Sub
PatHartman

Once you have exported the data to Excel and the user is interacting with it, what does it have to do with Access?  The code you need must go into a macro in Excel.  You can create a template and from Access, instead of creating a new workbook from scratch, you can copy the template and insert the data.  That way the macro will already be there.  Or, you can have Access create the macro by inserting it after it populates the data.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Ryan Chong

do you have a sample of AP_Aging-AEC.xlsx can share with us? (with crucial data masked)
SOLUTION
Jeffrey Coachman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Jeffrey Coachman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeffrey Coachman

...or do a similar web search of:
"Excel Sum colored cells"
or
"Excel Sum based on color"
ex:
https://www.google.com/?gws_rd=ssl#q=Excel+sum+cells+based+on+color
johnmadigan

ASKER
The CFO has a specific amount of cash each day he can use to pay past due bills.  He wants to highlight cells yellow that he is thinking about paying and show a running total so he see how much cash is left as he pick cells to pay.

When he is done he is going to send the sheet to the payable clerk - she will see the yellow cells and that will be the bills she will pay.

So was able to set up a excel sheet with a module and code to calculate a running total for all yellow highlighted cells.  So I looked at trying to take the code above to do the formating to an existign excel macro-enabled template.

Could not find a way to do this.

Any suggestion on how this could be done?  

I did some searching on sending access queries to excel templates - but could not find information on sending access query to macro-enabled templates.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeffrey Coachman

Glad I could help...