Replies

Another option might be to ask chrisgreaves if he has a tool that can do this or look on his website – I know he was working on something along these lines a long time ago…

I’ve used Prettycode in a previous life (but only in excel) and it could do all modules in a workbook but I’m fairly sure it couldn’t do all modules in all workbooks open – and I’m certain you had to have the file open (but apart from that, it’s well worth the money!)

Try Alt and Tab or Control and Tab – one of those should do what you want.

Brilliant – thanks for that. ( I won’t be re-editing the SQL – It was already making my eyes glaze over!!!)

These are error checking indicators – to turn them off go to Tools | Options | Error Checking and deselect “Enable Background error checking”.

again, clutching at straws…

You say you’re passing the SendTo in as a string. Have you tried passing it in as an array? Below is an outline of how I do it, but I’ve never gone for large distribution lists so don’t really know if this will help – we tend to set up mailgroups for each automated report. In the example below, the mailgroups could contain upto 30 people each. Would that work for you?

‘~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub SendDailies_SAPFGI()
Dim strSubject As String
Dim strDistList As Variant
Dim strBody As Variant

strSubject = “SAPFGIReport”
strDistList = Array(“EMEA DISTRIBUTION”, “AMI DISTRIBUTION”, “APR DISTRIBUTION”)
strBody = Array(“Today’s FGI report as at 08:00 GMT” , PATH_TEXTFILES & “SAPFGIReport.txt” , “regards” , “Brooke”)

Call SendLotusNotesEmail(strDistList, strSubject, , strBody)
End Sub
‘~~~~~~~~~~~~~~~~~~~~~~~~~~~

the main sub – with most code stripped out, is as below:

‘~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub SendLotusNotesEmail (varDistList As Variant, strSubject As String, Optional varCopyList As Variant, Optional varBody As Variant, Optional strPriority As String = “N”, Optional strImportance As String = “2”)

‘declare notes objects
‘declare standard variables
‘set up document
‘assign standard document values

‘assign passed document values
doc.Subject = strSubject ‘give it a subject
doc.SendTo = varDistList ‘set the SendTo field to the distribution list
doc.CopyTo = varCopyList

‘build body of email here if you want to

‘get rid of the thing
Call doc.SEND(False, doc.SendTo)
Call doc.Save(False, False)

‘kill all notes references
End Sub
‘~~~~~~~~~~~~~~~~~~~~~~~~~~~

HTH

I was going to apologise about the time-delay in replying, but I’m not sure that’s necessary?

I was hoping that you could do it by amending the code:

‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Call doc.Save(False, False)
Call doc.SEND(False, doc.SendTo)
‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

to

‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Call doc.Save(False, False)
Call doc.Activate
‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

However, .Activate is not a method of the NotesDocument Class: doc.Save does, however, put the document in the draft folder, so you could navigate to that and open it manually…

My mind is fuzzy when it comes to notes, but I’m sure I remember seeing code that does activate/open a document in the User interface, but I can’t find how to do it using NotesDocuments or NotesItems. It may be possible to get at the draft by the NotesUIView class so that you can then activate it using one of the other NotesUI classes. I’ll keep looking for you, but I’m afraid I can’t promise anything. Hopefully the manually opening the draft will tide you over.

Agreed. I find it curious that different people get different numbers, but I guess it is just that – a curiosity. FWIW, iseven and yield produce the same behaviour, but I doubt I’ll be investigating any further.

are you suggesting i don’t have it permanently running? I checked with the version i had, and have just checked with the most recent version on your website – all add-ins disabled, I get no names in the workbook and =isodd(25) returns #value, but =isodd still returns -154468285. curious

Does anybody know why,if i type “=isodd” (no brackets – I was playing and hit the enter key too soon) in a cell, I get the result “-154468285”? another addin using this as a defined name perhaps (I do have hyperion essbase installed, which does do some odd things on occasion,) or some other explanation?

It is possible – you’ve just got your brackets muddled. The following works for me:

=IF(OR(AND(A1>0,A10,B1<10)),"OKAY",0)

try changing the line “For c = 1 To c = 20” to “For c = 1 To 20” – any better?

thanks for the catch…

Here’s some quick and dirty code I cobbled together some time ago – there are probably tidier ways of doing this. I’m not sure, but the original source would probably have been either here, j-walk or chip (but all errors are mine entirely!) This works on the active workbook only.

Sub ListFuncsAndProcs()

Dim aComp
Dim WkBkComps
Set WkBkComps = ActiveWorkbook.VBProject.VBComponents
Dim RowVal As Integer, colval As Integer
Application.DisplayAlerts = False
On Error Resume Next 
Sheets("list of funcs and procs").Delete
On Error GoTo 0 
Application.DisplayAlerts = True

Sheets.Add.Name = "list of funcs and procs"
ActiveWindow.Zoom = 50
colval = 1

Cells(1, colval).Value = ".Name"
Cells(2, colval).Value = ".Type"
Cells(3, colval).Value = ".codemodule"
Cells(4, colval).Value = ".count of lines"

ActiveSheet.UsedRange.EntireColumn.AutoFit
colval = 2
Dim intCol As Integer

On Error Resume Next 
    For Each aComp In WkBkComps
        Cells(1, colval).Value = aComp.Name
        Cells(2, colval).Value = aComp.Type
        Cells(3, colval).Value = aComp.CodeModule
        intCol = aComp.CodeModule.CountOfLines
        Cells(4, colval).Value = intCol
Dim x
RowVal = 5
Dim strtemp
            For x = 1 To intCol
            strtemp = aComp.CodeModule.Lines(x, 1)
If Left(strtemp, 8) = "Function" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 16) = "Private Function" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 15) = "Public Function" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 3) = "Sub" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 11) = "Private Sub" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 10) = "Public Sub" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
            Next
        colval = colval + 1
    Next
End Sub

yes, I experience the same – “this query returned no data…” – same excel as you – 2002 sp3