Posts by Tony T

Re: Paste Chart From Sheet, Move To Next Slide

Try this line:

Activewindow.View.GotoSlide ActiveWindow.View.Slide.slideindex + 1

Re: Worksheetloop

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

I modified MaxTO's code slightly.

Sub LoopThroughActualCashFlowWorksheets() 
    Dim ws As  Worksheet 
    Workbooks("Actual Cash Flow.xlsx").Activate 
    For Each ws In Workbooks("Actual Cash Flow.xlsx").Worksheets 
        If len(ws.name) = 4 and isnumeric(ws.name) then
        'OR
        'If left(ws.name, 2) = format(year(date),"YY") then 
            ws. Range("D1:E" & WorksheetFunction.  CountA(ws.Range("E:E"))).Copy 
            ws.Range("I2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True 
            Application.CutCopyMode = False 
        End if
    Next ws 
End Sub

EDITED WITH SOME CORRECTIONS.

Re: Worksheetloop

And get rid of the exit for

Re: Worksheetloop

You need to activate the sheet first. Just add ws.activate as the first line inside your loop.

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:

Sub CreateHLs() 
    Dim rCell As  Range 
    Dim strName As String 
     
    With Sheet1 ' CodeName
        For Each rCell In .Range("A1", .Cells(.Rows.Count, 1).End(xlUp) _ 
            ).SpecialCells(xlCellTypeConstants, xlTextValues) 
            strName = rCell 
            .Hyperlinks.Add Anchor:=rCell(1, 2), Address:="", SubAddress:=strName 
        Next rCell 
    End With 
     
     
End Sub

Re: Determine Read-Only Status of Zip File

Sorry, but it would seem to me that the path is just the path. The thisworkbook.path would only return the route and path of the workbook and not the filename. have you tried to use thisworkbook.fullname instead?

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: Activate Userform Without Mouseclick In Workbook_open: Windows Scheduler

What is the executable line from the scheduled task? Are you calling the workbook directly or are you calling excel to open the workbook?

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:

Option Explicit


Sub HighlightSpellingErrors()
    Dim oWord As Object
    Dim oDoc As Object
    Dim rngSpelCheck As Range
    Dim FSO As Object
    Dim oFolder As Object
    Dim oPublishObject As PublishObject
    Dim oSpellingErrors As Object
    Dim oError As Object
    Set rngSpelCheck = ThisWorkbook.Sheets(1).UsedRange
    Set oWord = CreateObject("Word.application")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetSpecialFolder(2)
    Set oPublishObject = ActiveWorkbook.PublishObjects.Add( _
        SourceType:=xlSourceRange, _
        Filename:=oFolder.Path & "\TempHTML.html", _
        Sheet:=rngSpelCheck.Parent.Name, _
        Source:=rngSpelCheck.Address, _
        HtmlType:=xlHtmlStatic)
        oPublishObject.Publish (True)
    Set oDoc = oWord.Documents.Open(oFolder.Path & "\TempHTML.html")
    oDoc.Range.Text = LCase(oDoc.Range.Text)
    Set oSpellingErrors = oDoc.Range.SpellingErrors
    For Each oError In oSpellingErrors
        rngSpelCheck.Find(oError).Interior.Color = RGB(255, 0, 0)
    Next oError
    oDoc.Close False
    Set oDoc = Nothing
    oWord.Quit
    Set oWord = Nothing
    Kill oFolder.Path & "\TempHTML.html"
End Sub

Re: Use Max Function In Vba, But Conditionally

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

=maxif(A2:A9,"H",B2:B9)

Function MaxIf(rngLocation As Range, strCode As String, rngAttendance As Range)
    Dim lngCount As Long
    Dim varArray()
    ReDim varArray(1 To Application.WorksheetFunction.CountIf(rngLocation, strCode))
    lngCount = 1
    For Each acell In rngAttendance
        If rngLocation.EntireColumn.Cells(acell.Row) = strCode Then
            varArray(lngCount) = acell
            lngCount = lngCount + 1
        End If
    Next acell
    MaxIf = Application.WorksheetFunction.Max(varArray)
End Function

Re: Return A Range

You need to redim it.

Dim NewRange()

ReDim NewRange(1 To timeperiods)

Re: Max Number Of Arguments?

IRAValue should read IRAValueat70.

Option Explicit is your friend.

Re: 429 Active X Error When Running Email Code

You are very welcome. That is why we are all here.

Experience is a good teacher. I am sure you will think of it next time. Good luck and happy coding.

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: 429 Active X Error When Running Email Code

Is there an error message, or does it just fail silently?

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
    Loop
End Sub