Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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,
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,
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
to create a module pls try
http://www.cpearson.com/excel/vbe.aspx
Regards
to create a module pls try
Sub CreateProcedure()
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim S As String
Dim LineNum As Long
' Use the next two lines to create a new module for the code
'Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
'VBComp.Name = "NewModule"
' OR use the following line to use an existing module for the code
'Set VBComp = ThisWorkbook.VBProject.VBComponents("Module2")
Set CodeMod = VBComp.CodeModule
LineNum = CodeMod.CountOfLines + 1
S = "Sub HelloWorld()" & vbCrLf & _
" MsgBox ""Hello, World""" & vbCrLf & _
"End Sub"
CodeMod.InsertLines LineNum, S
End Sub
http://www.cpearson.com/excel/vbe.aspx
Regards
>>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.
>>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.
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_App
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_Agin
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
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_App
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_Agin
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
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 too am confused about the need for two programs to do this...
Summing up selected (or colored) cells is quite easy to do in Excel;
At the most basic level you can simply see the total of any range of selected cells (even cells selected non-contiguously)
Simple select all the cells you want to sum, then in look in the bottom right section of the Excel window to see the Average, Count and Sum of those cells.
JeffCoachman
Summing up selected (or colored) cells is quite easy to do in Excel;
At the most basic level you can simply see the total of any range of selected cells (even cells selected non-contiguously)
Simple select all the cells you want to sum, then in look in the bottom right section of the Excel window to see the Average, Count and Sum of those cells.
JeffCoachman
highlight cells of the report yellow for the invoices he is thinking of paying.
See the threads here to find ways to sum cells based on color
...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
"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
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.
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.
Ultimately, ...this can be done in Access
The downside is that with this system in Access, you need a lot more "controls" in place.
For example,:
How would you keep track of all the invoices paid in a certain time frame?
How are you "resetting" the next round of invoices to be paid?
What if something changes and you suddenly don't have enough money to pay the invoices you "paid"?
...etc
Once you make the jump to Access, a lot of the stuff you used to do in Excel, will not meet the more rigid requirements of a true database system.
In any event, here is a vary simple example of how to achieve what you are after in Access.
Create a form to select the invoiced to be paid. (Selected Rows will turn yellow)
Print a Report of this data (again with the selected invoices in Yellow), and give to AP clerk to pay.
I tried to add in some additional fields that you may need (Paid, DateTmePaid, ...?)
But these additional fields may, or may not be needed, ...in any event, these additions go far beyond the scope of your original question.
Let me know.
JeffCoachman
Access--EEQ28735478-PayInvoices.mdb
The downside is that with this system in Access, you need a lot more "controls" in place.
For example,:
How would you keep track of all the invoices paid in a certain time frame?
How are you "resetting" the next round of invoices to be paid?
What if something changes and you suddenly don't have enough money to pay the invoices you "paid"?
...etc
Once you make the jump to Access, a lot of the stuff you used to do in Excel, will not meet the more rigid requirements of a true database system.
In any event, here is a vary simple example of how to achieve what you are after in Access.
Create a form to select the invoiced to be paid. (Selected Rows will turn yellow)
Print a Report of this data (again with the selected invoices in Yellow), and give to AP clerk to pay.
I tried to add in some additional fields that you may need (Paid, DateTmePaid, ...?)
But these additional fields may, or may not be needed, ...in any event, these additions go far beyond the scope of your original question.
Let me know.
JeffCoachman
Access--EEQ28735478-PayInvoices.mdb
Premium Content
You need an Expert Office subscription to comment.Start Free Trial