Posts by peterhw

peterhw · Sep 26th 2013

Hope the file uploaded OK.
I just do not understand why this Simple Code fails at the first hurdle
So basic I am sure but like to understand why this fails - i.e. not just the solution

[ATTACH=CONFIG]56563[/ATTACH]

Code

Private Sub CommandButton1_Click()
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNameSpace("MAPI")
    Set myFolder = myNameSpace.GetDefaultFolder(olFolderContacts)   '   <<<<<<<<<<<<Fails here with INVALID PROCEEDURE CALL OR ARGUMENT
    Set myNewFolder = myFolder.Folders("IMS")
    'The following Visual Basic for Applications example returns the first folder.
    Set myNewFolder = myFolder.Folders(1)
End Sub

peterhw · Sep 25th 2013

Re: Outlook contact information

Many thanks.

Just found the Notes field - called .body
Still like to save to another folder if I could find a solution

peterhw · Sep 25th 2013

I have the following code (bits copied from anywhere!) which seems to work................ BUT....

why is "Const olContactItem = 2" used
how do I add Notes ? '.Notes = "what a load of rubbish" doesn't work and I just can't find the correct field
how do I save to an alternative folder (in this case 'IMS')

Appreciate any help

Code

Function AddOlContact()Dim olapp As Object
'Dim DestFldr As MAPIFolder
    On Error GoTo Error_Handler
    Const olContactItem = 2
    Set olapp = CreateObject("Outlook.Application")
    Set olContact = olapp.CreateItem(olContactItem)
    With olContact
        .FirstName = "FirstName"
        .LastName = "LastName"
        .JobTitle = "no job title"
        .CompanyName = "ACME Trading"
        .Categories = "1_IMS_Work"
        '.Notes = "what a load of rubbish"
        '.Move DestFldr:="IMS" '// moves the contact to the indicated folder
        .Save
        'use .Display if you wish the user to see the contact pop-up
        '.Display
    End With
Error_Handler_Exit:
    On Error Resume Next
    Set olContact = Nothing
    Set olapp = Nothing
    Exit Function
Error_Handler:
    MsgBox "EXCEL has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: AddOlContact" & vbCrLf & "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Display More

peterhw · Apr 28th 2012

Re: Accessing word macros from excel

Quote from sleeper108;606362

I am getting a

Dim my_replacements(2, 2)
[/code]
.

the above only defines the following cells. You need to replace with Dim my_replacements(2, 9) - this should eliminate the subscript error

[TABLE="width: 56"]

[tr]

[td]

my_replacements(0,0)

[/td]

[/tr]

[tr]

[td]

my_replacements(0,1)

[/td]

[/tr]

[tr]

[td]

my_replacements(0,2)

[/td]

[/tr]

[tr]

[td]

my_replacements(1,0)

[/td]

[/tr]

[tr]

[td]

my_replacements(1,1)

[/td]

[/tr]

[tr]

[td]

my_replacements(1,2)

[/td]

[/tr]

[tr]

[td]

my_replacements(2,0)

[/td]

[/tr]

[tr]

[td]

my_replacements(2,1)

[/td]

[/tr]

[tr]

[td]

my_replacements(2,2)

[/td]

[/tr]

[/TABLE]

peterhw · Apr 17th 2012

Re: Accessing word macros from excel

sorry if I had been at cross purposes. I had gone to what I thought was the bottom of the thread but was in fact only the end of page 1. Now found page 2 .

Many thanks for all your support.

PS - just edited to ADD I have now run the code and it works - many , many thanks

peterhw · Apr 17th 2012

Re: Accessing word macros from excel

Thought I had made a reply but can't locate.

Now made alterations and the following appears to work correctly except that the replacements are not made.
WORD opens
WORD saves to file ProblemsA
WORD quits
...but no replacements have been made (I changed to "and" for multiple replacements)

Code

Sub FindReplaceSample()         '// To test this code, paste it into an Excel module
Dim my_replacements(2, 2)
Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
Dim wrdDoc As Object '// Word.Document       same
Dim iTemp As Integer
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Open("D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\Problems.docx")
    Set wrdDoc1 = wrdApp.ActiveDocument
    wrdApp.Visible = True
    my_replacements(1, 1) = "and"
    my_replacements(1, 2) = "xxxxxxx"
    
    For iTemp = 1 To 1      '       reaches breakpoint 1 OK
        'With wrdDoc.Range.Find
        With wrdDoc.Content.Find                        '// Search value in cell
            .Text = my_replacements(iTemp, 1)
            .Replacement.Text = my_replacements(iTemp, 2)
             '// Other parameters - check if are as required.
            .Forward = True
             '// Go replace
            .Execute replace:=wdReplaceAll      '    reaches breakpoint 2 here
        End With
    Next
    wrdApp.ActiveDocument.SaveAs Filename:="D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\ProblemsA.docx"
        ' Close this new word document
    wrdApp.ActiveDocument.Close
    wrdApp.Quit                          '// Need to add more code to save, email or whatever you want to do...


End Sub

Display More

peterhw · Apr 17th 2012

Re: Accessing word macros from excel

realised I had not correctly got 'wrdApp' in all correct places.
The code below seems to achieve everything ** except ** make the replacements. WORD opens (visible).
A file is saved i.e. ProblemsA.docx but it appears to be the same as the original - i.e. no replacements have taken place.

Code

Sub FindReplaceSample()         '// To test this code, paste it into an Excel module
Dim my_replacements(2, 2)
Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
Dim wrdDoc As Object '// Word.Document       same
Dim iTemp As Integer
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Open("D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\Problems.docx")
    Set wrdDoc1 = wrdApp.ActiveDocument
    wrdApp.Visible = True
    my_replacements(1, 1) = "council"
    my_replacements(1, 2) = "xxxxxxx"
    
    For iTemp = 1 To 1      '       reaches breakpoint 1 OK
        With wrdDoc.Range.Find
             '// Search value in cell
            .Text = my_replacements(iTemp, 1)
            .Replacement.Text = my_replacements(iTemp, 2)
             '// Other parameters - check if are as required.
            .Forward = True
             '// Go replace
            .Execute Replace:=wdReplaceAll      '    reaches breakpoint 2 here
        End With
    Next
    '   fails with needs object - wrdDoc "Document1"
    wrdApp.ActiveDocument.SaveAs Filename:="D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\ProblemsA.docx"
        ' Close this new word document
    wrdApp.ActiveDocument.Close
    wrdApp.Quit                          '// Need to add more code to save, email or whatever you want to do...


End Sub

Display More

peterhw · Apr 16th 2012

Re: Accessing word macros from excel

if I add wrdApp.Visible = True then a WORD window opens but no replacements appear to have occurred when the failure happens - i.e. "council" is still present

peterhw · Apr 16th 2012

Re: Accessing word macros from excel

Quote from cytop;604652

The file is already open... shows the reason why error handlers should be included in code.

Either:
Close any copies of the file you have open.
Or
Use TaskManager to check for running but hidden copies of Word and kill the processes or restart your machine.

Guess I am a little out of my depth and where to look.

I killed the process using TASKMANAGER then tried to run and failed trying to SAVE, QUIT etc stating there was no object.
Presumably there are some handlers in word for checking if the file or object is closed / open == I just don't seem to be thinking along the right path

Code

Sub FindReplaceSample()         '// To test this code, paste it into an Excel module
Dim my_replacements(2, 2)
Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
Dim wrdDoc As Object '// Word.Document       same
Dim iTemp As Integer
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Open("D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\Problems.docx")
    Set wrdDoc1 = wrdApp.ActiveDocument
    my_replacements(1, 1) = "council"
    my_replacements(1, 2) = "xxxxxxx"
    
    For iTemp = 1 To 1      '       reaches breakpoint 1 OK
        With wrdDoc.Range.Find
             '// Search value in cell
            .Text = my_replacements(iTemp, 1)
            .Replacement.Text = my_replacements(iTemp, 2)
             '// Other parameters - check if are as required.
            .Forward = True
             '// Go replace
            .Execute Replace:=wdReplaceAll      '    reaches breakpoint 2 here
        End With
    Next
    '   fails with needs object - wrdDoc & wrdDoc1 appears as "Document1"
    appWd.ActiveDocument.SaveAs Filename:="D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\ProblemsA.docx"
        ' Close this new word document
    appWd.ActiveDocument.Close
    appWd.Quit                          '// Need to add more code to save, email or whatever you want to do...
End Sub

Display More

peterhw · Apr 16th 2012

Re: Accessing word macros from excel

I seem to be struggling and your help is valued.

I had the basics of your example but my system seems to 'lock' then comes up with FILE is locked for editing, read only etc.

Doesn't seem to close. Needs an object.

Code

Sub FindReplaceSample()         '// To test this code, paste it into an Excel module
Dim my_replacements(2, 2)
Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
Dim wrdDoc As Object '// Word.Document       same
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Open("D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\Problems.docx")
Dim iTemp As Integer
    my_replacements(1, 1) = "council"
    my_replacements(1, 2) = "xxxxxxx"
    For iTemp = 1 To 1
        With wrdDoc.Range.Find
             '// Search value in cell
            .Text = my_replacements(iTemp, 1)
            .Replacement.Text = my_replacements(iTemp, 2)
             '// Other parameters - check if are as required.
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
             '// Go replace
            .Execute Replace:=wdReplaceAll
        End With
    Next
    appWd.ActiveDocument.SaveAs Filename:="D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\ProblemsA.docx"
        ' Close this new word document
    appWd.ActiveDocument.Close
    appWd.Quit
     '// Need to add more code to save, email or whatever you want to do...
End Sub

Display More

peterhw · Apr 15th 2012

Re: Accessing word macros from excel

Just trying to implement something for myself and wondering how I could for example replace

my_replacements(1,1) with my_replacements(1,2) where my_replacements(1,1)= "replacement_1" and my_replacements(1,2) is the new text for this occurrence e.g. name
my_replacements(2,1) with my_replacements(2,2) where my_replacements(2,1)= "replacement_2" and my_replacements(1,2) is the new text for this occurrence e.g. address_line_1
my_replacements(3,1) with my_replacements(3,2) where my_replacements(3,1)= "replacement_3" and my_replacements(1,2) is the new text for this occurrence e.g. city
etc.

peterhw · Apr 15th 2012

Re: Accessing word macros from excel

Many thanks again.

peterhw · Apr 12th 2012

Re: Accessing word macros from excel

Quote from cytop;604066

.............), but I'll have a quick search for relevant threads here...

Many thanks.

peterhw · Apr 11th 2012

I have written several macros in VBA / EXCEL but want to access a WORD (template / master) then replace various fields from my VBA EXCEL application database.
Example replace my_address_line1 (in the word document, with the actual address line from my text database accessed from VBA / Excel

Just not sure if I can do what I want to achieve!

When the WORD document is 'edited' then I wish to save and either Print or Email. I don't want to save macros into the final word document id sending by email (I guess that would be poor practice).

There doesn't seem to be an option to save a file in word which is macro enabled or disabled.

Just trying to get some pointers before I head off in the complete wrong direction!

peterhw · Dec 23rd 2011

Re: Purpose of double unary in SUMPRODUCT formula

My apologies - I really had read the guidelines and thought I was trying to be specific.

I'd never have come up with
[h=2]Purpose of double unary in SUMPRODUCT formula[/h]thanks again

peterhw · Dec 23rd 2011

I tried to use the sumproduct function and struggled (with some ideas from this and other forums).

Eventually I got the following code to provide an answer to how many occurences of Apple in column A AND # 1 in column B

=SUMPRODUCT(--(A1:A4="Apple"),--(B1:B4=1))

could someone please advise the significance of the -- (double - minus) before each of the specified ranges. [I came across this by luck and it worked]