Add-in Express


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/04/18/add-in-express/

 

Not sure if you have heard about it. But maybe you might have. Smile

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 Smile

Check if an Excel File has a Macro


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/04/12/check-if-an-excel-file-has-a-macro/

 

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
You will notice that these are the types
Name : ThisWorkbook Type : 100
Name : Sheet1       Type : 100
Name : Module1      Type : 1
Name : UserForm1    Type : 3
Name : Class1       Type : 2
So you can actually trap the “type” in the above code to ignore say Userforms
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
You also use the Excel Constants instead of the above numbers for example, when you type
.VBComponents.Item(i).Type =
Intellisense automatically gives you the options. See picture.
image
Taking this post a step forward.
  • How to Check if an Excel File has any code

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
EDIT
This edit was required as Doug Glancy gave some nice suggestions.
To access the VBA components as shown in the code above, the user needs to have checked “Trust access to the VBA project object model” in Macro Security. To do this, follow these steps
Excel 2003: Click on menu Tools | Macro | Security to access the”Security” dialog Box. Under “Trusted Publisher” tab, check the box which says “Trust access to  the Visul Basic Project

Excel 2007 : Click on the “Office”  icon| Excel Options. You will get a dialog box as shown in the snapshot below. Click on “Trust Center Settings“. Under the “Macro Settings“, check the box which says “Trust access to  the VBA project object model
Excel 2010 : Click on the “File” Tab | Options. You will get a dialog box as shown in the snapshot below. Click on “Trust Center Settings“. Under the “Macro Settings“, check the box which says “Trust access to  the VBA project object model
HTH Smile

Calling Excel Macros programmatically in VB.Net


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/03/20/calling-excel-macros-programmatically-in-vb-net/

 

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.

image

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

image

2) Button 2

image

Calling Excel macros from vb.net is easy as you must have discovered by now Smile

Hope this helps Smile

VBA – Data Validation and Enforcing it to work


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/03/16/vba-data-validation-and-enforcing-it-to-work/

 

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 Smile

VBA/VB.Net/VB6–Click Open/Save/Cancel Button on IE Download window – PART II


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/02/02/vbavb-netvb6click-opensavecancel-button-on-ie-download-window-part-ii/

 

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.

image

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.

UPDATE: 25/7/2012

NOTE: For people using IE9, depending on the link that you pass to the browser, you may or may not see the Info Security Bar. If you see the Info security bar then I suggest seeing this link where I have attached an exe file which you can use to bypass the IE9 Info Security Bar.


Fill/Retrieve data from PDF Form Fields using VB.Net From an Excel File


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/01/28/fillretrieve-data-from-pdf-form-fields-using-vb-net-from-an-excel-file/

 

  • Tired of filling a PDF report every day? OR
  • Tired of collecting information from a pdf everyday?

Here are ways to ease your pain Smile

We will separate the article in 3 sections

  1. Filling PDF Form Fields
  2. Extracting the PDF Form Field names
  3. Retrieving data from PDF Form Fields

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 OpenFileDialogon a form. Once done the form will look similar to what is show below.

image

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.

image

Once the data is filled, your PDF will look like this

image

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.

image

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.

image

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.

Info 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

image

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.

image


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.

image

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.

image


Visual Studio Achievements


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/01/23/visual-studio-achievements/

 

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.

image

image

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??? Winking smile

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. Disappointed smile

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 to Read and Set Excel’s Inbuilt Document Properties


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/01/18/vb-net-to-read-and-set-excels-inbuilt-document-properties/

 

Document properties allow us to get information about a document. They are either built into the document, or are custom, user defined properties. The default properties are called BuiltinDocumentProperties.

Here is a list of complete built-in properties are

  1. Title
  2. Subject
  3. Author
  4. Keywords
  5. Comments
  6. Template
  7. Last author
  8. Revision number
  9. Application name
  10. Last print date
  11. Creation date
  12. Last save time
  13. Total editing time
  14. Number of pages
  15. Number of words
  16. Number of characters
  17. Security
  18. Category
  19. Format
  20. Manager
  21. Company
  22. Number of bytes
  23. Number of lines
  24. Number of paragraphs
  25. Number of slides
  26. Number of notes
  27. Number of hidden Slides
  28. Number of multimedia clips
  29. Hyperlink base
  30. Number of characters (with spaces)
  31. Content type
  32. Content status
  33. Language
  34. Document version

The document properties are shared by all Office applications. However not all the above built-in properties are supported by all Office programs. For example the “25. Number Of Slides” property applies only to PowerPoint and doesn’t apply to Excel.

The properties which I frequently use with Excel are

Title
Subject
Keywords (Tags)
Comments
Revision number

So how do we get the list of all document properties using VB.Net and How do we set their values?

Get the list of all document properties of an Excel Document say SAMPLE.XLSX

After you have set the relevant references to excel, use the code given below. The code will type the name of all the properties in a sheet in the Excel File. I would recommend adding a temporary sheet in the file and use that. For the code below, I am assuming that the name of the temporary sheet is “Temp”

Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim DocProps As Object, DProps As Object

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")
        xlSheet = xlWorkBook.Sheets("Temp")

        DocProps = xlWorkBook.BuiltinDocumentProperties

        '~~> Display Excel
        xlApp.Visible = False

        '~~> Loop via all properties
        If Not (DocProps Is Nothing) Then
            Dim i As Integer
            For i = 1 To DocProps.Count
                DProps = DocProps(i)
                xlSheet.Range("A" & i.ToString()).Value2 = DProps.Name
            Next i
        End If

        '~~> Save and Close the File
        xlWorkBook.Close(True)

        '~~> 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

When you run the code you will get this kind of output

image

Setting the values of Properties

And here is an example where we will try and update few of the values.

    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim DocProps As Object, DProps As Object

    '~~> Setting the Built in Document Properties
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Dim prop As Object

        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("E:\Users\Siddharth Rout\Desktop\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        DocProps = xlWorkBook.BuiltinDocumentProperties

        DocProps("Title").Value = "Summary Report"
        DocProps("Subject").Value = "Sales"
        DocProps("Keywords").Value = "Sales; Report; Summary"

        '~~> Close the File
        xlWorkBook.Close(True)

        '~~> 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

And here is a snapshot of the properties

image

VB.Net and MS Word Poll


I have been contemplating on creating a separate tab for “VB.Net and MS Word”. Like the one I have for “VB.Net and MS Excel”.

Color Print your code with Editor Format colors


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/10/31/color-print-your-code-with-editor-format-colors/

 

Here is one link which I found today and I feel that I should share it. Though this application has been discontinued but it still works on Vista and Win 7.

You can use this application to color print your VB6/VB.Net/VBA code.

Name of Application: PrettyCode.Print

Link: http://submain.com/products/prettycode.print.aspx

Hope this helps Smile