Posts by Fencliff

Re: Return Nth Value From Single Column Range

WHY exactly are you bent on using Choose, anyway?

Re: Find 11 Not 1

If you look for value 11, the value 1 should not be found. I'm guessing you meant it the other way around?

To force an exact match, use the argument LookAt:=xlWhole :

Set Found = ActiveSheet.Columns("A:B").Find(what:=wht, lookat:=xlWhole)

Edit: Whoopsie, beaten like a dog.

Re: Hiding A Selection Of Cells

Well, by conditional formatting you can only make the cell values appear hidden with setting the font color to the cell background color, not actually hide the cells.

However to simplify the conditional formula, you can set up a formula using OR() in one cell that returns True if any of your five cells' value is "other". Then in the conditional formatting you can just refer to that cell.

Re: Activate Webpage Button Where 2 Have Same Name

I mean that you should check the contents of the doc.body.innerhtml property to find out what exactly is loaded into the HTMLDocument object at the time of the error, and you should "view source" of the web page in a browser, and compare these two.

The easiest way to inspect the .innerHTML property is to go to debug mode after the error throws, and in the immediate window type

?doc.body.innerhtml

Re: List Names Of Workbook Files In Folder

Also missing the trailing backslash from the path...

Re: List Names Of Workbook Files In Folder

jindon,

I don't get your code. As far as I know, the line "Dir(myDir & "*.xls")" would return the first .xls file in the folder, and then enter an infinite loop. What's going on there?

Re: Activate Webpage Button Where 2 Have Same Name

What throws the error this time?

Well, can you check the state of the Doc object upon the time of crash (take doc.body.innerHTML and compare it to the source of the web page - are these identical?)

Other than that, I'm afraid I can't really help without being able to access the website.

Re: Udf Name Vba Caller

Unfortunately VBA doesn't have much capacity for reflection or metaprogramming in general.

I think the best way to do it would be to declare module-level constant to hold the sheet name, and a local constant to hold the name of each procedure, and then pass these as strings to your class instance.

There is also the VBA extensibility library that lets you access the VBA project structure. I don't have much experience there, and I don't believe it will be of any help, but this would be my best guess.

Re: Automatically Collate Data From Forms Onto A Table

There was nothing pertaining to SQL or ADO in the original post - there are other solutions to the same problem. It was me who replied with a SQL-related solution.

Re: Activate Webpage Button Where 2 Have Same Name

Hmm, I uploaded the bit of HTML you posted onto my server and tested it, getElementById finds the correct control.

Here's how to do what you originally requested, but this is still just a workaround to the real problem. However that problem is really hard to troubleshoot without access to the webpage itself.

Sub tct()
     
    Dim rng                 As String
    Dim btn                 As HTMLButtonElement
    Dim btnContractSearch   As HTMLButtonElement
    Dim colButtons          As IHTMLElementCollection
    
    Dim Doc                 As HTMLDocument
     
    rng = Worksheets(1).Range("e2").Value
     
    Dim IE As SHDocVw.InternetExplorer
    Set IE = New SHDocVw.InternetExplorer
     
    IE.navigate "http://58.27.200.20/board/"
    IE.Visible = True
     
    Do While IE.readyState <> READYSTATE_COMPLETE
        Application.Wait Now + TimeValue("0:00:01")
     Loop
     
    Set Doc = IE.document
     
    'Get collection of all nodes by the name btnSearch
    Set colButtons = Doc.getElementsByName("btnSearch")
    
    'Loop through the collection until we find one with tabIndex == 7
    For Each btn In colButtons
        If btn.tabIndex = 7 Then
            'Found it!
            Set btnContractSearch = btn
            Exit For
        End If
    Next btn


    btnContractSearch.Click
     
End Sub

Re: Automatically Collate Data From Forms Onto A Table

Hi,

You can insert data into a table-formed workbook with ADO. Here's a crude example (suggest you add error handling, at least):

Sub InsertDataWithADO_Example()


    'Your central file's name
    Const sCENTRAL_FILE     As String = "\\shared-drive\folder\file.xls"
    
    Dim sConnectionString   As String
    Dim sSQL                As String
    Dim adoConnection       As ADODB.Connection 'Requires reference to Microsoft ActiveX Data Objects library
    
    'Connection string to the file we want to write to
    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sCENTRAL_FILE & ";Extended Properties=""Excel 8.0; HDR=YES"";"
    
    'SQL command with placeholders
    sSQL = "Insert Into [Sheet1$] ([Name], [Telephone], [Date]) Values ('{Name}', '{Telephone}', '{Date}');"
    
    'Replace placeholders with the actual values from your form
    sSQL = Replace(sSQL, "{Name}", YourForm.TextBox1.Value)
    sSQL = Replace(sSQL, "{Telephone}", YourForm.TextBox2.Value)
    sSQL = Replace(sSQL, "{Date}", YourForm.TextBox3.Value)
    
    'Open connection to the workbook
    Set adoConnection = New ADODB.Connection
    adoConnection.Open sConnectionString
    
    'Execute the insert statement
    adoConnection.Execute sSQL
    
    'Clean up
    Set adoConnection = Nothing
    
End Sub

You will obviously need to change the reference to the central file on the top of the procedure, as well as the correct field names in the SQL string and references to the fields in your userform.

The first row of the target sheet needs to contain headers, and the data must contain no entirely blank rows.

Re: Reading Spinner Properties

Here's a fixed version (min and max are properties of the underlying control, not the shape). You also need to verify that the control is a of FormControlType 7 (xlSpinner). Also, you don't need to activate a worksheet to work on it.

Sub imposta_Min_e_Max()


    Dim num_controlli   As Integer
    Dim controllo       As Shape
    Dim foglio          As Worksheet
    
    num_controlli = 2
    
    Sheets(1).Activate
    
    For Each foglio In ThisWorkbook.Worksheets
    
        For Each controllo In foglio.Shapes
        
            If controllo.Type = msoFormControl Then
            
                If controllo.FormControlType = xlSpinner Then
                
                    Sheets("application_settings").Range("A" & num_controlli).FormulaR1C1 = controllo.Name
                    Sheets("application_settings").Range("B" & num_controlli).FormulaR1C1 = foglio.Name
                    Sheets("application_settings").Range("C" & num_controlli).FormulaR1C1 = controllo.ControlFormat.Min
                    Sheets("application_settings").Range("D" & num_controlli).FormulaR1C1 = controllo.ControlFormat.Max
                    
                    num_controlli = num_controlli + 1
                    
                End If
                
            End If
            
        Next controllo
        
    Next foglio
    
End Sub

Re: Activate Webpage Button Where 2 Have Same Name

It's hard to debug this when we don't have access to the specific website, but it might be that you get the error due to the fact that the document is not fully initialized. If memory serves me right, the .ReadyState flag is not 100% reliable. If you put a Stop statement before assigning value to the Doc variable, wait for a good while for the page to load, and then continue the execution, does it work?

Re: Environ Command In Vba To Get Logged Windows User Name

Simple, yes. But beware when using the Environ function in general: the environment variables may not be present in all systems, and some are editable by users, so check your output and have a API fallback.

Using Environ("USERNAME") should be safe, though - although it may return "Administrator" on some Windows Server configurations, even though the user logs on with a different name.

Hi All,

I have returned from vacation, and it's time to get back to business: dicking around in Excel VBA while pretending to be working Today I thought: wouldn't it be nice, if in Excel strings were true objects with methods. Well, why couldn't they?

If in VBA strings were objects, you would of course instantiate them like any other object:

Dim myString As New CString
myString = "Hello World!!!$$$????"

Now, these objects would of course have methods. And maybe the the methods would return a copy of the string object, you could link these methods together. Maybe we would want to remove all non-alphabet characters (I don't know why they're there in the first place...), reverse it, turn it to upper case and print it to the immediate window:

myString.Remove("!").Remove("$").Remove("?").Reverse.UpCase.Inspect

I guess that's ok, but wouldn't it be more elegant to remove the non-alphabets with regular expressions?

myString.Remove("[^a-z A-Z]", RegularExpression).Reverse.UpCase.Inspect

Yeah, that's better. And there is no reason why these methods couldn't extend the functionality above "normal" string manipulation, too. Let's assume for a while that you have a text file, where you want to programmatically replace all occurrences of "{date}" with the current date and write the new contents back into a file.

myString.ReadFromFile("C:\template.txt").Replace("{date}", Format(Date, "dd-mm-yyyy")).WriteToFile "C:\today.txt"

I think I'd like that.

If you take a look at the code, you notice that there are some useful methods, and some less useful, just simple wrappers for existing functionality, that I might remove down the line, because they add some overhead to the memory footprint of the objects instantiated from this class. However, I think there's a lot of interesting functionality I haven't thought of. What kind of string manipulation you often do? Any cool ideas? I will add any good suggestions and post the updated code here.

I haven't had a chance to write any documentation on this, but take a look and play around. I tried to include the code for the impatient and curious, but apparently the post was too long, and all the text got deleted. A word of warning: Do not copy paste the code into your project!. The Value property of the class is set as default to enable using 'myString = "Something"' instead of 'myString.Value = "Something"'. The default property also enable you to pass a CString object into anywhere that expects a string - VBA knows to pass the value instead of the object automatically. This attribute does not get carried over if you copy the code. You can either download the workbook and drag the class to your own project, or you can download the text file and import it into your project.

For a reference, here's a list of the methods and properties of the class. As a somewhat unorthodox design, I have assigned some functionality as property getters, when they would probably make more sense as methods. That's because apart from converters (ToDate, ToStringArray etc.) I wanted all the methods to return a linkable CString object, and the little icon on the intellisense menu gives a nice distinction. This list with descriptions and comments can be found in the attached workbook.

Methods
Append(sText)
Chomp(sEnd)
Chop
DownCase
Insert(sText, lPos)
Inspect
ProperCase
Quote
ReadFromFile(sFile)
Remove(sText)
Replace(sFind, sReplament)
Reverse
SwapCase
ToBoolean
ToCStringArray(sDelimiter)
ToDate
ToDouble
ToInt16
ToInt32
ToStringArray(sDelimiter)
Trim
UpCase
URLDecode
URLEncode
WriteToFile(sFile)

Properties
ByteLength
Contains(sSubString)
CountOf(sSubString)
Equals(sString)
FirstIndexOf(sSubString)
IsEmpty
LastIndexOf(sSubString)
Length
Match(sPattern)
Pointer
Value

Re: Fastest Lookup Method

Only in VBA, that was sort of the point of the exercise, since I don't exactly know how OP was planning on implementing the bit twiddling for a jump table on a worksheet. Exact matches, naturally.

Re: Fastest Lookup Method

Dave,

The table looked like this:

K1 Value1
K2 Value2
K3 Value3
...
K30 Value30

I picked a short key, so the hash table wouldn't have much unfair advantage (if the differentiating character would have been, say 10th char in the string, the string comparison overhead would've probably made the lookups even slower.

And the test was ran using WorksheetFunction.VLookup.

Re: Global Workbook_open

You could go to File > Properties and look through the tabs, if there is any indication of the template origin of the file (most likely in the Custom tab). If yes, you can access them by Workbook.BuiltInDocumentProperties and Workbook.CustomDocumentProperties to verify that the workbook is borne of a template.

Re: Fastest Lookup Method

Did not read previous post right. Nevermind

Re: Global Workbook_open

Andy Pope already gave you the answer, but here's my contribution: THIS. IS. A. HORRIBLE. IDEA.

Popping up a message box every time user opens/creates a workbook? Christ, if this happened in my company, I would probably find the culprit and adjust their outlook with a clue-by-four. Not to mention all the user macros that would break.

Isn't there any way you can detect whether something is a xpressv1.8 template or whatever?