Another excellent question that I came across in http://stackoverflow.com. Pradeep was kind enough to share a C# version of it in the same thread.
Unfortunately there is no inbuilt method where you can loop through all the Excel formulas and gets it’s name and arguments. So how do we retrieve all that information?
One way to retrieve this info is to parse any online page that has all the list and then retrieve the relevant details. For this example, we will use this link
To start with, create a form and place 2 command buttons and a textbox as shown below
In the Get Command Button, paste this code
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click TextBox1.Clear() Dim th As New Threading.Thread(AddressOf GetFormulas) th.Start() End Sub
and In the STOP Command Button, paste this code
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Cancelled = True End Sub
And add this procedure as well.
Sub GetFormulas() Cancelled = False Dim doc As mshtml.HTMLDocument = NewHtmlDoc("http://office.microsoft.com/client/helppreview.aspx?AssetId=HV805551279990&lcid=1033&NS=EXCEL.DEV&Version=12&pid=CH080555125") Dim table As mshtml.IHTMLElement2 = DirectCast(DirectCast(doc.getElementById("vstable"), mshtml.IHTMLElement2).getElementsByTagName("table")(0), mshtml.IHTMLElement2) Dim links As mshtml.IHTMLElementCollection = table.getElementsByTagName("A") For Each link As mshtml.IHTMLElement In links If Cancelled Then Exit For Dim doc2 As mshtml.HTMLDocument = NewHtmlDoc(link.getAttribute("href").ToString) Dim div2 As mshtml.IHTMLElement = doc2.getElementById("m_article") For Each elem As mshtml.IHTMLElement In DirectCast(div2, mshtml.IHTMLElement2).getElementsByTagName("P") If elem.getAttribute("className") IsNot Nothing AndAlso elem.getAttribute("className").ToString = "signature" Then Dim formulaString As String = elem.innerText AddText(link.innerText & vbTab & vbTab & formulaString & vbCrLf) End If Next Next RaiseEvent FormulaRetrivalCompleted(Me, EventArgs.Empty) End Sub Private Function NewHtmlDoc(ByVal url As String) As mshtml.HTMLDocument Dim wc As New Net.WebClient Dim page As String = wc.DownloadString(url) Dim doc As mshtml.IHTMLDocument2 = New mshtml.HTMLDocument doc.write(page) doc.close() Return DirectCast(doc, mshtml.HTMLDocument) End Function Private Sub AddText(ByVal text As String) If TextBox1.InvokeRequired Then Dim d As New AddTextCallback(AddressOf AddText) Me.Invoke(d, text) Else TextBox1.AppendText(text) End If End Sub
Paste this Form1_FormulaRetrivalCompleted event.
Private Sub Form1_FormulaRetrivalCompleted(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.FormulaRetrivalCompleted If Cancelled Then MessageBox.Show("Cancelled!", "Processing Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) Else MessageBox.Show("Processing completed!", "Processing Completed", MessageBoxButtons.OK, MessageBoxIcon.Information) End If End Sub
So your complete code will look like this.
Option Strict On Public Class Form1 Dim Cancelled As Boolean Delegate Sub AddTextCallback(ByVal text As String) Event FormulaRetrivalCompleted As EventHandler Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click TextBox1.Clear() Dim th As New Threading.Thread(AddressOf GetFormulas) th.Start() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Cancelled = True End Sub Sub GetFormulas() Cancelled = False Dim doc As mshtml.HTMLDocument = NewHtmlDoc("http://office.microsoft.com/client/helppreview.aspx?AssetId=HV805551279990&lcid=1033&NS=EXCEL.DEV&Version=12&pid=CH080555125") Dim table As mshtml.IHTMLElement2 = DirectCast(DirectCast(doc.getElementById("vstable"), mshtml.IHTMLElement2).getElementsByTagName("table")(0), mshtml.IHTMLElement2) Dim links As mshtml.IHTMLElementCollection = table.getElementsByTagName("A") For Each link As mshtml.IHTMLElement In links If Cancelled Then Exit For Dim doc2 As mshtml.HTMLDocument = NewHtmlDoc(link.getAttribute("href").ToString) Dim div2 As mshtml.IHTMLElement = doc2.getElementById("m_article") For Each elem As mshtml.IHTMLElement In DirectCast(div2, mshtml.IHTMLElement2).getElementsByTagName("P") If elem.getAttribute("className") IsNot Nothing AndAlso elem.getAttribute("className").ToString = "signature" Then Dim formulaString As String = elem.innerText AddText(link.innerText & vbTab & vbTab & formulaString & vbCrLf) End If Next Next RaiseEvent FormulaRetrivalCompleted(Me, EventArgs.Empty) End Sub Private Function NewHtmlDoc(ByVal url As String) As mshtml.HTMLDocument Dim wc As New Net.WebClient Dim page As String = wc.DownloadString(url) Dim doc As mshtml.IHTMLDocument2 = New mshtml.HTMLDocument doc.write(page) doc.close() Return DirectCast(doc, mshtml.HTMLDocument) End Function Private Sub AddText(ByVal text As String) If TextBox1.InvokeRequired Then Dim d As New AddTextCallback(AddressOf AddText) Me.Invoke(d, text) Else TextBox1.AppendText(text) End If End Sub Private Sub Form1_FormulaRetrivalCompleted(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.FormulaRetrivalCompleted If Cancelled Then MessageBox.Show("Cancelled!", "Processing Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) Else MessageBox.Show("Processing completed!", "Processing Completed", MessageBoxButtons.OK, MessageBoxIcon.Information) End If End Sub End Class
Now when you run it, you will start getting the all the relevant details. I am using the Textbox for the output but you can direct the output to a CSV as well.
HTH 
While answering a question in stackoverflow.com, I came across an interesting question. How do we create a scrollable Excel chart in PowerPoint. In Excel it is very easy to make a scrollable chart using ScrollBar – Form Control. But how do we do it in PowerPoint?
I will cover this in 3 parts
1) Creating the Excel File
2) Setting up your presentation
3) The code
CREATING THE EXCEL FILE
Open a new Excel File and feed in some sample data as shown in the screen shot. I have filled 200 rows with sample data. Once the Data ready, create a line chart. Your Excel File should look like this.
Save the file to say, C: or any other location of your choice.
SETTING UP YOUR PRESENTATION
Open MS PowerPoint and go to slide 1. Click on the tab INSERT | OBJECT
You will be presented with Insert Object Dialog Box. Select the “Create From File” Option and then click on the “Browse” button. Select the Excel file that we had created earlier and select an icon by clicking “Display as Icon” and click on OK.
Your presentation will now look like this
Next navigate to the DEVELOPER Tab. (See Snapshot) In the developer tab, click on additional controls button and select “Microsoft Web Browser” and insert that control in your respective slide. Ensure that it hides the Excel Object that we inserted earlier. Size it accordingly. Also place a Command Button. Name it “Show Chart” or anything else what you feel is right.
And you are done with setting up your presentation
THE CODE
Press ALT + F11 or Click on tab DEVELOPER | VISUAL BASIC on the right hand side to open the visual basic editor.
Paste the code given below in the Slide1 code area.
Option Explicit Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _ (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long Private Const MAX_PATH As Long = 260 Dim ImageFile As String Private Sub CommandButton1_Click() ExtractToTemp WebBrowser1.Navigate ImageFile End Sub Sub ExtractToTemp() Dim oSl As PowerPoint.Slide Dim oSh As PowerPoint.Shape Dim oXLApp As Object, oXLWB As Object, oXLSht As Object Dim mychart As Object Set oSl = ActivePresentation.Slides(1) Set oSh = oSl.Shapes(1) With oSh.OLEFormat.Object.Sheets(1) .Shapes(1).Copy End With '~~> Establish an EXCEL application object On Error Resume Next Set oXLApp = GetObject(, "Excel.Application") If Err.Number <> 0 Then Set oXLApp = CreateObject("Excel.Application") End If Err.Clear On Error GoTo 0 oXLApp.Visible = False '~~> Open the relevant file Set oXLWB = oXLApp.Workbooks.Add Set oXLSht = oXLWB.Worksheets(1) oXLSht.Paste '~~> Save Picture Object ImageFile = TempPath & "Tester.jpg" If Len(Dir(ImageFile)) > 0 Then Kill ImageFile Set mychart = oXLSht.ChartObjects(1).Chart mychart.Export FileName:=ImageFile, FilterName:="jpg" '~~> Wait till the file is saved Do If FileExists(ImageFile) = True Then Exit Do DoEvents Loop oXLWB.Close SaveChanges:=False oXLApp.Quit Set oXLWb = Nothing Set oXLApp = Nothing End Sub '~~> Get User's TempPath Function TempPath() As String TempPath = String$(MAX_PATH, Chr$(0)) GetTempPath MAX_PATH, TempPath TempPath = Replace(TempPath, Chr$(0), "") End Function '~~> Function tot check if file exists Public Function FileExists(strFullPath As String) As Boolean On Error GoTo Whoa If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileExists = True Whoa: On Error GoTo 0 End Function
And you are done. 
Run the presentation by pressing F5 and click on the “Show Chart Button”. You screen show now look like this.
If you have made this presentation then this presentation can also be distributed easily.
Hope this helps…
Not sure if you have heard about it. But maybe you might have. 
I first came across “Add-In Express” couple of months ago while answering questions in the MSDN forum and I was always wondering what was it all about. Do I really need it considering the fact that I had all the tools readily available in Visual Studio. So finally one day, I downloaded a copy to see myself what was so unique about it?
Much to my dislike, I realized my mug of coffee had gone cold by the time I moved away from my laptop (And I love my coffee!!!) However at the same time I was pleasantly surprised with the ease I could now build my Add-ins using Add-In Express.
I went back to the site to see if there was something that I had not yet discovered and found a blog post by Pieter van der Westhuizen. As I started reading it, I felt as if he was echoing my thoughts. Especially this part.
You might be thinking “Why do I need Add-in Express? I get the tools I need for Office development with Visual Studio.” Yes, you do get the tools needed for Microsoft Office development with Visual Studio, but comparing the two is like comparing driving a nail into a piece of wood with either a hammer or a wrench. With the wrench you’ll eventually get the nail into that piece of wood which will take a lot of time and maybe missing the nail and hitting your finger a few times but it’ll work. Whereas with the hammer, driving the nail into that piece of wood will be quick, although you might still miss and hit your finger.
How true! In today’s world, time is really important. As a freelance consultant, I have to work on multiple projects at any given time. And this tool actually made my life much simpler. If you are a serious developer of VSTO add-ins (Be it Excel, Word, Outlook or any other MS-Office App) then I would recommend you to have a look at http://www.add-in-express.com/creating-addins-blog/
I would also like to thank Andrei Smolin (You can also see him selflessly assisting people in the msdn forum) for promptly answering questions that I had about the product. Talk about support! Thanks Andrei
Today while answering a question in MSDN Excel Forum, I came across an interesting question. Can we check if the Excel File has a “Macro”. When I say “Macro”, I am not referring to just any “Code”.
I started off with a small piece of code and as the discussion went on in the thread, I realized that I had not taken several scenarios into consideration. For ex. What if the user had “Require Variable Declaration” checked. My code was just counting the lines and then deciding whether there was a macro or not. I would like to thank Hans Vogelaar for suggesting different scenarios which made it possible to narrow down the code to the specifics.
So how do we check if the file has a “Macro”?
Logic: Strictly speaking Macros start with “Sub“ or “Private Sub” or “Public Sub”. So if we check for “Sub” then we can decide if the file has any macros or not.
Code:
Option Explicit Sub Sample() Dim wb As Workbook Dim HasMacro As Boolean Dim StrCode As String Dim i As Long '~~> Open the file to check if it has any MACRO Set wb = Workbooks.Open("C:\Users\Siddharth Rout\Desktop\book2.xlsm") Select Case UCase(Split(wb.Name, ".")(UBound(Split(wb.Name, ".")))) '<~~ Excel files which can have a macro. Case "XLS", "XLSM", "XLTM", "XLT", "XLA", "XLSB", "XLAM" With wb.VBProject '~~> Components are like sheet1, thisworkbook, module etc. If .VBComponents.Count > 0 Then For i = 1 To .VBComponents.Count '~~> get the entire code in the module StrCode = .VBComponents.Item(i).CodeModule.Lines(1, _ .VBComponents.Item(i).CodeModule.CountOfLines) StrCode = " " & Replace(StrCode, vbCrLf, " ") & " " If InStr(StrCode, " Sub ") > 0 Then HasMacro = True Exit For End If Next End If End With End Select wb.Close SaveChanges:=False If HasMacro Then MsgBox "The workbook has macro" Else MsgBox "The workbook doesn't have a macro" End If End Sub
However an Userform might also have “Sub” so how do we ignore that?
All VBComponents have a type. To check that simply run this code in an Excel file which has Sheets, Thisworkbook, Module, Userform and a Class Module.
Option Explicit Sub Sample() Dim wb As Workbook Dim i As Long Set wb = ActiveWorkbook With wb.VBProject '~~> Components are like sheet1, thisworkbook, module etc. If .VBComponents.Count > 0 Then For i = 1 To .VBComponents.Count Debug.Print .VBComponents.Item(i).Name Debug.Print .VBComponents.Item(i).Type Next End If End With End Sub
Name : ThisWorkbook Type : 100 Name : Sheet1 Type : 100 Name : Module1 Type : 1 Name : UserForm1 Type : 3 Name : Class1 Type : 2
With wb.VBProject '~~> Components are like sheet1, thisworkbook, module etc. If .VBComponents.Count > 0 Then For i = 1 To .VBComponents.Count If .VBComponents.Item(i).Type = 3 Then '~~> Rest of your code End If Next End If End With
.VBComponents.Item(i).Type =
To check if there is any “Code” or not in an excel file you can use this code. This takes account of only “Option Explicit”. You can amend it to also take into considerations the following. One can also just check if the first word is “Option” to trap all the scenarios mentioned below.
Option Compare Binary Option Compare Text Option Private Module Option Base 0 Option Base 1
Code:
Sub Sample() Dim wb As Workbook Dim Count_of_Lines As Long Dim StrCode As String Set wb = Workbooks.Open("C:\Users\Siddharth Rout\Desktop\book2.xlsm") Select Case UCase(Split(wb.Name, ".")(UBound(Split(wb.Name, ".")))) '<~~ Excel files which can have a macro. Case "XLS", "XLSM", "XLTM", "XLT", "XLA", "XLSB", "XLAM" '~~> Taking this approach as there are very few extensions which support macros Case Else MsgBox "The workbook doesn't have any Code" wb.Close SaveChanges:=False Exit Sub End Select Count_of_Lines = 0 With wb.VBProject '~~> Components are like sheet1, thisworkbook, module etc. If .VBComponents.Count > 0 Then For i = 1 To .VBComponents.Count '~~> Get the entire code in the module StrCode = Trim(.VBComponents.Item(i).CodeModule.Lines(1, _ .VBComponents.Item(i).CodeModule.CountOfLines)) If checkstatus(StrCode) = False Then Count_of_Lines = Count_of_Lines + .VBComponents.Item(i).CodeModule.CountOfLines End If Next End If End With If Count_of_Lines > 0 Then MsgBox "The workbook has Code" Else MsgBox "The workbook doesn't have any Code" End If End Sub '~~> Checking if the code doesn't have just blank lines or "Option Explicit" only Function checkstatus(strg As String) As Boolean Dim ar Dim strTemp As String strTemp = strg If InStr(1, strTemp, vbNewLine) Then Do While InStr(1, strTemp, vbNewLine) > 0 strTemp = Replace(strTemp, vbNewLine, "") Loop strTemp = Trim(strTemp) Else strTemp = Trim(strg) End If If Trim(strTemp) = "Option Explicit" Or _ Len(Trim(strTemp)) = 0 Or _ Left(Trim(strTemp), 1) = "'" _ Then checkstatus = True End Function
Let’s say we have the following Macros in Excel
Sub RunMe() MsgBox "Called from VB.net Client", vbInformation, "Demo to run Excel macros from VB.net" End Sub Sub ShowMsg(msg As String, title As String) MsgBox msg, vbInformation, title End Sub
Let’s save the Excel File as Sample.xlsm to C:\. If you notice the first macro doesn’t take an argument and the second one take 2 arguments. Let’s try and call that from vb.net.
Now open the a new project in VB.net and put two command buttons on it. Your project should look like this.
Now add a reference to Excel Object Library. Covered here (Section: Setting up VB.Net to Work with Excel).
Once you have the references set up, use this code.
Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click '~~> Define your Excel Objects Dim xlApp As New Excel.Application Dim xlWorkBook As Excel.Workbook '~~> Start Excel and open the workbook. xlWorkBook = xlApp.Workbooks.Open("C:\book1.xlsm") '~~> Run the macros. xlApp.Run("RunMe") '~~> Clean-up: Close the workbook and quit Excel. xlWorkBook.Close(False) '~~> Quit the Excel Application xlApp.Quit() '~~> Clean Up releaseObject(xlApp) releaseObject(xlWorkBook) End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click '~~> Define your Excel Objects Dim xlApp As New Excel.Application Dim xlWorkBook As Excel.Workbook '~~> Start Excel and open the workbook. xlWorkBook = xlApp.Workbooks.Open("C:\book1.xlsm") '~~> Run the macros. xlApp.Run("ShowMsg", "Hello from VB .NET Client", "Demo 2nd Button") '~~> Clean-up: Close the workbook and quit Excel. xlWorkBook.Close(False) '~~> Quit the Excel Application xlApp.Quit() '~~> Clean Up releaseObject(xlApp) releaseObject(xlWorkBook) End Sub '~~> Release the objects Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Class
SNAPSHOTS
1) Button 1
2) Button 2
Calling Excel macros from vb.net is easy as you must have discovered by now 
Hope this helps
While answering a question today in StackOverflow, I came across an interesting question. And that inspired me to write a blog post on it.
PROBLEM: How to enforce Data Validation to work from VBA. By default you can set a data validation condition from VBA but it doesn’t work if you manually try to set a value to the cell. For example, this piece of code adds a DataValidation to Cell A1 in Sheet1 but doesn’t throw any error message when you supply a different value to the cell.
Sub Sample()
With Sheets("Sheet1").Range("A1")
.Validation.Delete
.Validation.Add Type:=xlValidateList, Formula1:="TRUE,FALSE"
.Value = "SID" '<~~ Trying to pass an invalid value
End With
End Sub
SOLUTION:
The solution to the above problem is a “Sideways” enforcing of DataValidation from Worksheet_Change() event. The Worksheet_Change() event is a procedure of the Worksheet Object and as such, it should reside in the private module of the Worksheet Object. This event fires whenever there is a change in the worksheet. There are few changes which of course cannot be trapped from the Worksheet_Change() event. For example – Resizing of Columns. So back to the point. The logic that we have to use:
1) Check if the change is happening in the relevant cell. For this we can use Intersect() method.
2) And if the change is happening in the the relevant cell, then check if the cell has a DataValidation.
3) If the cell has DataValidation then check for the “Type” of DataValidation
4) Once the type has been ascertained, check if the current value of the cell conforms to the formula of the DataValidation
CODE
Const dvMessage = "Incorrect Value. Please ensure that the value conforms to the Data Validation set on the cell" Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoa If Not Intersect(Target, Range("A1")) Is Nothing Then Application.EnableEvents = False On Error Resume Next If Not Target.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then Dim currentValidation As Excel.Validation Set currentValidation = Target.Validation If currentValidation.Type = xlValidateList Then Dim MyArray() As String Dim boolFound As Boolean MyArray = Split(currentValidation.Formula1, ",") For i = 0 To UBound(MyArray) If UCase(Target.Value) = UCase(MyArray(i)) Then boolFound = True Exit For End If Next i If boolFound = False Then MsgBox dvMessage Target.ClearContents End If End If End If On Error GoTo 0 End If LetsContinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub
With slight modification, the above code can also work very well for xlValidateWholeNumber, xlValidateCustom, xlValidateDecimal, xlValidateDate, xlValidateTime, xlValidateTextLength and xlValidateCustom
Hope this helps 
This is in continuation to my previous post where I showed you on how to click the “Open/Cancel Button”.
I am quite surprised by the number of hits that I am getting on that post. So I have decided to go ahead and post the code for “Save” button as well.
But before we go ahead, let me show you something interesting. If you have Spy ++ or uuSpy then you can see what I mean.
Our first impression is that once we click the “Save” button then it will be easy to populate the filename in the “Save As dialog” box. Well that is not that easy because it depends on the nesting windows. See these two examples. The first one is for IE6 and the other is for IE 8/9.
In the below code, I have used lot’s of Message Boxes and Wait so that you can actually step through the code or see how the code executes.
Paste this in a Module
Option Explicit Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _ (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _ ByVal lpsz2 As String) As Long Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _ (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long Private Declare Function GetWindowTextLength Lib "user32" Alias _ "GetWindowTextLengthA" (ByVal hwnd As Long) As Long Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _ (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, _ lpRect As RECT) As Long Private Declare Sub SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal _ hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As _ Long, ByVal cy As Long, ByVal wFlags As Long) Private Declare Function SetCursorPos Lib "user32" _ (ByVal X As Integer, ByVal Y As Integer) As Long Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Private Declare Sub mouse_event Lib "user32.dll" (ByVal dwFlags As Long, _ ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long) '~~> Constants for pressing left button of the mouse Const MOUSEEVENTF_LEFTDOWN As Long = &H2 '~~> Constants for Releasing left button of the mouse Const MOUSEEVENTF_LEFTUP As Long = &H4 Const WM_SETTEXT As Long = &HC Const BM_CLICK = &HF5 Const HWND_TOPMOST = -1 Const HWND_NOTOPMOST = -2 Const SWP_NOSIZE = &H1 Const SWP_NOMOVE = &H2 Const SWP_NOACTIVATE = &H10 Const SWP_SHOWWINDOW = &H40 Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Dim Ret As Long, OpenRet As Long, FlDwndHwnd As Long Dim ChildRet As Long Dim strBuff As String, ButCap As String Dim pos As RECT '~~> Use this if you want to specify your own name in the Save As Window Const FileSaveAsName = "C:\MyFile.xls" Private Sub CommandButton1_Click() On Error GoTo Whoa Ret = FindWindow(vbNullString, "File Download") If Ret <> 0 Then MsgBox "Main Window Found" '~~> Get the handle of the Button's "Window" ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString) If ChildRet = 0 Then MsgBox "Child Window Not Found" Exit Sub End If '~~> Get the caption of the child window strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0)) GetWindowText ChildRet, strBuff, Len(strBuff) ButCap = strBuff '~~> Loop through all child windows Do While ChildRet <> 0 '~~> Check if the caption has the word "Save" If InStr(1, ButCap, "Save") Then '~~> If this is the button we are looking for then exit OpenRet = ChildRet Exit Do End If '~~> Get the handle of the next child window ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString) '~~> Get the caption of the child window strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0)) GetWindowText ChildRet, strBuff, Len(strBuff) ButCap = strBuff Loop '~~> Check if we found it or not If OpenRet = 0 Then MsgBox "The Handle of Save Button was not found" Exit Sub End If '~~> Retrieve the dimensions of the bounding rectangle of the '~~> specified window. The dimensions are given in screen '~~> coordinates that are relative to the upper-left corner of the screen. GetWindowRect OpenRet, pos '~~> Move the cursor to the specified screen coordinates. SetCursorPos (pos.Left - 10), (pos.Top - 10) '~~> Suspends the execution of the current thread for a specified interval. '~~> This give ample amount time for the API to position the cursor Sleep 100 SetCursorPos pos.Left, pos.Top Sleep 100 SetCursorPos (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2 '~~> Set the size, position, and Z order of "File Download" Window SetWindowPos Ret, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE Sleep 100 '~~> Simulate mouse motion and click the button '~~> Simulate LEFT CLICK mouse_event MOUSEEVENTF_LEFTDOWN, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0 Sleep 700 '~~> Simulate Release of LEFT CLICK mouse_event MOUSEEVENTF_LEFTUP, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0 Wait 10 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' START OF SAVEAS ROUTINE ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Ret = FindWindow(vbNullString, "Save As") If Ret = 0 Then MsgBox "Save As Window Not Found" Exit Sub End If '~~> UNCOMMENT this if using IE6 and COMMENT the code for "DUIViewWndClassName" '~~> "DirectUIHWND" and "FloatNotifySink" ' '~~> Get the handle of the Main ComboBox ' ChildRet = FindWindowEx(Ret, ByVal 0&, "ComboBoxEx32", "") ' ' If ChildRet = 0 Then ' MsgBox "ComboBoxEx32 Window Not Found" ' Exit Sub ' End If ChildRet = FindWindowEx(Ret, ByVal 0&, "DUIViewWndClassName", "") If ChildRet = 0 Then MsgBox "DUIViewWndClassName Not Found" Exit Sub End If ChildRet = FindWindowEx(ChildRet, ByVal 0&, "DirectUIHWND", "") If ChildRet = 0 Then MsgBox "DirectUIHWND Not Found" Exit Sub End If ChildRet = FindWindowEx(ChildRet, ByVal 0&, "FloatNotifySink", "") If ChildRet = 0 Then MsgBox "FloatNotifySink Not Found" Exit Sub End If '~~> Get the handle of the Main ComboBox ChildRet = FindWindowEx(ChildRet, ByVal 0&, "ComboBox", "") If ChildRet = 0 Then MsgBox "ComboBox Window Not Found" Exit Sub End If '~~> Get the handle of the Edit ChildRet = FindWindowEx(ChildRet, ByVal 0&, "Edit", "") If ChildRet = 0 Then MsgBox "Edit Window Not Found" Exit Sub End If '~~> COMMENT the below 3 lines if you do not want to specify a filename Wait 10 SendMess FileSaveAsName, ChildRet Wait 10 '~~> Get the handle of the Save Button in the Save As Dialog Box ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString) '~~> Check if we found it or not If ChildRet = 0 Then MsgBox "Save Button in Save As Window Not Found" Exit Sub End If '~~> Get the caption of the child window strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0)) GetWindowText ChildRet, strBuff, Len(strBuff) ButCap = strBuff '~~> Loop through all child windows Do While ChildRet <> 0 '~~> Check if the caption has the word "Save" If InStr(1, ButCap, "Save") Then '~~> If this is the button we are looking for then exit OpenRet = ChildRet Exit Do End If '~~> Get the handle of the next child window ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString) '~~> Get the caption of the child window strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0)) GetWindowText ChildRet, strBuff, Len(strBuff) ButCap = strBuff Loop '~~> Check if we found it or not If OpenRet = 0 Then MsgBox "The Handle of Save Button in Save As Window was not found" Exit Sub End If '~~> Save the file SendMessage OpenRet, BM_CLICK, 0, ByVal 0& Wait 10 Else MsgBox "File Download Window Not found" End If Exit Sub Whoa: MsgBox Err.Description End Sub Sub Wait(nSec As Double) nSec = nSec + Timer While nSec > Timer DoEvents Wend End Sub Sub SendMess(Message As String, hwnd As Long) Call SendMessage(hwnd, WM_SETTEXT, False, ByVal Message) End Sub
To convert the above code to VB6 or VB.Net, refer to my previous post where I have already given an example.
If you understand the basic structure on how to get the handle of a window then the above task will look like a piece of cake.
Here are ways to ease your pain 
We will separate the article in 3 sections
Filling PDF Form Fields
Imagine if you need to fill a Daily Report and mail it to your boss over the email every day? In the beginning, it might sound adventurous but then you start hating the monotonous routine.
Here is one quick way to fill the Form Fields of PDF using VB.Net from data stored in Excel.
To begin with let’s design our form. Place 2 textboxes, 4 command buttons and one OpenFileDialog on a form. Once done the form will look similar to what is show below.
Now let’s say you have a PDF which looks like below. I created this PDF just to demonstrate on how to fill the PDF.
Once the data is filled, your PDF will look like this
Next is preparing your Excel Database.
Open Excel and Type the Data as shown below. Once done, save it as C:\PDF_FORM_DATA.XLSX.
This is how your Excel file might look.
We are now all set to write the code in VB.net
Double click on the “Browse” Button which we will be use to select the PDF file and paste this code.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click With OpenFileDialog1 .DefaultExt = ".PDF" .DereferenceLinks = True .Filter = _ "PDF files (*.PDF)|*.PDF" .Multiselect = False .Title = "Select a PDF file to open" .ValidateNames = True If .ShowDialog = Windows.Forms.DialogResult.OK Then Try TextBox1.Text = .FileName Catch fileException As Exception Throw fileException End Try End If End With End Sub
Next Double click on the “Browse” Button which we will be use to select the Excel file and paste this code.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click With OpenFileDialog1 .DefaultExt = ".XLSX" .DereferenceLinks = True .Filter = _ "Excel files (*.XLSX)|*.XLSX" .Multiselect = False .Title = "Select an Excel file to open" .ValidateNames = True If .ShowDialog = Windows.Forms.DialogResult.OK Then Try TextBox2.Text = .FileName Catch fileException As Exception Throw fileException End Try End If End With End Sub
Now Double click on the “Cancel” Button and paste this code.
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Me.Close() End Sub
Finally Double click on the “Update” Button and paste this code.
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim pdfTemplate As String = TextBox1.Text '~~> Change the Output FileName here Dim PDFUpdatedFile As String = "C:\PDF_FORM_DATA (UPDATED).pdf" Dim readerPDF As New PdfReader(pdfTemplate) Dim stamperPDF As New PdfStamper(readerPDF, _ New FileStream(PDFUpdatedFile, FileMode.Create)) Dim pdfFormFields As AcroFields = stamperPDF.AcroFields '~~> Define your Excel Objects Dim xlApp As New Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet '~~> Show/Hide Excel xlApp.Visible = True '~~> Opens an exisiting Workbook xlWorkBook = xlApp.Workbooks.Open(TextBox2.Text) '~~> Set the relevant sheet that we want to work with xlWorkSheet = xlWorkBook.Sheets("Sheet1") '~~> Update pdf FormFields pdfFormFields.SetField(xlWorkSheet.Range("A2").Value.ToString, xlWorkSheet.Range("B2").Value.ToString) pdfFormFields.SetField(xlWorkSheet.Range("A3").Value.ToString, xlWorkSheet.Range("B3").Value.ToString) pdfFormFields.SetField(xlWorkSheet.Range("A4").Value.ToString, xlWorkSheet.Range("B4").Value.ToString) '~~> To remove editting options from the output Form, set it to FALSE '~~> To leave then editting options open in the output Form, set it to TRUE stamperPDF.FormFlattening = True '~~> close the pdf stamperPDF.Close() '~~> Close the Excel file without saving xlWorkBook.Close(False) '~~> Quit the Excel Application xlApp.Quit() '~~> Clean Up releaseObject(xlApp) releaseObject(xlWorkBook) End Sub
Add the below code at the very top.
Imports iTextSharp Imports iTextSharp.text Imports iTextSharp.text.pdf Imports iTextSharp.text.xml Imports System.IO Imports Excel = Microsoft.Office.Interop.Excel
and add one Sub at the bottom for clean up.
'~~> Release the objects Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub
The last thing that we need to do is setting References for Excel and itextsharp.dll
itextsharp.dll is freely available on the web and it is free to use for Non Commercial Applications. Once you have downloaded the file, save it at a location of your choice. Once downloaded, click on the menu PROJECT | Add Reference. Navigate to the “Browse” tab and select the DLL and click on OK.
Next set a reference to the Excel Object Library. See this link for more information.
Your Final code will look like this. Now run the code and select the relevant files. Once done click on update. The new file with the updated Data will be saved in C:\ as PDF_FORM_DATA (UPDATED).PDF.
You can change the file name and path of the updated file in the code itself.
FINAL CODE:
Imports iTextSharp Imports iTextSharp.text Imports iTextSharp.text.pdf Imports iTextSharp.text.xml Imports System.IO Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click With OpenFileDialog1 .DefaultExt = ".PDF" .DereferenceLinks = True .Filter = _ "PDF files (*.PDF)|*.PDF" .Multiselect = False .Title = "Select a PDF file to open" .ValidateNames = True If .ShowDialog = Windows.Forms.DialogResult.OK Then Try TextBox1.Text = .FileName Catch fileException As Exception Throw fileException End Try End If End With End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click With OpenFileDialog1 .DefaultExt = ".XLSX" .DereferenceLinks = True .Filter = _ "Excel files (*.XLSX)|*.XLSX" .Multiselect = False .Title = "Select an Excel file to open" .ValidateNames = True If .ShowDialog = Windows.Forms.DialogResult.OK Then Try TextBox2.Text = .FileName Catch fileException As Exception Throw fileException End Try End If End With End Sub Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Me.Close() End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim pdfTemplate As String = TextBox1.Text '~~> Change the Output FileName here Dim PDFUpdatedFile As String = "C:\PDF_FORM_DATA (UPDATED).pdf" Dim readerPDF As New PdfReader(pdfTemplate) Dim stamperPDF As New PdfStamper(readerPDF, _ New FileStream(PDFUpdatedFile, FileMode.Create)) Dim pdfFormFields As AcroFields = stamperPDF.AcroFields '~~> Define your Excel Objects Dim xlApp As New Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet '~~> Show/Hide Excel xlApp.Visible = True '~~> Opens an exisiting Workbook xlWorkBook = xlApp.Workbooks.Open(TextBox2.Text) '~~> Set the relevant sheet that we want to work with xlWorkSheet = xlWorkBook.Sheets("Sheet1") '~~> Update pdf FormFields pdfFormFields.SetField(xlWorkSheet.Range("A2").Value.ToString, xlWorkSheet.Range("B2").Value.ToString) pdfFormFields.SetField(xlWorkSheet.Range("A3").Value.ToString, xlWorkSheet.Range("B3").Value.ToString) pdfFormFields.SetField(xlWorkSheet.Range("A4").Value.ToString, xlWorkSheet.Range("B4").Value.ToString) '~~> To remove editting options from the output Form, set it to FALSE '~~> To leave then editting options open in the output Form, set it to TRUE stamperPDF.FormFlattening = True '~~> close the pdf stamperPDF.Close() '~~> Close the Excel file without saving xlWorkBook.Close(False) '~~> Quit the Excel Application xlApp.Quit() '~~> Clean Up releaseObject(xlApp) releaseObject(xlWorkBook) End Sub '~~> Release the objects Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Class
Extracting PDF Form Field names
Now Let’s assume that you don’t know what the form fields are. In such a case you can extract the field names from the PDF using the below code.
Insert one more textbox in the above form and also add one more command button. Set the multiline property of the textbox to TRUE.
Your form now should look like this
Double click on the “Get Form Fields” Button and paste this code.
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click Dim pdfTemplate As String = TextBox1.Text Dim readerPDF As New PdfReader(pdfTemplate) Dim PDFfld As Object For Each PDFfld In readerPDF.AcroFields.Fields If TextBox3.Text = "" Then TextBox3.Text = PDFfld.key.ToString() Else TextBox3.Text = TextBox3.Text & Environment.NewLine & PDFfld.key.ToString() End If Next TextBox3.SelectionStart = 0 End Sub
Now when you select the pdf file and click on the “Get Form Fields” button, you will notice the textbox populates with the Form Field names.
Retrieving data from PDF Form Fields
Now let’s add one more button to the above form and call it “Get Form Data”. Your form should look like this.
Double click on the “Get Form Data” Button and paste this code. I will be using the same Excel file that we created above to output the data. I would output the data in Col C.
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click Dim pdfTemplate As String = TextBox1.Text Dim readerPDF As New PdfReader(pdfTemplate) Dim pdfFormFields As AcroFields = readerPDF.AcroFields '~~> Define your Excel Objects Dim xlApp As New Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim LastRow As Integer, i As Integer '~~> Show/Hide Excel xlApp.Visible = True '~~> Opens an exisiting Workbook xlWorkBook = xlApp.Workbooks.Open(TextBox2.Text) '~~> Set the relevant sheet that we want to work with xlWorkSheet = xlWorkBook.Sheets("Sheet1") '~~> Get the last row in Col A which has Form Fields LastRow = xlWorkSheet.Range("A" & xlApp.Rows.Count).End(Excel.XlDirection.xlUp).Row '~~> Loop through Col A and use the Form Fields to extract text For i = 2 To LastRow xlWorkSheet.Range("C" & i).Value = pdfFormFields.GetField(xlWorkSheet.Range("A" & i).Value) Next '~~> Close the Excel file without saving xlWorkBook.Close(True) '~~> Quit the Excel Application xlApp.Quit() '~~> Clean Up releaseObject(xlApp) releaseObject(xlWorkBook) End Sub
This is how the Excel file looks after you run the above code.
I recently discovered this Extension for Visual Studio. It is called the “Visual Studio Achievements”. Microsoft’s Channel 9 launched the beta on the 18th. I have already downloaded it and it bought a smile on my face every time an achievement was unlocked.
With the Visual Studio Achievements Extension, achievements are unlocked based on your activity. Your code is analysed on a background thread each time you compile. In addition, the extension listens for certain events and actions that you may perform in Visual Studio, reporting progress on these events to the server.
When an achievement is unlocked, Visual Studio lets you know visually with a pop-up.
The only thing which I didn’t like is the way it is being promoted
“Impress your friends!
Earn achievements while you code!
Code while you earn achievements! “
Whoa! What has coding to do with impressing your friends??? 
This extension is not for programmers who deliberately try to unlock the achievements to impress their friends. I would recommend the Facebook “Farm-Ville” crap for that. 
The thing that really caught my attention was this line and which I really liked.
“But rarely is a developer appreciated for all the nuances and subtleties of a piece of code–and all the heroics it took to write it. With Visual Studio Achievements Beta, your talents are recognized as you perform various coding feats, unlock achievements and earn badges.”
If I were to rank it on a scale of 0 – 10, I would definitely give it a 10 even though it is on a Beta version.
So go ahead and give it a try and I am sure you will love it!
VB.Net – Interacting with Comments in Excel Cells
In this post we will cover the following
I am assuming that you have your application setup and ready. If not see here.
INSERTING COMMENT
Once your application is all setup, we will insert a CommandButton (Name it “Insert Comment”) and in the command button we will add a new workbook. You application might look like this.
The below code will insert “Hello! This is a Sample Comment” in Cell D10
SNAPSHOT
EDITING COMMENT
DELETING THE COMMENT
Insert a 3rd button in the application and name it say, “Delete Comment”
In the click event of the button paste this. This is perhaps the easiest of all the code we will be writing here. All we need to do is issue the delete command.
SHOW/HIDE THE COMMENT
Let’s re-insert the comment again so that we can continue with the rest of the things. Click the “Insert Comment” button again to re-insert the comment.
To show the comment you can use
And to hide it
FORMATTING THE COMMENT
Now we know how to Add/Modify/Delete/Show/Hide a Comment. Let’s see how can we format it. Below are 1 or 2 liners which will help you format the comments
Increasing/Decreasing the Height of the Comment
'~~> Set the relevant Height xlRng.Comment.Shape.Height = 150Increasing/Decreasing the Width of the Comment
'~~> Set the relevant Width xlRng.Comment.Shape.Width = 150Changing the background color of the Comment
INSERTING AN IMAGE IN THE COMMENT
In this example, I will insert an image from the user’s sample picture directory and the image that I will use is “Chrysanthemum.jpg”. If you want to autosize the comment to show the image in it’s actual size then see the next section “Inserting a chart in the comment”
In a button click, insert this code
INSERTING A CHART IN THE COMMENT
Let’s say we have a file which has a chart and it looks like this
And let’s say we want to show this chart in a comment in cell J10. Use this code in a button click.
Hope this helps