Recent content by cheerio

Instead of looping through character by character you could define a VBA function as follows: Public Function HasLowerCase(strTest As String) As String If (0 = InStr(1, strTest, UCase(strTest), vbBinaryCompare)) Then HasLowerCase = "Y" Else HasLowerCase = "N" End If...

It might be worth looking at temporary work tables. I will use pseudocode rather than pure SQL to illustrate the idea. INSERT INTO TEMP1 (fields F1,F2) FROM TEST unique combinations where K is 1 or 2 INSERT INTO TEMP2 (fields F1, F2, F3) FROM TEST, TEMP1 unique combinations for F1, F2, and the...

why is the third field prefixed with tbl which is not a table name or alias?

is not true. The code can become corrupted - see http://www.appspro.com/Utilities/CodeCleaner.htm

Does the user actually need to see the file? Otherwise you can stop the user from doing things by making the application invisible.

does this help: http://blogs.msdn.com/excel/archive/2009/01/30/hidden-and-invisible-objects.aspx

I have some VBA that reformats a workbook of data downloaded from another system. There is only formatted data - no formulae. For two of my users the code was running very slowly - typically the task was taking a minute per sheet where I expected under 5 seconds per sheet. All users have...

Consider storing the workbook as a template?

In Excel go to a code window (Alt F11) and select the Object browser. Change the library dropdown to Excel and in the lower window click on application. To the right you will see Run. Right click on Run and select Help.

What value does the machine have for application.LibraryPath This defines where Excel will look for addins.

This is a long shot. If there were control characters (like line feeds) in the backend data then just possibly MSQuery and Excel might see them differently. Seems unlikely but in the absence of other ideas ...

This can easily be achieved by writing a simple VBA user defined function. For example, thisworkbook.CustomDocumentProperties("owner") would return the owner custom property. If you need help on VBA repost in VBA Visual Basic for Applications (Microsoft).

Why not use an Excel Style - say called InputData - that has protection turned off and has shading turned on. Just format the data entry cells with that style.

A good explanation of use of SUMPRODUCT in this way can be found at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Data Validation - first tab Allow Any value - Second tab is where you put your message