tag:blogger.com,1999:blog-284922622024-03-09T18:46:32.746-08:00VBA Tips & TricksWelcome to VBA Tips & Tricks.
All VBA related information will be posted on this blog. Of late, VBA has been disregarded by many software professionals for .Net, c# and other technologies. This blog will also post articles related to them too
Happy readingUnknownnoreply@blogger.comBlogger350125tag:blogger.com,1999:blog-28492262.post-56810131998138097092014-01-25T05:36:00.003-08:002014-01-25T05:36:54.406-08:00How to Extract Wild Card Matches in Word Document using Word VBA / Export Find Matches to Text File using VBA<h2>
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!"><span style="color: red;">
How to use Word VBA to Find Wild Card Matches in Multiple Word Documents and Extract them </span></om></om></om></om></om></om></om></om></om></om></h2>
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!"><br />There are many situations where a particular format throughout the document that needs to be extracted. The answer would be </om></om></om></om></om></om></om></om></om></om><br />
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!"><br /></om></om></om></om></om></om></om></om></om></om>
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!">1) Wild Card Search</om></om></om></om></om></om></om></om></om></om><br />
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!">2) Regular expressions</om></om></om></om></om></om></om></om></om></om><br />
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!"><br /></om></om></om></om></om></om></om></om></om></om>
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!">Lets consider wild card search for this post for a document that contains Reference Citations within square brackets [...]</om></om></om></om></om></om></om></om></om></om><br />
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!"><br /></om></om></om></om></om></om></om></om></om></om>
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!">The following snippet Loops through all documents in a folder, opens them and searches for content within Square Brackets </om></om></om></om></om></om></om></om></om></om><br />
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!"><br /></om></om></om></om></om></om></om></om></om></om>
It exports the matches to a Text File (Can store in Excel also)<br />
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!"><br /></om></om></om></om></om></om></om></om></om></om>
<om name="Om Ganeshaya Namaha/"><om name="Om Satguru Seshadri Swamigal Thiruvadike"><om name="Om Varahi Namaha"><om name="Om Saravana Bhava"><om name="Om Sai Ram"><om name="Om Namah Shivaya"><om name="Om Shakthi"><om name="Om Namo Narayanana"><om name="Jai Shri Ram"><om name="Hari Om!"><br /></om></om></om></om></om></om></om></om></om></om>
<span style="color: blue;">Sub Extract_WildCard_Matches()</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;">Dim sWildCard As String</span><br />
<span style="color: blue;">Dim sDir</span><br />
<span style="color: blue;">Dim oWD As Word.Document</span><br />
<span style="color: blue;">Dim sPath As String</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;"><br /></span>
<span style="color: blue;">sWildCard = "\[[!\[\]]{1,}\]"</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;">sPath = "C:\Documents\"</span><br />
<span style="color: blue;">sDir = Dir$(sPath & "*.docx", vbNormal)</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;">Do Until LenB(sDir) = 0</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;"> Set oWD = Documents.Open(sPath & sDir)</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;"> Open "C:\Match_Output.txt" For Append As #1</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> Selection.HomeKey wdStory, wdMove</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> Selection.Find.Execute FindText:=sWildCard, MatchWildcards:=True</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> Do While Selection.Find.Found</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> Print #1, ActiveDocument.Name & vbTab & Selection.Range.Text</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> Selection.Range.Collapse wdCollapseEnd</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> Selection.Find.Execute</span><br />
<span style="color: blue;"> Loop</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> Close #1</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;"> oWD.Close False</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;"> sDir = Dir$</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;">Loop</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;"><br /></span>
<span style="color: blue;"><br /></span>
<span style="color: blue;"><br /></span>
<span style="color: blue;">End Sub</span><br />
Unknownnoreply@blogger.com5tag:blogger.com,1999:blog-28492262.post-46507256277131364542013-10-14T09:17:00.001-07:002013-10-14T09:17:17.430-07:00How to Login to a HTTPS Website PopUp using Excel VBA<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjG_AwzQQ6PN31Ino_bryDb64k73Z20gNERCSJ_lzOgSl6M65lZmsuHgTDh1MnQQtstY03n4rqUex99f6vP7Swts3qtg26UfrGZrjDFdTxug8VXUnz0zYTXzm8huwDFrYXZ3zEi/s1600/Website+Login+Popup+VBA+Coding.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="181" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjG_AwzQQ6PN31Ino_bryDb64k73Z20gNERCSJ_lzOgSl6M65lZmsuHgTDh1MnQQtstY03n4rqUex99f6vP7Swts3qtg26UfrGZrjDFdTxug8VXUnz0zYTXzm8huwDFrYXZ3zEi/s320/Website+Login+Popup+VBA+Coding.png" width="320" /></a><om name="Om Ganeshaya Namaha"><span style="color: red; font-size: large;">
<om name="Om Satguru Seshadri Swamigal Thiruvadike">
<om name="Om Varahi Namaha">
<om name="Om Saravana Bhava">
<om name="Om Sai Ram">
<om name="Om Namah Shivaya">
<om name="Om Shakthi">
<om name="Om Namo Narayana">
<om name="Hari Om!">
<om name="Jai Shri Ram">
<b>Control Login Popup from Excel VBA </b></om></om></om></om></om></om></om></om></om></span></om><br />
<br />
The new HTTPS popups cause lot of irritation to the developers who have coded for it before.<br />
<br />
<br />
I have tried to circumvent it using SendKeys. Please have a look at the snippet below and share your views on it<br />
<br />
<br />
<br />
<br />
<span style="color: blue;"> Set ie = CreateObject("InternetExplorer.Application.1")</span><br />
<span style="color: blue;"> ie.Visible = True</span><br />
<span style="color: blue;"> ie.navigate cURL</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> Application.Wait (Now + TimeValue("0:00:10"))</span><br />
<span style="color: blue;"> If ie.readyState = READYSTATE_LOADING Then</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> Set objShellWindows = New ShellWindows</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;"> Application.SendKeys "abcd"</span><br />
<span style="color: blue;"> Application.SendKeys "{TAB}"</span><br />
<span style="color: blue;"> Application.SendKeys "pwd123"</span><br />
<span style="color: blue;"> Application.SendKeys "{TAB}"</span><br />
<span style="color: blue;"> Application.SendKeys "{RETURN}"</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> End If</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> 'Do While ie.Busy: DoEvents: Loop</span><br />
<span style="color: blue;"> Set doc = ie.Document</span>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-28492262.post-25352274781826693412013-05-26T22:46:00.000-07:002013-05-26T22:46:37.558-07:00Convert PowerPoint TextBox Slides as Notes using VBA<om name="Om Ganeshaya Namaha">
<om name="Om Satguru Seshadri Swamigal Thiruvadike">
<om name="Om Varahi Namaha">
<om name="Om Saravana Bhava">
<om name="Om Sai Ram">
<om name="Om Namah Shivaya">
<om name="Om Shakthi">
<om name="Om Namo Narayana">
<om name="Hari Om!">
<om name="Jai Shri Ram!">
<span style="color: red;"><span style="font-size: large;"><b>How to Add Notes to Powerpoint Slides using VBA</b> </span></span></om></om></om></om></om></om></om></om></om></om>
<br />
<br />
<br />
It has been quite some time since I posted in this blog. <a href="http://vbadud.blogspot.in/2010/05/how-to-extract-textbox-contents-from.html#comment-form">Murugan</a> had kindled that in the form of the following snippet. We are had earlier tried <a href="http://vbadud.blogspot.in/2006/07/vba-creating-powerpoint-presentation.html">Creating PowerPoint Presentation</a> through VBA and <a href="http://vbadud.blogspot.in/2012/05/how-to-save-powerpoint-presentation-as.html"><span style="color: #225588;">Save PowerPoint Presentation as PDF using VBA</span></a> <br />
<br />
This snippet converts the TextBoxes / Shapes with Text that are available in PowerPoint Slide to Notes Section. <br />
<br />
<pre class="vb" name="code">Sub Export_TextBoxes_AsNotes()
Dim oPPT As Presentation
Dim oSlide As Slide
Dim oSlideShape As Shape
Dim oNotesShape As Shape
Set oPPT = ActivePresentation
Set oSlide = oPPT.Slides(3)
For Each oSlideShape In oSlide.Shapes
If oSlideShape.HasTextFrame Then
Set oNotesShape = oSlide.NotesPage.Shapes.AddShape(msoShapeRectangle, 54, 442, 432, 324) '
oNotesShape.TextFrame.TextRange.Text = oSlideShape.TextFrame.TextRange.Text
End If
Next
If Not oSlide Is Nothing Then Set oSlide = Nothing
If Not oPPT Is Nothing Then Set oPPT = Nothing
End Sub
</pre>
See also:<br />
<a href="http://vbadud.blogspot.in/2008/07/delete-end-points-from-list-using.html"><span style="color: #225588;">Delete End Points from List using Powerpoint VBA</span></a><br />
<a href="http://vbadud.blogspot.in/2012/05/how-to-save-powerpoint-presentation-as.html"><span style="color: #225588;">How to Save PowerPoint Presentation as PDF using VBA</span></a> <br />
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-29582845015409406392012-05-11T23:44:00.001-07:002012-05-11T23:44:47.910-07:00How to Save PowerPoint Presentation as PDF using VBA<strong>How to Convert PowerPoint Presentation PPT to PDF using VBA</strong><br />
<br />
PDF is always the universal format for sending the files. With lot of versions of MS Office and other Office suites around .. it is better to circulate the Deck as a PDF<br />
<br />
The following snippet converts the Presentation to a PDF and saves in the same folder of the PPT<br />
<br />
<pre class="vb" name="code">ActivePresentation.ExportAsFixedFormat ActivePresentation.Path & "\" & ActivePresentation.Name & ".pdf", ppFixedFormatTypePDF, ppFixedFormatIntentPrint
</pre>
<div class="vb" name="code">
<br /></div>
<div class="vb" name="code">
<a href="http://vbadud.blogspot.in/2009/07/how-to-create-pdf-from-word-document.html">How to Create PDF from Word Document using VBA</a></div>
<div class="vb" name="code">
<a href="http://vbadud.blogspot.in/2012/01/how-to-convert-word-table-to-pdf-using.html">How to Convert Word Table to PDF using VBA</a></div>Unknownnoreply@blogger.com5tag:blogger.com,1999:blog-28492262.post-42028297250974031772012-05-06T07:56:00.000-07:002012-05-06T07:56:06.021-07:00Excel VBA TimeStamp – Milliseconds using Excel VBA<div class="MsoNormal" style="color: red; font-weight: bold;">
<span lang="EN-IN">How to Get Time in Milliseconds using Excel VBA</span></div>
<div class="MsoNormal">
<span lang="EN-IN">The following function uses Timer function to get the milliseconds and append it to the current time</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">Public Function TimeInMS() As String</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> TimeInMS = Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">End Function</span></div>
<div class="MsoNormal">
<span lang="EN-IN">Timer function returns a <b>Single</b> representing the number of seconds elapsed since midnight.</span></div>
<div class="MsoNormal">
<span lang="EN-IN">Another method is to use API Functions as shown below</span></div>
<div class="MsoNormal">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN">Private Type SYSTEMTIME</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"> wYear As Integer</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"> wMonth As Integer</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"> wDayOfWeek As Integer</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"> wDay As Integer</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"> wHour As Integer</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"> wMinute As Integer</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"> wSecond As Integer</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"> wMilliseconds As Integer</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN">End Type</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN">Private Declare Sub GetSystemTime Lib "kernel32" _</span></div>
<div class="MsoNormal" style="color: #999900;">
<span lang="EN-IN"> (lpSystemTime As SYSTEMTIME)</span></div>
<div class="MsoNormal">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">Public Function TimeToMillisecond() As String</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> Dim tSystem As SYSTEMTIME</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> Dim sRet</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> On Error Resume Next</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> GetSystemTime tSystem</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> sRet = Hour(Now) & ":" & Minute(Now) & ":" & Second(Now) & _</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> ":" & tSystem.wMilliseconds</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> TimeToMillisecond = sRet</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">End Function</span></div>
<div class="MsoNormal">
<span lang="EN-IN">Millisecond timer using VBA, How to get milliseconds in VBA Now() function, VBA Now() function, VBA Timer function , Excel VBA Timer, VBA Milliseconds </span></div>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-28492262.post-32872554288015991162012-05-06T07:55:00.000-07:002012-05-06T07:55:49.018-07:00Excel VBA uninstall Excel Addins<div class="MsoNormal" style="color: red; font-weight: bold;">
<span lang="EN-IN">Programmatically uninstall Excel Addins using VBA</span></div>
<span lang="EN-IN"><o:p> </o:p></span><br />
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN">Sub UnInstall_Addins_From_EXcel_AddinsList()</span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN">Dim oXLAddin As AddIn</span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN">For Each oXLAddin In Application.AddIns</span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"> Debug.Print oXLAddin.FullName</span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"> If oXLAddin.Installed = True Then</span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"> oXLAddin.Installed = False</span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"> End If</span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN">Next oXLAddin</span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #000099;">
<span lang="EN-IN">End Sub</span></div>
<div class="MsoNormal">
<span lang="EN-IN"><br /></span></div>Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-28492262.post-45746614184567996412012-05-06T07:54:00.001-07:002012-05-06T07:54:37.228-07:00Embed Existing Word File to Spreadsheet using Excel VBA<h1 style="color: #ff6600;">
<span style="font-size: medium;"> <span lang="EN-IN">Insert Existing File (Word Document) to Spreadsheet using VBA</span></span></h1>
<br />
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">Sub Insert_File_To_sheet()<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">Dim oWS As Worksheet ' Worksheet Object<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">Dim oOLEWd As OLEObject ' OLE Word Object<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">Dim oWD As Document ' Word Document Object (Use Microsoft Word Reference)<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">Set oWS = ActiveSheet<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">' embed Word Document<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">Set oOLEWd = oWS.OLEObjects.Add(Filename:="C:\VBADUD\Chapter 1.doc")<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">oOLEWd.Name = "EmbeddedWordDoc"<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">oOLEWd.Width = 400<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">oOLEWd.Height = 400<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">oOLEWd.Top = 30<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">' Assign the OLE Object to Word Object<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">Set oWD = oOLEWd.Object<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">oWD.Paragraphs.Add<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">oWD.Paragraphs(oWD.Paragraphs.Count).Range.InsertAfter "This is a sample embedded word document"<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">oOLEWd.Activate<o:p></o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff; line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="font-family: Verdana; font-size: 8.5pt;">End Sub<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="color: black; font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="color: black; font-family: Verdana; font-size: 8.5pt;">If you want to embed other document like PDF etc, you can do the same by<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="color: black; font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="color: black; font-family: Verdana; font-size: 8.5pt;"> ActiveSheet.OLEObjects.Add Filename:= "C:\VBADUD\Sample_CH03.pdf", Link:=False, DisplayAsIcon:= False<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="color: black; font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 1.5pt 0in;">
<b><span lang="EN-IN" style="color: black; font-family: Verdana; font-size: 8.5pt;">Display embedded document as Icon<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="color: black; font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="color: black; font-family: Verdana; font-size: 8.5pt;">If you want to display the embedded document as an Icon set DisplayAsIcon property to True<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 1.5pt 0in;">
<span lang="EN-IN" style="color: black; font-family: Verdana; font-size: 8.5pt;"><o:p> </o:p></span></div>Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-28492262.post-41498420560430545392012-05-06T07:54:00.000-07:002012-05-06T07:54:09.190-07:00Retrieve / Get First Row of Excel AutoFilter using VBA<div>
<span style="color: red; font-family: Calibri;"><strong>Extract First Row of the Filtered Range using Excel VBA </strong></span></div>
<br />
<div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;"></span> </div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">We can create filters programmatically using Excel VBA () and also add multiple criteria to it (). Once we get the filtered data, either we extract the same or iterate each row in it and do some operations. Here is one such simple program to extract the rows of filtered range using VBA</span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">Sub</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> Get_Filtered_Range()<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">Dim</span> oWS <span style="color: blue;">As</span> Worksheet<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">Dim</span> oRng <span style="color: blue;">As</span> Range<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">Dim</span> oColRng <span style="color: blue;">As</span> Range<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">Dim</span> oInRng <span style="color: blue;">As</span> Range<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">On</span> <span style="color: blue;">Error</span> <span style="color: blue;">GoTo</span> Err_Filter<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>oWS = ActiveSheet<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>oWS.UsedRange.AutoFilter(Field:=2, Criteria1:=<span style="color: #a31515;">"Banana"</span>)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>oRng = oWS.Cells.SpecialCells(xlCellTypeVisible)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>oColRng = oWS.Range(<span style="color: #a31515;">"A2:A5000"</span>)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>oInRng = Intersect(oRng, oColRng)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>MsgBox(<span style="color: #a31515;">"Filtered Range is "</span> & oInRng.Address)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>MsgBox(<span style="color: #a31515;">"First Row Filtered Range is "</span> & oInRng.Rows(1).Row)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">Finally</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">:<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">If</span> <span style="color: blue;">Not</span> oWS <span style="color: blue;">Is</span> <span style="color: blue;">Nothing</span> <span style="color: blue;">Then</span> oWS = <span style="color: blue;">Nothing<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">Err_Filter:<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">If</span> Err <> 0 <span style="color: blue;">Then<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>MsgBox(Err.Description)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>Err.Clear()<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">GoTo</span> <span style="color: blue;">Finally<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">End</span> <span style="color: blue;">If<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">End</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">Sub<o:p></o:p></span></span></div>
</div>
<br />
<div>
</div>
<br />
<div>
</div>
<br />
<div>
</div>
<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6OpTwjBo4mDuBBi7fgD99N9gkcINu6suVWP9ZKr6KXTaozys_8sHE7NmJRW3jU2l7bQoIy7Jxm6180o48JGZs5OJxo4saOFjSKzUSoZu7FTSKtLN7K61w39Lsgnd8mAVWGZa9/s1600-h/DND_Filter_Original.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5233616172362485458" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6OpTwjBo4mDuBBi7fgD99N9gkcINu6suVWP9ZKr6KXTaozys_8sHE7NmJRW3jU2l7bQoIy7Jxm6180o48JGZs5OJxo4saOFjSKzUSoZu7FTSKtLN7K61w39Lsgnd8mAVWGZa9/s400/DND_Filter_Original.JPG" /></a><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1oxsKdx5AghmUl6bvIjQkzP9itK9Hm2T72p3s_GjiEto0pO48uuVzYh9c8im9PSTDJua-q81uomfToCjG3xoee5kA0Agzvn_wINltNU4jBSGxnyuTXM0-r34kpwdAeOo2GOMZ/s1600-h/DND_Filter_GetFirstRow.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5233616175907700514" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1oxsKdx5AghmUl6bvIjQkzP9itK9Hm2T72p3s_GjiEto0pO48uuVzYh9c8im9PSTDJua-q81uomfToCjG3xoee5kA0Agzvn_wINltNU4jBSGxnyuTXM0-r34kpwdAeOo2GOMZ/s400/DND_Filter_GetFirstRow.JPG" /></a><br />
<br />
<div>
</div>
</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-63140072924369743702012-05-06T07:53:00.000-07:002012-05-06T07:53:44.471-07:00UnInstall Word Addins using VBA<div class="MsoNormal">
<b><span lang="EN-IN"><br /><o:p></o:p></span></b></div>
<div class="MsoNormal">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal">
<span lang="EN-IN">Here is a simple method to uninstall a Word Addin (.dot file) using Word VBA</span></div>
<div class="MsoNormal">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">Private Sub UnInstalled_AllWordAddins()</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> Dim oAddin As AddIn</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> On Error GoTo Err_Addin</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> For Each oAddin In AddIns</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> If oAddin.Installed Then</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> msg = oAddin.Name</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> oAddin.Installed = False</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> End If</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> Next oAddin</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">Finally:</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> If Not oAddin Is Nothing Then Set oAddin = Nothing</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">Err_Addin:</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">If Err < > 0 Then</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> Err.Clear</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> GoTo Finally</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">End If</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">End Sub</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifkkGwNVfjqP3pHOwO1PafyJrhSgEw-8I0k_NcFqjGQfwBVrIo_nlTwLG6YJlwkOAIgT9vWyzgNf9MNmqwIKmH6LsW156KKq4MmFTjvLSKE8NrwzIrFPP7norpDHW8fcVUGwIY/s1600-h/dnd_wordaddin_afterprogram.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5256081561665541954" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifkkGwNVfjqP3pHOwO1PafyJrhSgEw-8I0k_NcFqjGQfwBVrIo_nlTwLG6YJlwkOAIgT9vWyzgNf9MNmqwIKmH6LsW156KKq4MmFTjvLSKE8NrwzIrFPP7norpDHW8fcVUGwIY/s400/dnd_wordaddin_afterprogram.JPG" style="cursor: pointer;" />Installed Word Addin</a><br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfT8CpC1fBdVqQFEsRJvYKMuVo-xXaupwlnflf9XSwP2wAdsYEMvOxatHZVzsirtQaFKL2sIOZpWICBqOmjIjwT6HhGFtSqeEW2epvd8HJVXtuL65g7cOVrOqdTSEUGft28bKs/s1600-h/dnd_wordaddin_uninstalled.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5256081567188433938" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfT8CpC1fBdVqQFEsRJvYKMuVo-xXaupwlnflf9XSwP2wAdsYEMvOxatHZVzsirtQaFKL2sIOZpWICBqOmjIjwT6HhGFtSqeEW2epvd8HJVXtuL65g7cOVrOqdTSEUGft28bKs/s400/dnd_wordaddin_uninstalled.JPG" style="cursor: pointer;" />Word Addin List after Macro Execution. Addin is uninstalled (not removed)</a><br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-10501598200401624552012-05-06T07:52:00.000-07:002012-05-06T07:52:47.896-07:00Word VBA add command buttons through code<div class="MsoNormal" style="color: red; font-weight: bold;">
<span lang="EN-IN">Add CommandButton to Word Document using VBA (through AddOLEControl)</span></div>
<div class="MsoNormal">
<span lang="EN-IN">Here is one of the ways to add a command button on a Word document using Word VBA</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">Sub Macro_Add_Button()</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> Dim oCtl</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> Dim oCmd</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> </span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> Set oCtl = ActiveDocument.InlineShapes.AddOLEControl(ClassType:="Forms.CommandButton.1")</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> Set oCmd = oCtl.OLEFormat.Object</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"> oCmd.Caption = "Click Me..."</span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN"><o:p> </o:p></span></div>
<div class="MsoNormal" style="color: #3333ff;">
<span lang="EN-IN">End Sub</span></div>
<div class="MsoNormal">
<span lang="EN-IN"><o:p> </o:p></span></div>
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiHGCO9ExRzKSEJVfjv9G36XIxq88mUx_4UINNxvW7ZzzTzo1FNbsJuOGDQ5tf2kIaah-M4jSROtmTLBw4UPzh6UmuCoJNbD6GyhDyBsg89vGtDr_NRLjOwSBpabqhyfnkda0s/s1600-h/dnd_Word_CommandButton_Add.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5256083535429099746" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiHGCO9ExRzKSEJVfjv9G36XIxq88mUx_4UINNxvW7ZzzTzo1FNbsJuOGDQ5tf2kIaah-M4jSROtmTLBw4UPzh6UmuCoJNbD6GyhDyBsg89vGtDr_NRLjOwSBpabqhyfnkda0s/s400/dnd_Word_CommandButton_Add.JPG" style="cursor: pointer;" /></a><br />
<br />Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-28492262.post-35914400172594300482012-05-06T07:50:00.000-07:002012-05-06T07:50:57.206-07:00Update Word Document with Excel Information using VBA<div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Excel Range to Word Template using VBA</span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Most often we maintain list of contacts in Excel workbook and it needs to be transferred to Word document (made from some template). Here is a simple snippet that can help:</span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">The code is used to copy the content from Excel range shown below to a Word document:</span></div>
<br />
<table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; margin: auto auto auto 4.65pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184; width: 432px;">
<><></><tbody>
<><></>
<tr style="height: 15pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<><></><td style="background: rgb(247, 150, 70); border-color: rgb(250, 192, 144) rgb(240, 240, 240) rgb(250, 192, 144) rgb(250, 192, 144); border-style: solid none solid solid; border-width: 1pt medium 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-left-alt: solid #FAC090 .5pt; mso-border-top-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 89pt;" valign="bottom" width="119"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: white; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">Name<o:p></o:p></span></span></b></div>
</td>
<><></><td style="background: rgb(247, 150, 70); border-color: rgb(250, 192, 144) rgb(240, 240, 240); border-style: solid none; border-width: 1pt medium; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-top-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 65pt;" valign="bottom" width="87"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: white; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">ContactNo<o:p></o:p></span></span></b></div>
</td>
<><></><td style="background: rgb(247, 150, 70); border-color: rgb(250, 192, 144) rgb(240, 240, 240); border-style: solid none; border-width: 1pt medium; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-top-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 54pt;" valign="bottom" width="72"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: white; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">Address<o:p></o:p></span></span></b></div>
</td>
<><></><td style="background: rgb(247, 150, 70); border-color: rgb(250, 192, 144) rgb(250, 192, 144) rgb(250, 192, 144) rgb(240, 240, 240); border-style: solid solid solid none; border-width: 1pt 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-right-alt: solid #FAC090 .5pt; mso-border-top-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 116pt;" valign="bottom" width="155"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: white; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">Email<o:p></o:p></span></span></b></div>
</td></tr>
<><></>
<tr style="height: 15pt; mso-yfti-irow: 1;">
<><></><td style="background: rgb(253, 233, 217); border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(250, 192, 144) rgb(250, 192, 144); border-style: none none solid solid; border-width: medium medium 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-left-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 89pt;" valign="bottom" width="119"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: black; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">Christina<o:p></o:p></span></span></div>
</td>
<><></><td style="background: rgb(253, 233, 217); border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(250, 192, 144); border-style: none none solid; border-width: medium medium 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 65pt;" valign="bottom" width="87"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: black; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">516 418 1234<o:p></o:p></span></span></div>
</td>
<><></><td style="background: rgb(253, 233, 217); border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(250, 192, 144); border-style: none none solid; border-width: medium medium 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 54pt;" valign="bottom" width="72"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: black; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">Cincinatti<o:p></o:p></span></span></div>
</td>
<><></><td style="background: rgb(253, 233, 217); border-color: rgb(240, 240, 240) rgb(250, 192, 144) rgb(250, 192, 144) rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-right-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 116pt;" valign="bottom" width="155"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<u><span style="color: blue; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><a href="mailto:Christina@vbadud.com"><span style="color: blue;"><span style="font-family: Calibri;">Christina@vbadud.com</span></span></a><o:p></o:p></span></u></div>
</td></tr>
<><></>
<tr style="height: 15pt; mso-yfti-irow: 2;">
<><></><td style="background-color: transparent; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(250, 192, 144) rgb(250, 192, 144); border-style: none none solid solid; border-width: medium medium 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-left-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 89pt;" valign="bottom" width="119"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: black; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">Girish Kutty<o:p></o:p></span></span></div>
</td>
<><></><td style="background-color: transparent; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(250, 192, 144); border-style: none none solid; border-width: medium medium 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 65pt;" valign="bottom" width="87"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: black; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">516 418 6752<o:p></o:p></span></span></div>
</td>
<><></><td style="background-color: transparent; border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(250, 192, 144); border-style: none none solid; border-width: medium medium 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 54pt;" valign="bottom" width="72"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: black; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">Cincinatti<o:p></o:p></span></span></div>
</td>
<><></><td style="background-color: transparent; border-color: rgb(240, 240, 240) rgb(250, 192, 144) rgb(250, 192, 144) rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-right-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 116pt;" valign="bottom" width="155"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<u><span style="color: blue; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><a href="mailto:gkutty@vbadud.com"><span style="color: blue;"><span style="font-family: Calibri;">gkutty@vbadud.com</span></span></a><o:p></o:p></span></u></div>
</td></tr>
<><></>
<tr style="height: 15pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes;">
<><></><td style="background: rgb(253, 233, 217); border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(250, 192, 144) rgb(250, 192, 144); border-style: none none solid solid; border-width: medium medium 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-left-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 89pt;" valign="bottom" width="119"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: black; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">Ravichand Koneru<o:p></o:p></span></span></div>
</td>
<><></><td style="background: rgb(253, 233, 217); border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(250, 192, 144); border-style: none none solid; border-width: medium medium 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 65pt;" valign="bottom" width="87"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: black; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">777 213 213<o:p></o:p></span></span></div>
</td>
<><></><td style="background: rgb(253, 233, 217); border-color: rgb(240, 240, 240) rgb(240, 240, 240) rgb(250, 192, 144); border-style: none none solid; border-width: medium medium 1pt; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 54pt;" valign="bottom" width="72"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="color: black; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><span style="font-family: Calibri;">Boston<o:p></o:p></span></span></div>
</td>
<><></><td style="background: rgb(253, 233, 217); border-color: rgb(240, 240, 240) rgb(250, 192, 144) rgb(250, 192, 144) rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid #FAC090 .5pt; mso-border-right-alt: solid #FAC090 .5pt; padding: 0cm 5.4pt; width: 116pt;" valign="bottom" width="155"><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<u><span style="color: blue; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN;"><a href="mailto:rkoneru@vbadud.com"><span style="color: blue;"><span style="font-family: Calibri;">rkoneru@vbadud.com</span></span></a><o:p></o:p></span></u></div>
</td></tr>
</tbody></table>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;">Sub CopY_Data_To_Word()<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;">Dim oWA As Word.Application<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;">Dim oWD As Word.Document<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;">Set oWA = New Word.Application<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;">Set oWD = oWA.Documents.Add("C:\Users\comp\Documents\Doc2.dot") ' Replace with your template here<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;">For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;"><span style="mso-spacerun: yes;"> </span>oWD.Bookmarks("Name").Range.Text = Cells(i1, 1)<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;"><span style="mso-spacerun: yes;"> </span>oWD.Bookmarks("ContactNo").Range.Text = Cells(i1, 2)<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;"><span style="mso-spacerun: yes;"> </span>oWD.Bookmarks("Address").Range.Text = Cells(i1, 3)<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;"><span style="mso-spacerun: yes;"> </span>oWD.Bookmarks("Email").Range.Text = Cells(i1, 4)<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;"><span style="mso-spacerun: yes;"> </span>'Code for saving the document<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;">Next i1<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;">' Releasing objects etc<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: #0070c0;"><span style="font-family: Calibri;">End Sub<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Bookmarks are added to the Word template and whenever a new document is created from the template, the document has those bookmarks.</span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">The code above places the information from the Excel sheet to the specific Bookmark ranges</span></div>
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 115%; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US;"><br clear="all" style="mso-special-character: line-break; page-break-before: always;" /></span><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZu9O9vgwUc4Mic8Pi_0RU2S5uI-vM6UkUR9yDOnEAyDpyJuePstmERRR-A6FTHCkAx0ZSab18NouCDPRKK8MrN_KxmvRD4A3eXdqr9_hHJCVBpTufcdA6CFbZxw3kv9QiTAL9/s1600-h/dnd_Excel_Word.JPG"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5372609354616366674" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZu9O9vgwUc4Mic8Pi_0RU2S5uI-vM6UkUR9yDOnEAyDpyJuePstmERRR-A6FTHCkAx0ZSab18NouCDPRKK8MrN_KxmvRD4A3eXdqr9_hHJCVBpTufcdA6CFbZxw3kv9QiTAL9/s400/dnd_Excel_Word.JPG" style="height: 291px; width: 400px;" /></a>Excel to Word using VBA </div>
<br />
<div>
</div>
<br />
<div>
<br /></div>
<br />
<div>
</div>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-28492262.post-87260636077077543312012-05-02T18:15:00.001-07:002012-05-02T18:15:45.465-07:00How to Make a File ReadOnly using Excel VBA<strong><span style="color: red;">How to Create ReadOnly Files using VBA - Excel VBA ReadOnly Function</span></strong><br />
<br />
There are many occassions where you want to save the file as Readonly (at times with a Password protection) after you complete the process. We have talked about <a href="http://vbadud.blogspot.in/2007/05/changing-file-attributes.html">SetAttr</a> that changes the file attributes. Now let us see how to do this using FileSystemObject<br />
<br />
Please refer <a href="http://vbadud.blogspot.in/2010/05/how-to-iterate-through-all.html">How to iterate through all Subdirectories till the last directory in VBA</a> to know how to include the references if you are using Early binding. <br />
<br />
The following snippet uses late binding and shows how to set the file as read-only<br />
<br />
<br />
<br />
<pre class="vb" name="code">Function MakeFileReadOnly(ByVal sFile As String)
Dim strSaveFilename As String
Dim oFSO As Object 'Scripting.FileSystemObject
Dim oFile As Object 'Scripting.File
' Create Objects
' Uses Late Binding
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.GetFile(FilePath:=sFile)
' Set file to be read-only
oFile.Attributes = 1
' Releasing Objects
If Not oFSO Is Nothing Then Set oFSO = Nothing
If Not oFile Is Nothing Then Set oFile = Nothing
End Function
</pre>
The function is not restricted to Excel files alone and can be used for any kind of files
<br />
<br />
Once You are done you can <a href="http://vbadud.blogspot.in/2007/05/check-workbook-attributes.html">Check Workbook Attributes</a> to confirm if the Workbook is ReadOnlyUnknownnoreply@blogger.com1tag:blogger.com,1999:blog-28492262.post-52643807833688792232012-02-29T18:15:00.000-08:002012-02-29T18:15:53.267-08:00How to Create Hyperlinks in multiple cells using EXcel VBAHow to Link Cells to Files/Folders using Excel VBA<br />
<br />
There are many cases where we want to have a Hyperlink on a cell that opens a document / image etc.<br />
In the following snippet we can see how that works<br />
<br />
The sheet is the Master Sheet, which contains the list of Products that are compared. The comparison reports for these products are placed in separate files in the same folder.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZUsMWi2UajZUcxMcIoG1kE8mjH8-g8jlN0qsGLobbxfzlV0XI4wUft9-2O4xlyJg3fDLXYaqKqdSxqK0K2ngtQOdje_eymh0jqfNJyHoDj1-aCLNshmYGvmguO95Okkz2WTUX/s1600/Create+Hyperlinks+for+Multiple+Cells.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZUsMWi2UajZUcxMcIoG1kE8mjH8-g8jlN0qsGLobbxfzlV0XI4wUft9-2O4xlyJg3fDLXYaqKqdSxqK0K2ngtQOdje_eymh0jqfNJyHoDj1-aCLNshmYGvmguO95Okkz2WTUX/s640/Create+Hyperlinks+for+Multiple+Cells.png" width="640" /></a></div><br />
<br />
The hyperlink uses Relative path - you can hardcode this to any particular folder<br />
<br />
<pre class="vb" name="code">Sub Create_HyperLinks()
Dim i1 As Integer
Dim sA, sB As String
For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
If LenB(Trim$(Cells(i1, 3).Value)) <> 0 Then
sA = Trim$(Cells(i1, 1).Value)
sB = Trim$(Cells(i1, 2).Value)
sA = "Compared_" & sA & "_" & sB & ".xls"
Sheets(1).Range("C" & i1).Hyperlinks.Add Cells(i1, 3), "CompareReports\" & sA
End If
Next i1
End Sub
</pre><div class="vb" name="code"><br />
</div><div class="vb" name="code">See also: <a href="http://vbadud.blogspot.in/2007/12/convert-urls-to-hyperlinks-using-vba.html">Convert URLs to Hyperlinks using VBA</a></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-88482770142893045792012-02-29T17:57:00.000-08:002012-02-29T17:57:18.181-08:00How to Split Text in a Cell to Multiple Cells using Excel VBA<strong><span style="color: red; font-size: large;">Convert a Text to Range using Excel VBA</span></strong><br />
<br />
The following snippet converts the Text to an Array by splitting using SemiColon delimiter and uses the Transpose Function to place it in the Range<br />
<br />
<br />
<pre class="vb" name="code">Sub ConvertText2Range()
Dim sText As String, arText
sText = Range("c16").Value
arText = Split(sText, ";")
Range("D16:D" & CStr(16 + UBound(arText))).Value = WorksheetFunction.Transpose(arText)
End Sub
</pre><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-LF0CFEc7daatBr7nvgz1U1nZSNgCMB5I0FCD1PpY5UBGQXxjYZ6bM3cEpiKLiWQRwpcSAT4TsxEbKhCSj5PRFIAbi7rECX8t-r2fgr8EyPTw-fHyr3K-0AMF4u1SQFHC028F/s1600/Splitting+Text+Across+Multiple+Ranges.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-LF0CFEc7daatBr7nvgz1U1nZSNgCMB5I0FCD1PpY5UBGQXxjYZ6bM3cEpiKLiWQRwpcSAT4TsxEbKhCSj5PRFIAbi7rECX8t-r2fgr8EyPTw-fHyr3K-0AMF4u1SQFHC028F/s640/Splitting+Text+Across+Multiple+Ranges.png" width="640" /></a></div>Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-28492262.post-47799643294217715092012-02-03T09:26:00.000-08:002012-02-04T03:20:40.876-08:00How to convert Excel Text to Comments using VBA<span style="color: red; font-size: large;">Convert Excel Range to Comments using VBA</span><br />
<br />
We have seen how to <a href="-http://vbadud.blogspot.in/2008/09/convert-excel-comments-to-text-using.html">Copy Comments in an Excel Sheet to a Range</a>; now let us see how to do the opposite<br />
<br />
Our reference Excel has Text that needs to be converted as Comments on Column E, which needs to be placed as comments <br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGwyBEC8pbKsiGL9tF-r3Ut2La6do7vkztjdKnC_MxfSJZRsnhRxnQH1Ss1lq8cBRrkZMXubT6bQ5hBfB3hhPSw0wzmDaTtwUn4CeDy7G6h94_K-F13dfghZyFfGqaDT68Jp-S/s1600/Excel+Add+Comments+2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGwyBEC8pbKsiGL9tF-r3Ut2La6do7vkztjdKnC_MxfSJZRsnhRxnQH1Ss1lq8cBRrkZMXubT6bQ5hBfB3hhPSw0wzmDaTtwUn4CeDy7G6h94_K-F13dfghZyFfGqaDT68Jp-S/s640/Excel+Add+Comments+2.png" width="640" /></a></div><br />
<pre class="vb" name="code">Sub Convert_Text_To_Comments()
Dim sText As String ' Comment String
Dim i1 As Long ' Counter
Dim sUser As String ' User Name
sUser = Application.UserName
For i1 = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
sText = ActiveSheet.Cells(i1, 5).Value
'Deletes Existing Comments
Cells(i1, 3).ClearComments
' Creates Comment
Cells(i1, 3).AddComment
Cells(i1, 3).Comment.Text Text:=sUser & Chr(10) & sText
Next i1
End Sub
</pre><br />
<br />
If you already have comments and try to AddComment then Runtime Error 1004 will be thrown. That is why it is better to Remove the existing comments (ClearComments) and proceed with Adding new comment<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDG4twZJTh0LceMWNy819CPrLkOSPr8vwQOjJ0oqJ9qtt54nE8uhTApfKTDKbO1TyqhQkRFNZ3xtf7HoLb1a_vLuT0xpZpzOEoljn3KBkHeOtAB6ItdZbOGSdWzrQJzwslY30C/s1600/Excel+Add+Comments.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="188" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDG4twZJTh0LceMWNy819CPrLkOSPr8vwQOjJ0oqJ9qtt54nE8uhTApfKTDKbO1TyqhQkRFNZ3xtf7HoLb1a_vLuT0xpZpzOEoljn3KBkHeOtAB6ItdZbOGSdWzrQJzwslY30C/s320/Excel+Add+Comments.png" width="320" /></a></div>Unknownnoreply@blogger.com5tag:blogger.com,1999:blog-28492262.post-82613031151556480002012-01-30T19:11:00.000-08:002012-01-30T19:11:31.310-08:00How to Extract Comment information from Word VBA<strong><span style="color: red;">How to Extract Comments Text and Related Information from Word Document using VBA</span></strong><br />
<br />
Here is a hint of accessing the comments and related information using VBA<br />
<br />
<pre class="vb" name="code">Sub Get_Comment_Information()
Dim oComment As Comment
Dim oCommentRange As Range
For i1 = 1 To ActiveDocument.Comments.Count
Set oComment = ActiveDocument.Comments(i1)
Set oCommentRange = oComment.Scope.Paragraphs(1).Range
Debug.Print "Page : " & oCommentRange.Information(wdActiveEndPageNumber) & vbTab _
& "Line : " & oCommentRange.Information(wdFirstCharacterLineNumber) & vbTab
Next i1
End Sub
</pre>Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-28492262.post-37242963483025202792012-01-30T19:09:00.000-08:002012-01-30T19:09:10.115-08:00How to Identify and Tag Numbered Lists using VBA<strong><span style="color: red;">How to Identify and Tag Bullet Lists using VBA</span></strong><br />
<br />
Following snippet identifies a Bulleted List and Tags all Bullet List items and the Bulletted List as a whole<br />
<br />
<pre class="vb" name="code">Sub Tag_Lists()
Dim oBL As ListFormat
Dim oList As List
Dim oLI
For Each oList In ActiveDocument.Lists
If oList.Range.ListFormat.ListType = WdListType.wdListBullet Then
For Each oLI In oList.ListParagraphs
oLI.Range.InsertBefore "
<li>" oLI.Range.InsertAfter "</li>
"
Next oLI
oList.Range.InsertBefore "<ol>" oList.Range.InsertAfter "</ol>"
End If
Next oList
End Sub
</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-31278966720317081602012-01-30T19:03:00.000-08:002012-01-30T19:03:16.733-08:00Compare Word Documents with Headers and Footers using VBA<strong><span style="color: orange;">How to Compare Word Documents Programatically using Word VBA</span></strong><br />
<br />
Word Documents are everywhere .. proposals, tenders, notes, technical papers. In many cases there are more than one authors and more than five reviewers. There is a devil in everyone which comes out when reviewing the document. Suggest some changes.. boldface some text, markup some paragraph and screw the document. If you are the author it is your responsibility to ensure that the changes get reflected. There are many document management solutions that are available for parallel working. <br />
<br />
Just in case you get a document reviewed by your boss (and without track changes) and you want to know what he/she has done use the following<br />
<br />
<pre class="vb" name="code">Sub CompareDoc()
Dim oDoc1 As Document
Dim oDoc2 As Document
Set oDoc1 = Documents.Open("D:\Changed Header.doc")
Set oDoc2 = Documents.Open("D:\Original Header.doc")
Application.CompareDocuments oDoc1, oDoc2, wdCompareDestinationNew, , , , , , True, True
End Sub
</pre><br />
This compares two documents and creates a new document with Track Changes showing the changes.<br />
<br />
<br />
There are lot of parameters to CompareDocuments method. The notable being CompareFormatting, CompareHeaders, CompareFootnotes. The last two ones are used if you want to know the changes made in Headers and Footers. Who knows you would have kept the same header from the document you cloned and your boss would have noticed and changed it. Do you want to take risk of ignoring that<br />
<br />
See also<br />
<a href="http://vbadud.blogspot.in/2007/12/comparing-two-word-documents-using-word.html">Comparing two Word Documents using Word VBA</a><br />
<a href="http://vbadud.blogspot.in/2007/05/compare-files-by-date.html">Compare Files by Date</a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-84369241199309871412012-01-30T18:50:00.000-08:002012-01-30T18:50:22.885-08:00How to Convert Word Table to PDF using VBA<span style="color: orange;"><strong>Export Word Table as PDF using VBA</strong></span><br />
<br />
Anyone who is using Word for quite sometime will agree that Tables and Images are bit scary when it comes to viewing across versions or machines. A Table which looks great in your machine might not look so if he uses a different version of Word. <br />
<br />
In that case it is better to have the Table converted as PDF in your machine and circulate the same. In last post we saw <a href="http://vbadud.blogspot.in/2012/01/how-to-export-parts-of-document-using.html">how to export part of text to a new document</a> using ExportFragment method. Here we export a Table as PDF using ExportAsFixedFormat method. <br />
<br />
The following snippet does exactly the same:<br />
<br />
<pre class="vb" name="code">Sub Table2PDF()
Dim oTab As Word.Table
Dim oRange As Word.Range
Set oTab = ActiveDocument.Tables(1)
oTab.Range.ExportAsFixedFormat "D:\Documents and Settings\Admin\My Documents\Tab_PDF.pdf", wdExportFormatPDF
End Sub
</pre><br />
See also:<br />
<a href="http://vbadud.blogspot.in/2009/07/how-to-create-pdf-from-word-document.html">Convert Word to PDF using VBA</a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-75443371689318915042012-01-30T18:43:00.000-08:002012-01-30T18:43:06.253-08:00How to Export Parts of Document using Word VBA<strong><span style="color: red;">Copy Content with Formatting to New Document using Word VBA</span></strong><br />
<br />
Not all the tens and hundreds of pages in a Word document interests you or matters to you. There are some documents, which we use for reference. All we need is a paragraph/section from the document. If it is a book we used to take a photo-copy of the same and keep it in a folder. How to do the same in a Word document - and in an automated way with all the formatting intact?<br />
<br />
ExportFragment method in Word VBA provides the solution. It creates a new document from the existing one for the Range of your choice.<br />
<br />
Here is an example where it exports eleventh paragraph of the document to a new one.<br />
<br />
<pre class="vb" name="code">Sub PartofText()
Dim oWDRange As Word.Range
Set oWDRange = ActiveDocument.Paragraphs(11).Range
oWDRange.ExportFragment "D:\Documents and Settings\Admin\My Documents\Reference_11.docx", wdFormatDocumentDefault
End Sub
</pre><div class="vb" name="code"><br />
</div><div class="vb" name="code">See also</div><div class="vb" name="code"><a href="http://vbadud.blogspot.in/2009/09/how-to-export-word-range-as-rtf-using.html">How to export Word Range as RTF using VBA</a></div>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-28492262.post-49316289259239601642012-01-30T18:34:00.000-08:002012-01-30T18:34:32.391-08:00How to Format Part of Content Controls in Word VBAWord VBA - Format Some portion of Rich Text Content Control Programatically<br />
<br />
ContentControls have become ubiquitous with Word documents nowadays. Rich Text Content Control is used by many developers and authors to represent useful information. <br />
<br />
At times there is a necessity to highlight / format some part of the Text in that control. You can either <a href="http://vbadud.blogspot.in/2012/01/how-to-search-and-highlighttag-string.html">search for the text and highlight it</a> or Highlight them based on position<br />
<br />
The following example shows how to boldface certain portion of ContentControl<br />
<br />
<pre class="vb" name="code">Sub FormatContentControl()
Dim oCC As ContentControl
Dim oCCRange As Range
Dim oCCRngFormat As Range
Dim oChr As Range
Set oCC = ActiveDocument.ContentControls(1)
oCC.Type = wdContentControlRichText
Set oCCRange = oCC.Range
Set oCCRngFormat = oCCRange.Duplicate
oCC.LockContentControl = False
oCC.LockContents = False
oCCRngFormat.Start = 20
oCCRngFormat.End = oCCRange.End
For Each oChr In oCCRngFormat.Characters
oChr.Font.Bold = True
Next oChr
oCCRngFormat.Font.Bold = -1
oCCRngFormat.Font.Underline = WdUnderline.wdUnderlineSingle
oCCRngFormat.Start = oCCRange.End
oCCRngFormat.Font.Bold = 0
oCCRngFormat.Font.Underline = WdUnderline.wdUnderlineNone
End Sub
</pre><br />
See also<br />
<a href="http://vbadud.blogspot.in/2010/07/how-to-retrieve-value-from-content.html">How to retrieve value from Content Controls using Word VBA</a><br />
<a href="http://vbadud.blogspot.in/2010/07/how-to-add-content-controls-using-vba.html">How to add Content Controls using VBA</a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-18611520649670080612012-01-29T08:03:00.000-08:002012-01-29T08:03:43.146-08:00How to Search and Highlight/Tag a string in Word VBAHow to Search Content for Specific String/Text using Word VBA<br />
<br />
This action is performed often by programmers - there are couple of ways to do <br />
<br />
1. Selection.Find<br />
2. Content.Find<br />
<br />
We will have a look at how to search a string, highlight the string and tag the same using Word VBA. This needs document to be open <br />
<br />
<pre class="vb" name="code">Sub Highlight_Tag_Found_Word()
Dim sFindText As String
sFindText = "Olympics"
Selection.ClearFormatting
Selection.HomeKey wdStory, wdMove
Selection.Find.ClearFormatting
Selection.Find.Execute sFindText
Do Until Selection.Find.Found = False
Selection.Range.HighlightColorIndex = wdPink
Selection.InsertBefore "< FoundWord >"
Selection.InsertAfter < /FoundWord >
Selection.MoveRight
Selection.Find.Execute
Loop
End Sub
</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-9312363617707519242012-01-21T08:01:00.000-08:002012-01-21T08:01:56.524-08:00How to create a Trendline Chart using Excel VBA<strong><span style="color: red;">Excel VBA - Trendline Charts</span></strong><br />
<br />
Here are some snippets useful to create a TrendLine Chart in Excel<br />
<br />
Have used the entire data from the given sheet to create the chart. Have used the UsedRange function to get that.<br />
<br />
If you want to have a specified range you can pass that also<br />
<br />
<span style="color: blue;">Sub Create_TrendLine_Chart_Excel_2003(ByRef oRep As Worksheet, ByVal iLeft As Double, ByVal iTop As Double, ByVal sChartTitle As String, ByRef oSource As Range)<br />
Dim oChts As ChartObjects '* Chart Object Collection<br />
Dim oCht As ChartObject '* Chart Object</span><br />
<span style="color: blue;">On Error GoTo Err_Chart</span><br />
<span style="color: blue;"> Set oChts = oRep.ChartObjects<br />
Set oCht = oChts.Add(iLeft, iTop, 400, 450)<br />
<br />
oCht.Chart.SetSourceData oSource, PlotBy:=xlColumns<br />
oCht.Chart.ChartType = xlLineMarkers<br />
<br />
oCht.Chart.HasTitle = True<br />
oCht.Chart.ChartTitle.Text = sChartTitle<br />
<br />
oCht.Chart.Legend.Position = xlLegendPositionRight<br />
<br />
oCht.Chart.HasAxis(XlAxisType.xlCategory) = True<br />
oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).HasTitle = True<br />
oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).AxisTitle.Characters.Text = ""<br />
<br />
oCht.Chart.HasAxis(XlAxisType.xlValue) = True<br />
oCht.Chart.Axes(XlAxisType.xlValue, xlPrimary).HasTitle = True<br />
oCht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percentage Done" '.Axes(Type:=XlAxisType.xlValue).AxisTitle.Text = "% Done"<br />
oCht.Chart.Axes(xlValue).MaximumScale = 1<br />
<br />
oCht.Chart.Axes(xlCategory).TickLabelSpacing = 1<br />
oCht.Chart.Axes(xlCategory).TickLabels.Font.Size = 8<br />
<br />
'oCht.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)</span><br />
<span style="color: blue;"> If Not oCht Is Nothing Then Set oCht = Nothing<br />
If Not oChts Is Nothing Then Set oChts = Nothing</span><br />
<br />
<span style="color: blue;">Err_Chart:<br />
If Err <> 0 Then<br />
Debug.Assert Err = 0<br />
Debug.Print Err.Description<br />
If Err.Number = 94 Then 'Invalid Use of Null Error<br />
Err.Clear<br />
Resume Next<br />
Else<br />
Err.Clear<br />
Resume Next<br />
End If<br />
End If</span><br />
<br />
<span style="color: blue;">End Sub</span><br />
<br />
For some reason the above was creating a problem in Excel 2007 and above. Hence created a separate snippet for it<br />
<br />
<span style="color: blue;">Sub Create_TrendLine_Chart_Excel_2007(ByRef oRep As Worksheet, ByVal iLeft As Double, ByVal iTop As Double, ByVal sChartTitle As String)</span><br />
<span style="color: blue;">Dim oChts As ChartObjects '* Chart Object Collection<br />
Dim oCht As ChartObject '* Chart Object</span><br />
<span style="color: blue;">On Error GoTo Err_Chart</span><br />
<span style="color: blue;"> Set oChts = oRep.ChartObjects<br />
Set oCht = oChts.Add(iLeft, iTop, 400, 450)<br />
<br />
oCht.Chart.ChartWizard Source:=oRep.UsedRange<br />
oCht.Chart.ChartType = xlLineMarkers<br />
<br />
oCht.Chart.HasTitle = True<br />
oCht.Chart.ChartTitle.Text = sChartTitle<br />
<br />
oCht.Chart.Legend.Position = xlLegendPositionRight<br />
<br />
<br />
oCht.Chart.HasAxis(XlAxisType.xlCategory) = True<br />
oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).HasTitle = True<br />
oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).AxisTitle.Characters.Text = ""<br />
<br />
oCht.Chart.HasAxis(XlAxisType.xlValue) = True<br />
oCht.Chart.Axes(XlAxisType.xlValue, xlPrimary).HasTitle = True<br />
oCht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percentage Done" '.Axes(Type:=XlAxisType.xlValue).AxisTitle.Text = "% Done"<br />
oCht.Chart.Axes(xlValue).MaximumScale = 1</span><br />
<span style="color: blue;"> 'oCht.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)</span><br />
<br />
<span style="color: blue;">Err_Chart:<br />
If Err <> 0 Then<br />
Debug.Assert Err = 0<br />
Debug.Print Err.Description<br />
If Err.Number = 94 Then 'Invalid Use of Null Error<br />
Err.Clear<br />
Resume Next<br />
Else<br />
Err.Clear<br />
Resume Next<br />
End If<br />
End If</span><br />
<br />
<span style="color: blue;">End Sub</span><br />
<br />
<span style="color: orange;">ChartType = xlLineMarkers</span> makes this Chart a <strong>TrendLine</strong>. You can try your luck by selecting other typesUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-41709322467067925652011-11-06T03:34:00.000-08:002011-11-06T03:34:49.491-08:00How to Extract Properties from Excel/Word without Opening File using VBA<strong><span style="color: red; font-size: large;">How to get CustomProperties from Excel Workbook/Word Document (VBA) without physically opening the file</span></strong><br />
<br />
<br />
There are many cases where we need to get the document property without the file being opened in VBA. <br />
<br />
This can be achieved by using the objects available in DSOFile.dll. This file can be downloaded from <a href="http://support.microsoft.com/kb/224351">http://support.microsoft.com/kb/224351</a><br />
<br />
Once this downloaded and installed. You need to add a reference to DSO Ole Document's property library (refer image below)<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3lAdTj3NfB3Su6wDf0PE7MyXvUCRkKpH7-5RAbWolavN9lWQcMHVr-UFTNa_Q1VBu0epE0JT1OsqNPRWhL3u9w9b62N6K1QASz8qqhhd8fwSHBZZEiTtIcCCIwpNW6X6pHLEe/s1600/VBA+Microsoft+DSO+File+-+Used+to+Read+Properties+of+Excel+Word+without+opening.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="542" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3lAdTj3NfB3Su6wDf0PE7MyXvUCRkKpH7-5RAbWolavN9lWQcMHVr-UFTNa_Q1VBu0epE0JT1OsqNPRWhL3u9w9b62N6K1QASz8qqhhd8fwSHBZZEiTtIcCCIwpNW6X6pHLEe/s640/VBA+Microsoft+DSO+File+-+Used+to+Read+Properties+of+Excel+Word+without+opening.png" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><br />
<pre class="vb" name="code">Function GetPropFromDSO(ByVal sFile As String, ByVal sCP As String) As String
Dim oFil As DSOFile.OleDocumentProperties
Dim oCP As DSOFile.CustomProperties
On Error GoTo Err_Tp
Set oFil = New OleDocumentProperties
oFil.Open sFile, True
Set oCP = oFil.CustomProperties
GetRevFromDSO = oCP(sCP)
.Value
oFil.Close
Err_Tp:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Function
</pre><div class="vb" name="code"><br />
</div><div class="vb" name="code">The function gets the Filename and the Property to be extracted and returns the property value.</div><div class="vb" name="code"><br />
</div><div class="vb" name="code">Here are some important custom properties</div><div class="vb" name="code"><br />
</div><div class="vb" name="code">How to know if a Excel Workbook has Macro without opening it</div><div class="vb" name="code"><br />
</div><div class="vb" name="code"><br />
</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-28492262.post-82230599772055860402011-09-06T09:18:00.000-07:002011-09-06T09:18:46.382-07:00How to link Excel Table to ListBox using VBA<b>Fill a ListBox from Excel Table using VBA / Populate a ListBox from Excel Table using VBA</b><br />
<br />
Let us take a Excel table as shown below - a list of Top 10 All time hits .<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghtoJx6KAM0Q6mX0KbmatSctUMvaqvLJkH40MbbFlYGVNLwYwyM-0c5jOY_w9ETfR7OTVyxz_9S31w_YXONuEbYR4aYRVAGawfo2tGaii9AsgNPF0kxobAeBZ9Lvzwd72eQALp/s1600/Excel+Table+to+Listbox.png" imageanchor="1"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghtoJx6KAM0Q6mX0KbmatSctUMvaqvLJkH40MbbFlYGVNLwYwyM-0c5jOY_w9ETfR7OTVyxz_9S31w_YXONuEbYR4aYRVAGawfo2tGaii9AsgNPF0kxobAeBZ9Lvzwd72eQALp/s640/Excel+Table+to+Listbox.png" width="640" /></a></div><br />
Let us assume that we need to populate the Listbox with values from Column 2<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjp9St6NtRr5yXkSYR14CVN8M93yu2vtB10sAVPtSDjyM0k8Vdks_z3PzclPfUAM1DtJM4_UtglPt3OqubpE0a2tNXykIFbAErfeL6blgN-jXOy6kRa6qTvNJZIy3jGe_6fJ7aI/s1600/Excel+Table+to+Listbox+2.png" imageanchor="1"><img border="0" height="250" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjp9St6NtRr5yXkSYR14CVN8M93yu2vtB10sAVPtSDjyM0k8Vdks_z3PzclPfUAM1DtJM4_UtglPt3OqubpE0a2tNXykIFbAErfeL6blgN-jXOy6kRa6qTvNJZIy3jGe_6fJ7aI/s400/Excel+Table+to+Listbox+2.png" width="400" /></a></div><br />
<br />
The following code will help you populate the data<br />
<br />
<pre class="vb" name="code">Dim oWS As Worksheet
Set oWS = ThisWorkbook.Sheets(3)
Me.ListBox1.List = oWS.ListObjects(1).ListColumns("Title").DataBodyRange.Value
End Sub
</pre><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-sESqsY8298yDFuJ2yt8pPA1NBLlQT3e0JzTHRzshJ7Nyjtvk4QPzAeTUocipFeCu1qEv-71WXMQBFWbl5B2Xwlc9C3xdgourjMN7XMvdXzEmT3eOBivJdk8o8A4f-MyQuzMb/s1600/Excel+Table+to+Listbox+3.png" imageanchor="1"><img border="0" height="252" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-sESqsY8298yDFuJ2yt8pPA1NBLlQT3e0JzTHRzshJ7Nyjtvk4QPzAeTUocipFeCu1qEv-71WXMQBFWbl5B2Xwlc9C3xdgourjMN7XMvdXzEmT3eOBivJdk8o8A4f-MyQuzMb/s400/Excel+Table+to+Listbox+3.png" width="332" /></a></div>Unknownnoreply@blogger.com0