Posts by AJW

Hi All,

Does anyone know how to have a list of email addresses in Excel looped through to send a generic message?

For example column A cells would have email addresses running down it, column B cells would have the subject matter, column C cells would have the generic message.

Any ideas?

Thanks AJW

Re: Control Design Mode

All,

I've had a bitmore of a look into this and find that my checkbox numbers are blowing out......

CheckBox911111
CheckBox3111111
CheckBox5511111
CheckBox5911111
Etc.

I'm opening a master document making changes and then saving with a different file name.

When I try to exit design mode it says:

Can't exit design mode because Control "CheckBox35" can not be created.

Has anyone come across similar before?

Thanks

AJW

Hi All,

I've made a Word document and placed some Controls on it, checkbox's etc whilst in design mode.

I've exited design mode and saved the file, however every time I open it design mode is active.

Any clues on why this may be happening?

Thanks

AJW

Re: Open a password protected word file with the password

WillR

Thanks for the reply, my email notification needs to be updated.

I tried your solution but came up with an error regarding the object.

Did the following to resolve:

Word.Application.Documents.Open ("C:\ TEMPLATES\CONTRACT VARIATION .dot"), passwordtemplate:="checkopen", Visible:=True

Thanks for the pointers, greatly appreciated.

AJW

Hi all,

I have a number of password protected Word templates that I need to open from within Excel using VBA.

I use the following code to open the files but can not figure out how to send a password across?

Any help would be appreciated.

AJW

Sub wordtemp()
On Error GoTo ErrorHandler
Dim wdApp As Word.Application
Dim MyDoc As Word.Document
Dim mywdRange As Word.Range
Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
Set MyDoc = wdApp.Documents.Add("C:\Business\Force\TEMPLATES\New Templates\CONTRACT VARIATION .dot")
Set mywdRange = MyDoc.Words(1)
Exit Sub
ErrorHandler:
Set wdApp = Nothing
Set MyDoc = Nothing
Set mywdRange = Nothing
MsgBox ("Could not locate document")
End Sub

Edit: WillR - added code tags... please use them when posting code. Thanks

Hi all,

I've got one that sounds simple enough but has me stumped.

I'm after a Function solution in preference to VBA if possible.

The Problem!!

I have a data matrix that contains part numbers applicable to equipment models, ie.

Part > Wheel Rim Nut Bolt
ModelA W12 A26 N23 D47
ModelB A13 R33 P22 B67
ModelC Z16 A26 Q27 K57
ModelD W22 R14 N23 B67
ModelE A13 R26 P22 D47

On another worksheet I have a list of part numbers and descriptions.

Part Description
A13 Wheel
A26 Rim
B67 Bolt
D47 Bolt
K57 Bolt
N23 Nut
P22 Nut
Q27 Nut
R14 Rim
R26 Rim
R33 Rim
W12 Wheel
W22 Wheel
Z16 Wheel

1st. I'd like to count how many times each part number is found in the parts matrix, ie. N23 is found twice

Part Description Qty in Matrix
A13 Wheel
A26 Rim
B67 Bolt
D47 Bolt
K57 Bolt
N23 Nut
P22 Nut
Q27 Nut
R14 Rim
R26 Rim
R33 Rim
W12 Wheel
W22 Wheel
Z16 Wheel


2nd. I'd like to know the models that the part number suits.

Part Description Model 1 Model 2 Model 3
A13 Wheel
A26 Rim
B67 Bolt
D47 Bolt
K57 Bolt
N23 Nut
P22 Nut
Q27 Nut
R14 Rim
R26 Rim
R33 Rim
W12 Wheel
W22 Wheel
Z16 Wheel

As I said earlier if possible using Worksheet Functions but VBA if not.

Thanks in advance.

AJW

Found out the reason.

Instead of:

Application.ThisWorkbook.FollowHyperlink

It should have been:

Application.ActiveWorkbook.FollowHyperlink

AJW

Hi All,

Just back from a wonderfull 3 weeks off work, and as usual when your not there it all hits the fan.

I've got the following examples of code that allows me to hyperlink through VBA to a Lotus Notes Database.

The problem is that when I run it attached to a Sub Menu command it crashes Excel. When I step through it from within the VBE all is OK ????? Slightly bewildered

If anyone out there has any idea why it would be greatly appreciated.

BTW WinXP, XL2002

Thnx AJW

Sub LotusNotes_ValveDocLib()
Application.ScreenUpdating = False
On Error Resume Next
AppActivate "Lotus Notes"
If Not Err.Number = 0 Then
Err.Clear
MsgBox "Notes is Not Running", vbInformation, "WSM-Marketing"
Else
Application.ThisWorkbook.FollowHyperlink Address:="Notes://WARSYD1/4A256903007EE404/8525608C005E322585255D7C00545AF7", NewWindow:=True
End If
Exit Sub
End Sub

Sub LotusNotes_ISOGatePricing()
Application.ScreenUpdating = False
On Error Resume Next
AppActivate "Lotus Notes"
If Not Err.Number = 0 Then
Err.Clear
MsgBox "Notes is Not Running", vbInformation, "WSM-Marketing"
Else
Application.ThisWorkbook.FollowHyperlink Address:="Notes://WARSYD1/4A256903007EE404/8525608C005E322585255D7C00545AF7/01D498B498130A7F4A25690A001CB306", NewWindow:=True
End If
Exit Sub
End Sub

Sub LotusNotes_LeaveApplication()
Application.ScreenUpdating = False
On Error Resume Next
AppActivate "Lotus Notes"
If Not Err.Number = 0 Then
Err.Clear
MsgBox "Notes is Not Running", vbInformation, "WSM-Marketing"
Else
Application.ThisWorkbook.FollowHyperlink Address:="Notes://WARSYD1/4A25659A000C4FC9/8525608C005E322585255D7C00545AF7/ED0979DCA79B73A4CA256E450082284B", NewWindow:=True
End If
Exit Sub
End Sub

WillR / XL-Dennis,

Thanks guys, appreciate the feedback. it gives me a bit more insight as to how Crystal works.

Regards

AJW

WillR + Others,

Has anyone ever used the CRViewer9 control to generate reports from Excel?

I've seen Crystal Reports out put from another application and was fairly impressed. Wondered if it could be done in Excel?

Have placed a control on a user form but have no idea what to do next?

Any Ideas / Help would be great.

Thanks

AJW

Villa + others,

Figured it out, fairly simple once I stopped heading down one track and took another path. Must say that seem to be the way I've learnt most of my Excel VBA skills............ TRIAL & ERROR !

So here's the cure.......

I'm running Notes 6.5, by right mouse clicking on the toolbar area you get a list of available toobars (like with Excel).

1. Select the one that says address.
2. Open your database.
3. Go to the address toolbar and click the dropdown, the address for your database will show in the toolbar.
4. Copy the address.
5. Paste the address in the following code, close your database (not Notes) and run the code.
6. Hey presto up pops the database.
7. Also works for Documents within a database.
8. Enjoy !

Sub LotusNotes_CVXDocLib()
Application.ScreenUpdating = False
On Error Resume Next
AppActivate "Lotus Notes"
If Not Err.Number = 0 Then
Err.Clear
MsgBox "Notes is Not Running", vbInformation, "WSM-Marketing"
Else
Application.ActiveWorkbook.FollowHyperlink Address:="Notes://WARRIORSYD1/4A2565BF00171D6E/8525608C005E322585255D7C00545AF7", NewWindow:=True
End If
Exit Sub
End Sub

Regards

AJW

Liverspot,

Try creating it as a draft first and then send it.

Public Function NotesMailNewDraft(strSendTo As Variant, strSubject As Variant, strText As Variant, strcc As Variant, strbcc As Variant, strFilename As String)
Dim objNotes As Object, objNotesDB As Object, objNotesMailDoc As Object
Dim SendItem, NCopyItem, BlindCopyToItem, i As Integer, rtitem
Dim Msg As String
On Error Resume Next
AppActivate "Lotus Notes"
If Not Err.Number = 0 Then
    Err.Clear
    GoTo ExitF
Else
    On Error GoTo ExitF
    Set objNotes = GetObject("", "Notes.Notessession")
    Set objNotesDB = objNotes.GETDATABASE("", "")
    Call objNotesDB.OPENMAIL
    Set objNotesMailDoc = objNotesDB.CREATEDOCUMENT
    objNotesMailDoc.Form = "Memo"
    Call objNotesMailDoc.Save(True, False)
    Set SendItem = objNotesMailDoc.APPENDITEMVALUE("SendTo", "")
    Set NCopyItem = objNotesMailDoc.APPENDITEMVALUE("CopyTo", "")
    Set BlindCopyToItem = objNotesMailDoc.APPENDITEMVALUE("BlindCopyTo", "")
    objNotesMailDoc.SendTo = Application.Worksheets("Client Information").Range("EmailAddress").value
    objNotesMailDoc.Subject = Application.Worksheets("Client Information").Range("ProjectName").value & " " & Application.Worksheets("Client Information").Range("ProjectNumber").value
    Set rtitem = objNotesMailDoc.CREATERICHTEXTITEM("Body")
    objNotesMailDoc.Body = Application.Worksheets("Client Information").Range("ContactName").value & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & "Regards" & vbCr & " " & vbCr & Application.Worksheets("Client Information").Range("SalesEng").value & vbCr & "WWL"
    rtitem.ADDNEWLINE (1)
    Call objNotesMailDoc.Save(True, False)
    objNotesMailDoc.RemoveItem ("DeliveredDate")
    Call objNotesMailDoc.Save(True, False)
    Msg = "A draft E-Mail was successfully created and can be found in your Notes Drafts folder!"
    MsgBox Msg, vbInformation, "Notesmail Draft..."
    Call objNotes.Close
    Set objNotes = Nothing
    Exit Function
End If
ExitF:
Msg = "A draft E-Mail was not created! Please check your network connection and ensure you are logged into Lotus Notes."
MsgBox Msg, vbInformation, "Notesmail Draft..."
End Function




Sub ClientQuoteEmailNew()
Dim strSendTo, strSubject, strText, strcc, strbcc As String
Dim strFile As String
strSendTo = ""
strSubject = ""
strText = ""
strFile = ""
NotesQuoteNewDraft strSendTo, strSubject, strText, strcc, strbcc, strFile
Exit Sub
End Sub

Regards

AJW

Villa,

Did you get this one sorted?

I'm having the same problem and was hoping we could exchange ideas.

My code so far:

Sub LotusNotes_CVXLib()
Application.ScreenUpdating = False
On Error GoTo error:
Dim s As Object
Dim db As Object
Dim doc As Object
On Error Resume Next
AppActivate "Lotus Notes"
If Not Err.Number = 0 Then
Err.Clear
MsgBox "Notes is Not Running", vbInformation, "WSM-Marketing"
Else
Set s = CreateObject("Notes.Notessession")
' Function GETDATABASE(SERVER As String, FILE As String, [CREATEONFAIL])
Set db = s.GETDATABASE("WARRIOR1/AU/WAR/WERE", "WARRAU/Market/CVXLby.nsf")
' Call db.OPENMAIL
Call db.???
' Set doc = db.CREATEDOCUMENT
Set s = Nothing
Set db = Nothing
Set doc = Nothing
End If
Exit Sub
error:
End Sub

I've been having trouble with Call db.??? as I can't figure what to put there?

Regards

AJW

Can't figure out how to attach the file so the code is as below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = Range("TriggerValidation1").Address Then
GoTo 10
ElseIf Target.Address = Range("TriggerValidation2").Address Then
GoTo 10
End If
Exit Sub
10 Application.ScreenUpdating = False
Application.Range("H15").Activate
SendKeys "{F2}", True
SendKeys "{Enter}", True
Application.ScreenUpdating = True
End Sub

Place this in the code Worksheet code area and assign the range names TriggerValidation1 and TriggerValidation2 to your H11 & I11 cells.

Hit the escape key twice to cancel out of the validation message.

AJW

You could try playing around with the formula Validation Criteria however I think you'll find that the actual cell (H15) needs to be actively changed for Excel to trigger the Data validation. (I could be wrong though)

You could also try using condition formating as per attached example to highlight that the value has exceeded a set range.

Another though just came to me which involves a VBA solution to your problem. We could use a worksheet event to trigger the validation, I have included this in the attached example also.

AJW

Hi Dave,

Sorry to take so long to respond, I’ve been a little busy trying to get XLXP working.

I’ll start at the beginning and hopefully make it a little less confusing.

I have a file called MMLoad.xls which has the ISAddin property found in the VBE under ThisWorkbook set to True. This file is stored in the XLStart directory and loads when Excel is launched. The XLStart directory also normally contains the personal.xls workbook that is used to store peoples custom Macro’s, Personal.xls is also loaded at launch time and can be viewed through Windows > Unhide. MMLoad.xls however cannot be viewed through Windows > Unhide as it is seen by Excel as being an Addin even though it has an .xls extension.

MMLoad.xls controls the loading and unloading of my main code repository (MM.xls), MMLoad.xls also audits the users machine for username, operating system 95 / 98 / 2000 / XP / NT, Excel version 97 / 2000 / XP, connection status LAN / Dial Up / Stand Alone / ADSL / ISDN etc. When these parameters are defined a VBS (Visual Basic Script) is run external to Excel that brings the MM.xls addin into the XLStart directory and opens it with a password. MM.xls is also a Workbook with the IsAddin property set to true, it is also password protected which essentiall makes it a password protected addin that can only be accessed through MMLoad.xls

MMLoad.xls also audits the local C drive for the latest version of a package of Templates and Addins that I have developed and if a later version exists on the network it will download it external to Excel through a VBS.

All this as confusing as it sounds is in place due to security requirements. So to answer your question simply the *.xls Addin is loaded at launch time.

The problem I was having was that at load / launch time the code contained in ThisWorkbook Workbook_Open would not trigger. I tried many various combinations of coding from having the code I needed to run in Worbook_Open through to calling it from a separate module with Application.Run “My Macro”. Nothing was working, I has tried all various levels of security. I checked my XLStart folder location and discovered there was another one under My Documents folder (just like you said), I put MMLoad.xls in there and still the code would not trigger at startup, it would however trigger if I manually launched the file after Excel was running but still would not run at load time.

It dawned on me that I had had a similar problem before when running a macro and jumping to another before the first one had finished. I solved that problem by using OnTimeNow so I recoded my Workbook_Open code to Application.OnTime Now + TimeValue("00:00:01"), "My Macro". Hey presto it worked, I can only assume that having the code running while Excel was opening caused some kind of conflict that would open freeze up Excel. By using the delay of OnTime Now I was able to resolve the conflict by giving XLXP time to launch completely.

I am now faced with a new problem however in that the location for the XLStart folder is variable based on user name i.e C:\My Documents\watermaj\application\Microsoft\xlstart with watermaj being the variable for every different machine. I’m 90% of the way to resolving this by ripping the username from the registry as a variable and passing it to a hardcoded string.

Anyway I hope that serves to clarify my complex security and Addin loading process a little. If anyone else is having similar problems hopefully they can learn from my experience.

AJW

"This would make it an *.xla not *.xls Go to Tools>Add-ins and browse for it."

Nope. It's an *.xls workbook with the IsAddin property set to True. By setting the IsAddin property for ThisWorkbook to True it becomes an *.xls ADDIN. Not an *.xla Addin which is the usual.

It has all the features of an *.xla Addin in that you can not see any of the worksheets even though it has an *.xls extension. The sheets are not "hidden" like through the Format > Sheet > Hide or the Window > Hide commands, they are hidden like the worksheets in an *.xla Addin.

"Who suggested "a workbook with sheets hidden or very hidden" ?"

No one in particular, I've seen it done this way and it did not suit my purposes.

"Have you an *.xls or *.xla in there."

C:\Program Files\Microsoft Office\Office10\XLStart\Load MM.xls

It is however a xls file with the properties set to Addin.

" If the former try placing it in: C:Documents and SettingsOwnerApplication DataMicrosoftExcelXLSTART"

I don't have this path available. the only XLStart I have is as per the path above.

"You also haven't answered my question "

Which One?

AJW

It's an *.xls workbook with the IsAddin property set to True and VBE code password protected.

I didn't want a workbook with sheets hidden or very hidden as someone could get into the worksheet databases it contains, setting the workbook property to addin makes it harder as they need to get into it through the VBE. The databases are still available to lookup from other workbooks.

This file is placed in the xlstart directory and in XL97 works fine with the Workbook_Open event.

For some reason XP is not allowing the Workbook_Open to trigger through the XLStart directory. I've messed with all the security settings and still can't get it to happen.

AJW

Record and Read for straight forward stuff.

You'll definately need a reference book of some type for the serious stuff like defining variables, loops, if's, File System Objects etc.

I like Walkenbachs as above and also some of the books from Mr Excel (sorry Dave), also Hans Herber put out a great CD with stacks of code on it and fully searchable.

Other than that stick with this message board and be patient. Don't forget how many programmers MS has developing Excel. It's gonna take a little while to catch up and cotton on but keep at it.

AJW

mmmmm................... I'll look into it.

TA

AJW

No it wont work. It's not manually installed as an Addin through the Tools>Addins Menu.