Posts by Tony T

    Re: Worksheetloop

    You can test the name of the sheet before taking action on it.

    I modified MaxTO's code slightly.


    Re: Return Sql Query Results To Vba Variable Instead Of Cell

    Yes, there is a way to return an ado recordset to your subroutine and to extract the value of a field to a variable. It is slightly more complicated than using the Excel built-in query table feature.

    Try researching ADO recordsets and their many methods.

    Alternatively, you could just take the value off the spreadsheet and put it in a variable.

    Re: Open .xls File From .bat DOS File & Save As .csv

    Firstly, that is two arguments, since start C:\blacklist.xls is actually:

    c:\%programfiles%\microsoft office\office\excel.exe C:\blacklist

    Or something like that.

    Try setting an envirnmental variable.

    You can read an environmental variable that was set in a DOS batch file using the windows scripting host.

    In DOS

    Set Foldername = 2315t

    In your macro

    Set WshShell = CreateObject("WScript.Shell")
    Set WshSysEnv = WshShell.Environment("User")
    Foldername = WshSysEnv("Foldername")

    That should get the variable you set in the batch file.

    BUT!! there may be an easier way of doing it. So anyone can feel free to chime in here.

    EDITED: Changed the environment to User. It should work well enough.

    Re: Create Hyperlinks To Named Range Where Names Resides In Column

    Use Dave's code but, add the useless address argument. it's required (even when unused.)

    Try Dave's code with this modification:

    Re: Addins

    The open workbook event will trigger everytime you open Excel, as installed add-ins open with excel regardless of the workbook or link used to open the application.

    Have you considered using the workbook's open event to create a toolbar with a button that will trigger the vba code. This is the method I use for creating add-ins. YOu can also place a new menu item on one of the menus in excel.

    The workbook open event on an add-in should not be used to launch the add-in's code other than to create a button or menu item.

    If this code needs to run unattended, I would suggest a different method.

    If you need some help with the code for the toolbar button, let me know and I will throw some out there.

    Re: VBA Macro Code To Check Spelling In Range

    The spell check method does not return an object in Excel... But in Word, it is a different story. This method is only as accurate as the Word spell checker.

    It exports a worksheet to HTML and then opens it and Word. Spelling errors are identified and then found on the worksheet one-by-one.

    Try something like this:

    Re: Use Max Function In Vba, But Conditionally

    Try this. Just use the formula in a cell like this:


    Re: Place 1 line Of VBA Code Into Mutiple Lines

    I write my code like this so that I can see the arguments of the round function. I use this indent style to allow me to see the functions embedded in the round function more clearly.

    ColorCompare = Round( _[INDENT][INDENT][INDENT](111111 + Blue + Red + Green) / (Green + LenPaint) _ [/INDENT][/INDENT][INDENT][/INDENT][INDENT] [INDENT], 0 _ [/INDENT][/INDENT][INDENT][/INDENT][INDENT][INDENT])[/INDENT][/INDENT][/INDENT]

    And, yes... Round((111111 + 10.4 + 15.3 + 20) / (20 + 400) , 0) = 256

    EDIT: Man, is it tough to work with indents, but I think I got my point across.

    Re: 429 Active X Error When Running Email Code

    If it is the same activeX error that you originally described happening on the same line, then the chances are, a) they don't have Outlook installed at all or b) the installation has become seriously corrupted.

    Check your spelling and capitalization first. The "Outlook.Application" is case sensitive.

    Remove all refrences except those that you originally found checked.

    If there are different versions of Outlook, you can use the late binding technique that you have described. However, Outlook must be installed and installed correctly.

    Re: 429 Active X Error When Running Email Code

    OK... Being a compile error, the runtime will not execute a single line of code. All VBA code is compiled at runtime, before any action is taken. That is why the highlighted line happens to be the first line of code. It is also why it does not save anymore. It will have done nothing at all, as the error happens the instant you try to run the sub.

    This particular error has everything to do with the list we have been discussing. One of the items in the Tools-->Refrerences is missing from this machine.

    Look at this list and try to determine what is missing. Let me know.

    Re: Insert Row Where Cell In Column Is Repeated x Times

    Take 2:

    Sub insertFail()
        Dim r As Long
        r = 4
        Do While Cells(r, 1).Row <> Cells(Rows.Count, 1).End(xlUp)(2).Row
            If (Cells(r, 1) = Cells(r - 1, 1) And _
                Cells(r, 1) = Cells(r - 2, 1)) Then _
                Rows(r).Insert Shift:=xlDown
            r = r + 1
    End Sub