Interacting with another app

Creating a new word document
Set wrd = CreateObject("Word.Application")
wrd.Documents.Add
wrd.Visible = True

Setting the start up path
wrd.Options.DefaultFilePath(wdStartupPath) = "c:\user\"

Quitting word
wrd.Quit

Starting word from another app
Set wrd = CreateObject("Word.Application")

Create and launch another app from Word
Set myobject = CreateObject("Excel.Application")
MsgBox myobject.StartupPath
Set myobject = Nothing

Using DDE Channel to send data to another app, like excel:

Dim lngChannel As Long
lngChannel = DDEInitiate(App:="Excel", Topic:="System")
DDEExecute Channel:=lngChannel, Command:="[OPEN(" &; Chr(34) _
 &; "C:\NameOfExcelFile.xls" &; Chr(34) &; ")]
DDETerminate Channel:=lngChannel
lngChannel = DDEInitiate(App:="Excel", Topic:="NameOfExcelFile.xls")
'Go to row 1 column 1 and key in data
DDEPoke Channel:=lngChannel, Item:="R1C1", Data:="Words you are keying in"
DDETerminate Channel:=lngChannel

Using DDE Channel to get data from another app, like excel:
Dim lngChannel As Long
lngChannel = DDEInitiate(App:="Excel", Topic:="System")
DDEExecute Channel:=lngChannel, Command:="[OPEN(" &; Chr(34) _
 &; "C:\Documents\NameOfExcelFile.xls" &; Chr(34) &; ")]"
DDETerminate Channel:=lngChannel
lngChannel = DDEInitiate(App:="Excel", Topic:="NameOfExcelFile.xls")
'Get information from row 1 column 1
MsgBox DDERequest(Channel:=lngChannel, Item:="R1C1")
DDETerminateAll

Using DDE to create a new worksheet in Excel:
Dim lngChannel As Long

lngChannel = DDEInitiate(App:="Excel", Topic:="System")
DDEExecute Channel:=lngChannel, Command:="[New(1)]"
DDETerminate Channel:=lngChannel

Using DDE to run a macro in excel:
Dim lngChannel As Long

lngChannel = DDEInitiate(App:="Excel", Topic:="System")
DDEExecute Channel:=lngChannel, Command:="[Run(" &; Chr(34) &; _
 "NameofExcelFile.xls!NameOfMacro" &; Chr(34) &; ")]"
DDETerminate Channel:=lngChannel

Comments

Post a Comment

Popular posts from this blog

Find and replace

Find and select text With Selection.Find  .Forward = True  .Wrap = wdFindStop  .Text = "Text you need to find"  .Execute End With Bold the text that you are looking for: With ActiveDocument.Content.Find  .Text = "The text you are looking for"  .Forward = True  .Execute  If .Found = True Then .Parent.Bold = True End With Replace all occurances With Selection.Find  .ClearFormatting  .Text = "Text you are looking for"  .Replacement.ClearFormatting  .Replacement.Text = "Text you are replacing it with"  .Execute Replace:=wdReplaceAll, Forward:=True, _  Wrap:=wdFindContinue End With Look for bold formatting and remove it With ActiveDocument.Content.Find  .ClearFormatting  .Font.Bold = True  With .Replacement  .ClearFormatting  .Font.Bold = False  End With  .Execute FindText:="", ReplaceWith:="", _  Format:=True, Replace:=wdReplaceAll End With
Read more

Menus and toolbars

Adding a new toolbar item for word count to the Standard Menu Note that each predefined command has an ID. In this case, the word count command has an ID of 792.  Dim btnNew As CommandBarButton     CustomizationContext = NormalTemplate     Set btnNew = CommandBars("Standard").Controls.Add _         (Type:=msoControlButton, ID:=792, Before:=6)     With btnNew         .BeginGroup = True         .FaceId = 700         .TooltipText = "Word count"     End With Adding a double underline command to the formatting toolbar The double underline command has a standard ID of 60  CustomizationContext = NormalTemplate     CommandBars("Formatting").Controls.Add _         Type:=msoControlButton, ID:=60, Before:=7
Read more

Formatting

Format selected text (change font, size, etc)  With Selection.Font  .Name = "Times New Roman"  .Size = 14  .AllCaps = True  End With  With Selection.ParagraphFormat  .LeftIndent = InchesToPoints(0.5)  .Space1  End With Apply formatting to a range of paragraphs  Dim rngFormat As Range  Set rngFormat = ActiveDocument.Range( _  Start:=ActiveDocument.Paragraphs(1).Range.Start, _   End:=ActiveDocument.Paragraphs(3).Range.End)  With rngFormat  .Font.Name = "Arial"  .ParagraphFormat.Alignment = wdAlignParagraphJustify  End With For more adjustments: Dim rngFormat As Range  Set rngFormat = ActiveDocument.Range(Start:=0, End:=0)  With rngFormat  .InsertAfter Text:="Title"  .InsertParagraphAfter  With .Font  .Name = "Tahoma"  .Size = 24  .Bold = True  End With  End With  With ActiveDocument.Paragraphs(1)  .Alignment = wdAlignParagraphCenter  .SpaceAfter = InchesToPoints(0.5)  End Wi
Read more