Replies

A user defined function like this one may work.

Function USD_SUMOFCELL_AY5()
Dim XSUM
Dim SH

XSUM = 0

For Each SH In ActiveWorkbook.Sheets
XSUM = XSUM + SH.Range(“AY5”)
Next SH
USD_SUMOFCELL_AY5 = XSUM

End Function

Reference it from any worksheet with the formula
=USD_SUMOFCELL_AY5()

Make a pivot table. Use “the list:” as the row source and “Count of the list:” as the data field. Leave the column source blank.

Good luck. Excel is a great general purpose application so it might prove adequate.

Personally, I’ll stick with QuickBooks.

ITT Tech is the most widely known community-type college in Indy. They frequently advertise for instructors of Use-Of-MSOffice classes.

You might consider a temp agency. I twice temp’d as an accounting geek, only to be picked up full time as an IT geek.

Indiana is not the Hi Tech Mecca that most people think it is. Sometimes the IT section of the Sunday Ads is much less than one page. And they still list all things IT under the “Computer” heading.

Don’t get me wrong. I love Indiana. It is my home. We keep things simple here. Like the rules for the Indy 500: 1. Drive fast. 2. Turn left.

Thanks Mark. That link is very helpful.

Looks like I can realize a lifelong dream:
Captain Piccard will command “Engage!” whenever the “Go” button is clicked.

You’re right. It’s the “Default Sound” in the control panel.

I wonder if there is a sound object collection that one can reference to associate sounds to events?

See return attachment.

I used the histogram tool from the analysis took pack (add-in under Tools Menu). Then plotted the Bin (x axis) against Frequency (y axis). The Histo tool will graph for you as well.

You can easily see the distinctive bell curve of a mostly normal distribution. How normal is it? Give the Descriptive Statistics tool a try. It will give you the quantitative stuff statisticians crave.

This article deals with your situation.
http://support.microsoft.com/support/kb/ar…s/Q171/9/55.asp

Good luck.

Thanks Rory. I was doing it the hard way. Your suggestion works great.

Here’s the SQL for a query like I described. I use Table1 as the table and Ad1 thru Ad6 as the fields. Readability of this syntax is less than favorable, but it is quite unambiguous. (That’s good)

SELECT Table1.*, 
Cust_Instr("District",[Table1]![Ad1],[Table1]![Ad2],[Table1]![Ad3]) AS District, 
Cust_Instr("TX",[Table1]![Ad3],[Table1]![Ad4],[Table1]![Ad5],[Table1]![Ad6]) AS TX
FROM Table1;

Put that verbage into a query by using the SQL VIEW option under the VIEW menu when you have the query design screen open. Just paste the verbage over whatever is in there. Then replace table and field names as needed.

I spoke of the excel macro recorder. Thanks for the clarification.

Jaz,
Will this query idea work?

Do a Select * of the table into a new query. Then add two new fields: District and TX, which are determined using the following function:
*—————— Put in a public module —————————*
Function Cust_Instr(String_To_Find, F1, F2, F3, Optional F4) As Boolean
‘Returns true if the text String_To_Find is found
‘among the arguments F1 thru F4.
Dim f_All, i
Cust_Instr = False

If IsMissing(F4) Then F4 = Null

f_All = F1 & F2 & F3 & F4

i = InStr(f_All, String_To_Find)

If Not (i = 0) Then Cust_Instr = True

End Function
*————————————-*

The expression for the district field is District:= Cust_Instr(“District”, YourTable!Field1, YourTable!Field2, YourTable!Field3)
Likewise for TX:= Cust_Instr(“TX”, YourTable!Field4, ….)

Base the report off the query instead of the table. You should be able to group on District (true or false) and TX (true or false).

Moving the code from excel VBA to an access module is something I would strive to do. It can be tricky, though. You have to know your excel objects and reference them with object variables in access — something that is not exactly intuitive. Start with something simple and get it working from access. Use the macro recorder and the object browser. Learn as you go.

Before long you will cast recordsets into ranges and spin them into pivot tables with charts and trendlines and… You get the idea. Good luck.

My preference is to write the excel macro in access and run from there, but you probably have a lot of work tied up in this already. So here’s something that should suit your situation.

Sub Run_Excel_Macro() ‘TransferredSS As String) ‘The argument is the path and file name of the
‘workbook you created with the TransferSpreadSheet Action. I have assumed the action has
‘already been done.

‘Set a reference to excel in order to run this macro. From the Tools menu,
‘ select References…. , then check the MS Excel x.0 Object Library box.
Dim xla As Excel.Application
Dim xlsM As Workbook ‘this is for the workbook that contains the macro that you want to run.
Dim xls As Workbook ‘for the Transfered Spreadsheet

Dim TransferredSS As String
Dim MacroWBPathAndFileName As String ‘for the Macro WorkBook Path And File Name
Dim MacroName As String ‘for the name of the macro to run
‘Lets give these some value
MacroWBPathAndFileName = “C:My DocumentsExcelTest1.xls”
MacroName = “Macro1”
TransferredSS = “C:My DocumentsExcelBook1.xls”

‘Launch Excel
Set xla = New Excel.Application
‘Make it visible
xla.Visible = True

‘Open the Transferred Spreadsheet
Set xls = xla.Workbooks.Open(TransferredSS)

‘Open the macro workbook
Set xlsM = xla.Workbooks.Open(MacroWBPathAndFileName)
‘While xlsM should be the active workbook, lets just make sure because the “Run” command
‘ that follows is meaningful only in the context of the active sheet.
xlsM.Activate
xla.Run MacroName

‘The macro runs. Whether control returns to Access automatically depends on your excel macro.

‘Assuming you want to save and close everything,
xls.Save: xls.Close
xlsM.Save: xlsM.Close

xla.Quit
‘That’s all folks.
End Sub

Please clarify:

Are you trying to add an access table as a new worksheet to an existing excel file?

Or do you want to append an existing worksheet of an excel file with the data in an access table?