Category Archives: Vb.Net

Vb.Net Two Dot Rule when working with Office Applications


EDIT

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

http://www.siddharthrout.com/2012/08/06/vb-net-two-dot-rule-when-working-with-office-applications-2/

 

The Two Dot rule unfortunately is not very well documented in msdn. The only mention of it happens to be in the All-In-One Code Framework.


What is Two Dot Rule?


The Two Dots tunnels your call into the Com object model to access it’s properties.

comObject.Property.PropertiesProperty

Let’s take an example

Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook

xlWorkBook = xlApp.Workbooks.Add

Do you notice the two dots? One after the xlApp and the other after Workbooks?

Using Two Dots when calling xlApp.Workbooks.Add creates an RCW (Runtime Callable Wrapper) for the Workbooks object. If you invoke these accessors, the RCW  for Workbooks is created on the GC heap. However what’s worth noting is that the reference is created under the hood on the stack and are then discarded. Because of this there is no way to call MarshalFinalReleaseComObject on this RCW. Therefore, if all references have not been released on the RCW, the COM object does not quit and this results in an instance of your Excel Application (in this case) being left in Task Manager.


Is ignoring Two Dot Rule Bad?


Honestly, if I may say so, it all depends on how you flush the toilet after use!

As mentioned above, there is no way to call MarshalFinalReleaseComObject on this RCW. You will have to either force a garbage collection as soon as the calling function is off the stack or you would need to explicitly assign each accessor object to a variable and free it.

Let’s take an example

Dim xlApp As New Excel.Application
Dim xlWorkBooks As Excel.Workbooks = xlApp.Workbooks
Dim xlWorkBook As Excel.Workbook = xlWorkBooks.Add()

'
'~~> rest of the code
'

xlApp.Quit()

If Not xlWorkBook Is Nothing Then
    Marshal.FinalReleaseComObject (xlWorkBook)
    xlWorkBook = Nothing
End If
If Not xlWorkBooks Is Nothing Then
    Marshal.FinalReleaseComObject (xlWorkBooks)
    xlWorkBooks = Nothing
End If
If Not xlApp Is Nothing Then
    Marshal.FinalReleaseComObject (xlApp)
    xlApp = Nothing
End If
Having a VBA background, ignoring the two dot rules comes very naturally for me. And there is nothing wrong with it till the time you are doing a Garbage Collection in the end. Let’s take an example.
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook (IGNORING THE TWO DOT RULE)
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Do some stuff Here

        '~~> Save the file
        xlWorkBook.SaveAs(Filename:="C:\Tutorial\SampleNew.xlsx", FileFormat:=51)

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

        '~~> 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
So in the end actually it is up to you which style of coding you like.

Biggest multipurpose FREE Excel Add-in


EDIT

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

http://www.siddharthrout.com/free-add-in/

 

I am planning to create a FREE VSTO Excel Add-In which can be used by every one. I would request you to leave your requests here.

I intend to make this the biggest multipurpose FREE Add-In for everyone so be sure you leave your request Winking smile

Embed Excel Documents in VB.Net Application


EDIT

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

http://www.siddharthrout.com/2012/07/02/embed-excel-documents-in-vb-net-application/

 

As far as I know, there are no native .NET controls for embedding Office applications.

Earlier you could use the DSOFramer to achieve what you want but then it was discontinued. I believe it still works though (I am not sure). The Web Browser (COM) control is an alternative to DSOFramer, but has its own drawbacks. For example, you cannot use the inbuilt Excel “Goodies”.

Disclaimer: The below is just my personal opinion

I doubt that MS will never support embedded Office applications and the reason is very simple. MS-Office Applications are “End-User-Targeted” products. You need separate licenses for it and from a business perspective they wouldn’t want to loose on that

Having said that what alternatives do we have?

Recently I came across Edraw Office Viewer Component (EOVC)  and I was pretty much inspired by it. It not only allows us to embed the document but also gives us the experience of working in Excel directly. But here is the catch! It is not free. Considering the things which we can do with this control (in absence of a similar control in VS), I wouldn’t mind paying for it though.

Let’s test the Component. I would be testing this component in Excel 2010 and VS 2010

First download the 30 day trial version from this link.

Once you have installed it, open a new Windows Application and in the ToolBox add the Component. You can do that by Right Clicking on the ToolBox and Clicking on Choose items. Under the COM Components TAB, select the Edraw Office Viewer Component.

image

Click on OK. Your toolbox now looks like this.

image

Create a new form and place the EOVC on the form. Add couple of buttons so that your form now looks like this

image

Place this code in the form

Public Class Form1

    '~~> Create a New File
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        AxEDOffice1.CreateNew("Excel.Application")
    End Sub

    '~~> Load a File
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        AxEDOffice1.OpenFileDialog("Excel Files(*xls;*.xlsx)|*.xls;*.xlsx")
    End Sub

    '~~> Save File
    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        AxEDOffice1.Save()
    End Sub

    '~~> File Save As
    Private Sub Button7_Click(sender As System.Object, e As System.EventArgs) Handles Button7.Click
        AxEDOffice1.SaveFileDialog()
    End Sub

    '~~> Closing the file
    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        AxEDOffice1.CloseDoc()
    End Sub

    '~~> Invoking the print dialog
    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
        AxEDOffice1.PrintDialog()
    End Sub

    '~~> Quit
    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Me.Close()
    End Sub

End Class
Here is an explanation of the commands used above.
AxEDOffice1.CreateNew("Excel.Application")
The above creates a blank new workbook for you
AxEDOffice1.OpenFileDialog()

The above presents am open file dialog so that you can choose your file. By default it shows you all Office extensions but if you want only Excel files then you can specify the respective filters as shown in the main code above. From what I tested, unfortunately it doesn’t support wildcards in the OpenFileDialog(). For example this works in VBA Excel.

Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
But the same filters in Edraw OVC will not give you error when you use it but will give you unexpected results in the DialogBox. So my suggestion is to specify the relevant extensions like I have mentioned above.
AxEDOffice1.Save()
The above code simply saves the file that you opened. To do a SaveAs use the code below.
AxEDOffice1.SaveFileDialog()
If you have a predefined path then you can also use
AxEDOffice1.SaveAs("C:\Sample.xlsx")
The best part is that the file that you load are “Read Only” i.e till the time you don’t specify a save command, the opened file is not saved. if the user tries to click on the “Save” button in Excel then Excel will inform the user that the file is in Read Only mode and to save it you need to do a “Save As”.
By default the Edraw OVC Loads the Excel toolbars as well. For example
image
If you want you can disable by simply switching it off in the Form Load Event
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    AxEDOffice1.Toolbars = False
End Sub

image

When the toolbar is visible, you have access to all the toolbar button. But if the Toolbar’s visibility is set to OFF and you want to give the user the ability to print then you can use the below command. BTW the user can still use the Excel’s Shortcuts. So pressing a CTRL P will invoke the Print button.
AxEDOffice1.PrintDialog()
You can also do a Print Preview using the below code
AxEDOffice1.PrintPreview()
To close the file without saving, simply use
AxEDOffice1.CloseDoc()
Now let’s go one step forward. Let’s try and work with Excel Ranges in the EOV Component. We will use the same sample file and create a small Report from it.
Add a new button on the form and Call it “Report”
image
In the Report button click event, paste this code. I have commented the code so you shouldn’t have any problem understanding it. What we will try and do is format our range and create a chart from it.
'~~> Create A Report
Private Sub Button8_Click(sender As System.Object, e As System.EventArgs) Handles Button8.Click
    Dim oxlAp = AxEDOffice1.GetApplication()
    Dim oWbk As Excel.Workbook = AxEDOffice1.ActiveDocument()
    Dim oWs As Excel.Worksheet = oWbk.Sheets("Sheet1")

    With oWs
        '~~> Change the range into a tabular format
        .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("A1:E6"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

        '~~> Format the table
        .ListObjects("Table1").TableStyle = "TableStyleLight8"

        '~~> Format the Total and Average Expenses cells
        With .Range("A1:A6")
            .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
            With .Font
                .ColorIndex = 2 '<~~ Font Color White
                .Size = 8
                .Name = "Tahoma"
                .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                .Bold = True
            End With
        End With

        '~~> Autofitting text in columns
        .Columns("A:E").EntireColumn.AutoFit()

        '~~> Inserting a Graph
        .Shapes.AddChart.Select()
        oxlAp.ActiveChart.ApplyCustomType(Excel.XlChartType.xlLineMarkers)
        oxlAp.ActiveChart.SetSourceData(Source:=.Range("Sheet1!$A$1:$E$6"))
    End With
End Sub
Now load the sample file using the load button. You Form should look like this.
image
When you click on the Report button, you will notice that you data range has been formatted nicely and a chart has also been created.
image
To know more about Excel – VB.Net interaction, you can view this link. In fact you can try the example given there on the Edraw Office Viewer Component as well.

Excel Text To Columns From VB.net


EDIT

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

http://www.siddharthrout.com/2012/06/29/excel-text-to-columns-from-vb-net/

 

There are many occasions where we need to open a text file in Excel and then arrange them in separate columns. If we directly open the file in Excel then we notice that everything is filled in Col A. In such a scenario what  can we do?

Excel has an inbuilt feature which is called Text To Columns which parses a column of cells that contain text into several columns. In Excel 2010 you can find that feature in the Data Tab.

image

So how do we call this functionality from VB.net? Let’s take an example. Copy the below text and paste it in a Notepad and save it as “C:\Sample.Txt”

Name, Age, Sex
Frank, 21, M
Matha, 22, F
Jack, 23, M
William, 35, M

image

Now if we open the file directly in Excel this is what we get.

image

Now let’s try and automate the entire process in VB.Net so that the data is distributed in separate columns.

Create a form and put a button on the form. Set the reference to Microsoft Excel Library. If you do not know how to do that then see this.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) _
    Handles Button1.Click
        '~~> Open the File
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.txt")

        '~~> Set reference to the 1st Sheet
        xlWorkSheet = xlWorkBook.Sheets(1)

        '~~> Display Excel
        xlApp.Visible = True

        With xlWorkSheet
            .Columns(1).TextToColumns( _
            Destination:=.Cells(1, 1), _
            DataType:=Excel.XlTextParsingType.xlDelimited, _
            TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            TAB:=False, _
            Semicolon:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, _
            TrailingMinusNumbers:=False)
        End With
    End Sub
End Class
When you run the code this is the output that you get

image

If you are looking for a VBA version then you might want to look at this MSDN Site.

HTH

VB.Net/VBA Copy Rows From Multiple Tabs Into One Sheet in Excel


EDIT

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

http://www.siddharthrout.com/2012/06/05/vb-netvba-copy-rows-from-multiple-tabs-into-one-sheet-in-excel/

 

While answering questions in many forums, I very frequently come across questions where the user wants to consolidate rows from all sheets in one sheet. So I finally decided to write a piece of code that I can link to.

You will find below the code for both VB.Net and VBA.


VB.Net (Tested in VS2010/2012 + Office 2010)


Create a form and put a button on the form. Set the reference to Microsoft Excel Library. If you do not know how to do that then see this.

Paste the code and change the file name and output sheet name as required.

The function lets you specify several things like

  • Worksheet for Output
  • Start Row in Output Sheet from where the data needs to be pasted
  • Start Row in Rest of sheets from where the data needs to be copied from
  • Check for Sheet visibility?
  • Paste as Values?
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' This macro copies data from all sheets into a master sheet '
' '
' Created By Siddharth Rout '
' URL - https://siddharthrout.wordpress.com/2012/06/05/vb-netvba-copy-rows-from-multiple-tabs-into-one-sheet-in-excel/ '
' Date: 05/06/2012 '
' '
' Note: This code can be freely used. However would request '
' that you do not delete these comments '
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim wsO As Excel.Worksheet
    Dim wsISr As Integer, wsILr As Integer, wsOlr As Integer

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Open the relevant Workbook
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsm")

        '~~> Set your worksheet here where the output will be generated
        wsO = xlWorkBook.Sheets("Master")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> IMPORTNAT NOTE: USE ANY ONE OF THE BELOW

        ' This will consolidate rows from all VISIBLE sheets taking '
        ' data from row 5 and output it in Sheets("Master") from row 1 '
        ' onwards. If any cell has formulas then they will be pasted '
        ' as VALUES. '
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
        'MergeSheets(wsO, 1, 5, True, True)

        ' This will consolidate rows from ALL sheets taking data from '
        ' row 1 and output it in Sheets("Master") from row 1 onwards. '
        ' If any cell has formulas then they will NOT be pasted as '
        ' values. '
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
        MergeSheets(wsO)
        Me.Close()
    End Sub

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' '
    ' Syntax:- '
    ' wsOutput | Required |: Worksheet for Output '
    ' startRowOutput | Optional |: Start Row in wsOutput '
    ' startRowInput | Optional |: Start Row in Rest of sheets '
    ' chkVisible | Optional |: Check for visiblity? '
    ' pasteVal | Optional |: Paste as Values '
    ' '
    ' USAGE:- '
    ' MergeSheets Sheets("Sheet1"), 10, 5, True, True '
    ' This will consolidate rows from all visible sheets taking '
    ' data from row 5 and output it in Sheets("Sheet1") from row 10 '
    ' onwards. If any cell has formulas then they will be pasted '
    ' as values. '
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    Private Sub MergeSheets(wsOutput As Excel.Worksheet, _
    Optional startRowOutput As Integer = 1, _
    Optional startRowInput As Integer = 1, _
    Optional chkVisible As Boolean = False, _
    Optional pasteVal As Boolean = False)

        Dim ws As Excel.Worksheet
        wsOlr = startRowOutput
        wsISr = startRowInput
        'If startRowOutput = 0 Then wsOlr = 1 Else wsOlr = startRowOutput
        'If startRowInput = 0 Then wsISr = 1 Else wsISr = startRowInput

        '~~> Loop through all sheets
        For Each ws In xlWorkBook.Sheets
            '~~> Ignore the output sheet
            If ws.Name <> wsOutput.Name Then
                '~~> Visibility Check If Required
                If chkVisible = True And ws.Visible <> Excel.XlSheetVisibility.xlSheetVisible Then GoTo NextSheet

                '~~> Get Last Row of the input sheet
                wsILr = GetLastRow(ws)

                '~~> Check if the last row is greater than [startRowInput]
                '~~> We also check if the sheet is not empty
                If Not wsILr < wsISr Or Not wsILr = 0 Then _
                ws.Rows(wsISr & ":" & wsILr).Copy( _
                wsOutput.Rows(wsOlr))

                '~~> Get the next available row in the output sheet
                wsOlr = GetLastRow(wsOutput) + 1
            End If
NextSheet:
        Next
    End Sub

    '~~> Function to get the last row in the sheet
    Private Function GetLastRow(ByVal wks As Excel.Worksheet) As Long
        GetLastRow = 0
        If xlApp.WorksheetFunction.CountA(wks.Cells) <> 0 Then
            GetLastRow = wks.Cells.Find(What:="*", _
            After:=wks.Range("A1"), _
            LookAt:=Excel.XlLookAt.xlPart, _
            LookIn:=Excel.XlFindLookIn.xlFormulas, _
            SearchOrder:=Excel.XlSearchOrder.xlByRows, _
            SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
            MatchCase:=False).Row
        End If
        Return GetLastRow
    End Function
End Class

VBA (Tested in Office 2010)


Paste this code in a module.

The function lets you specify several things like

  • Worksheet for Output
  • Start Row in Output Sheet from where the data needs to be pasted
  • Start Row in Rest of sheets from where the data needs to be copied from
  • Check for Sheet visibility?
  • Paste as Values?
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' This macro copies data from all sheets into a master sheet '
' '
' Created By Siddharth Rout '
' URL - https://siddharthrout.wordpress.com/2012/06/05/vb-netvba-copy-rows-from-multiple-tabs-into-one-sheet-in-excel/ '
' Date: 05/06/2012 '
' '
' Note: This code can be freely used. However would request '
' that you do not delete these comments '
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

Option Explicit

Dim wsO As Worksheet
Dim wsISr As Long, wsILr As Long, wsOlr As Long

Sub Sample()
    Dim calc As Long

    On Error GoTo Whoa

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        calc = .Calculation
        .Calculation = xlCalculationManual
    End With

    '~~> Set your worksheet here where the output will be generated
    Set wsO = Sheets("Master")

    '~~> IMPORTNAT NOTE: USE ANY ONE OF THE BELOW

    ' This will consolidate rows from all VISIBLE sheets taking '
    ' data from row 5 and output it in Sheets("Master") from row 1 '
    ' onwards. If any cell has formulas then they will be pasted '
    ' as VALUES. '
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    MergeSheets wsO, 1, 5, True, True

    ' This will consolidate rows from ALL sheets taking data from '
    ' row 1 and output it in Sheets("Master") from row 1 onwards. '
    ' If any cell has formulas then they will NOT be pasted as '
    ' values. '
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    MergeSheets wsO

LetsContinue:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = calc
    End With
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' Syntax:- '
' wsOutput | Required |: Worksheet for Output '
' startRowOutput | Optional |: Start Row in wsOutput '
' startRowInput | Optional |: Start Row in Rest of sheets '
' chkVisible | Optional |: Check for visibility? '
' pasteVal | Optional |: Paste as Values '
' '
' USAGE:- '
' MergeSheets Sheets("Sheet1"), 10, 5, True, True '
' This will consolidate rows from all visible sheets taking '
' data from row 5 and output it in Sheets("Sheet1") from row 10 '
' onwards. If any cell has formulas then they will be pasted '
' as values. '
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
Private Sub MergeSheets(wsOutput As Worksheet, _
    Optional startRowOutput As Long, _
    Optional startRowInput As Long, _
    Optional chkVisible As Boolean, _
    Optional pasteVal As Boolean)

    Dim ws As Worksheet

    If startRowOutput = 0 Then wsOlr = 1 Else wsOlr = startRowOutput
    If startRowInput = 0 Then wsISr = 1 Else wsISr = startRowInput

    '~~> Loop through all sheets
    For Each ws In ThisWorkbook.Sheets
        '~~> Ignore the output sheet
        If ws.Name <> wsOutput.Name Then
            '~~> Visibility Check If Required
            If chkVisible = True And ws.visiBle <> xlSheetVisible Then GoTo NextSheet

            '~~> Get Last Row of the input sheet
            wsILr = GetLastRow(ws)

            '~~> Check if the last row is greater than [startRowInput]
            '~~> We also check if the sheet is not empty
            If Not wsILr < wsISr Or Not wsILr = 0 Then _
            ws.Rows(wsISr & ":" & wsILr).Copy _
            wsOutput.Rows(wsOlr)

            '~~> Get the next available row in the output sheet
            wsOlr = GetLastRow(wsOutput) + 1
        End If
NextSheet:
    Next
End Sub

'~~> Function to get the last row in the sheet
Private Function GetLastRow(ByVal wks As Worksheet) As Long
    If Application.WorksheetFunction.CountA(wks.Cells) <> 0 Then
        GetLastRow = wks.Cells.Find(What:="*", _
        After:=wks.Range("A1"), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
    End If
End Function

VB.Net to retrieve the names and arguments of all Excel formulas


EDIT

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

http://www.siddharthrout.com/2012/05/02/vb-net-to-retrieve-the-names-and-arguments-of-all-excel-formulas/

 

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

http://office.microsoft.com/client/helppreview.aspx?AssetId=HV805551279990&lcid=1033&NS=EXCEL.DEV&Version=12&pid=CH080555125

To start with, create a form and place 2 command buttons and a textbox as shown below

image

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.

image

HTH Smile

VB.Net – Interacting with Comments in Excel Cells


EDIT

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

http://www.siddharthrout.com/2012/04/28/vb-net-interacting-with-comments-in-excel-cells/

 

In this post we will cover the following

  • Inserting Comment
  • Editing Comment
  • Deleting Comment
  • Show/Hide Comment
  • Formatting Comment
  • Inserting Images in Comment
  • Inserting Charts in Comment

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.

image

The below code will insert “Hello! This is a Sample Comment” in Cell D10

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim xlRng As Excel.Range

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Set reference to Sheet1
        xlWorkSheet = xlWorkBook.Sheets(“Sheet1″)

        '~~> Set reference to the range that we would be working with
        xlRng = xlWorkSheet.Range(“D10″)

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Here we set what needs to go into comments
        Dim msgComment As String = "Hello! This is a Sample Comment"

        xlRng.AddComment(msgComment)

        '~~> Save the file
        xlWorkBook.SaveAs(Filename:="C:\SampleNew.xlsx", FileFormat:=51)

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

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

SNAPSHOT

image


EDITING COMMENT


Now let’s try and edit the comment that we inserted earlier. The below code will open the file that we created above and then edit the comments in Cell D10. Insert another button on the form (Name it “Edit Comment”).
image
Place this code in the button click event.
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    '~~> Open the existing Workbook
    xlWorkBook = xlApp.Workbooks.Open("C:\SampleNew.xlsx")

    '~~> Set reference to Sheet1
    xlWorkSheet = xlWorkBook.Sheets(“Sheet1″)

    '~~> Set reference to the range that we would be working with
    xlRng = xlWorkSheet.Range(“D10″)

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Here we set what needs to be added to the existing comment.
    Dim msgComment As String = vbNewLine & vbNewLine & "We have Just added new line to Existing Text"

    '~~> Add the new text to the existing comment
    xlRng.Comment.Text(Text:=xlRng.Comment.Text & msgComment)

    '~~> Save the File
    xlWorkBook.Save()

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

    '~~> Quit the Excel Application
    xlApp.Quit()

    '~~> Clean Up
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
End Sub
SNAPSHOT
image

DELETING THE COMMENT


Insert a 3rd button in the application and name it say, “Delete Comment”

image

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.

Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
    '~~> Open the existing Workbook
    xlWorkBook = xlApp.Workbooks.Open("C:\SampleNew.xlsx")

    '~~> Set reference to Sheet1
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    '~~> Set reference to the range that we would be working with
    xlRng = xlWorkSheet.Range("D10")

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Add the new text to the existing comment
    xlRng.Comment.Delete()

    '~~> Save the File
    xlWorkBook.Save()

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

    '~~> Quit the Excel Application
    xlApp.Quit()

    '~~> Clean Up
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
End Sub

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

xlRng.Comment.Visible = True

image

And to hide it

xlRng.Comment.Visible = False

image


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 = 150

Increasing/Decreasing the Width of the Comment

'~~> Set the relevant Width
xlRng.Comment.Shape.Width = 150

Changing the background color of the Comment

Dim Shp As Excel.Shape = xlRng.Comment.Shape
'~~> This will color the comment background to red
Shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
Changing the font name of the Comment
Dim Shp As Excel.Shape = xlRng.Comment.Shape
'~~> This changes the font to "Time new Roman"
Shp.TextFrame.Characters.Font.Name = "Times New Roman"
Changing the font size of the Comment
Dim Shp As Excel.Shape = xlRng.Comment.Shape
'~~> This will change the font size of the comment to 12
Shp.TextFrame.Characters.Font.Size = 12
Changing the font color of the comment
Dim Shp As Excel.Shape = xlRng.Comment.Shape
'~~> This will change the font size of the comment to Red
Shp.TextFrame.Characters.Font.ColorIndex = 3
Changing the margins inside the comment
Margins are set or returned in points. Use the InchesToPoints method or the CentimetersToPoints method to convert measurements from inches or centimeters.
Dim Shp As Excel.Shape = xlRng.Comment.Shape
With Shp.TextFrame
    '~~> Set the Automargins to false to work with margins
    .AutoMargins = False
    '~~> Set the margins to 0.5 inch (36 points)
    .MarginBottom = xlApp.InchesToPoints(0.5)
    .MarginLeft = xlApp.InchesToPoints(0.5)
    .MarginRight = xlApp.InchesToPoints(0.5)
    .MarginTop = xlApp.InchesToPoints(0.5)
End With
Working with the Borders of the comment
Dim Shp As Excel.Shape = xlRng.Comment.Shape
With Shp.Line
    '~~> Set border color to red
    .ForeColor.RGB = RGB(255, 0, 0)

    '~~>Sets border weight to 8 points
    .Weight = 8

    '~~> Set Style to Dash Dot Dot (check intellisense for more styles
    .DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineDashDotDot
End With

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

Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
     '~~> Add a New Workbook
     xlWorkBook = xlApp.Workbooks.Add

     '~~> Set reference to Sheet1
     xlWorkSheet = xlWorkBook.Sheets(“Sheet1″)

     '~~> Set reference to the range that we would be working with
     xlRng = xlWorkSheet.Range(“D10″)

     '~~> Display Excel
     xlApp.Visible = True

     '~~> Here we set what needs to go into comments
     Dim picComment As String = "C:\Users\Public\Pictures\Sample Pictures\Chrysanthemum.jpg"
     Dim msgComment As String = "Chrysanthemum:"

     '~~> Add the comment
     xlRng.AddComment(msgComment)

     Dim shp As Excel.Shape = xlRng.Comment.Shape

     '~~> Insert the picture
     shp.Fill.UserPicture(picComment)

     '~~> Save the file
     xlWorkBook.SaveAs(Filename:="C:\SampleNew.xlsx", FileFormat:=51)

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

     '~~> Quit the Excel Application
     xlApp.Quit()

     '~~> Clean Up
     releaseObject(xlApp)
     releaseObject(xlWorkBook)
 End Sub

image


INSERTING A CHART IN THE COMMENT


Let’s say we have a file which has a chart and it looks like this

image

And let’s say we want to show this chart in a comment in cell J10. Use this code in a button click.

Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
    '~~> Get user’s temp directory
    Dim TmpDir = Environment.GetEnvironmentVariable("TEMP")

    '~~> Open the existing Workbook
    xlWorkBook = xlApp.Workbooks.Open("C:\SampleNew.xlsx")

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Set reference to Sheet1
    xlWorkSheet = xlWorkBook.Sheets(“Sheet1″)

    '~~> Set reference to the range that we would be working with
    xlRng = xlWorkSheet.Range(“J10″)

    '~~> Set reference to the chart we will be working with
    Dim xlChartObj As Excel.ChartObject = xlWorkSheet.ChartObjects(1)
    Dim xlChart As Excel.Chart = xlChartObj.Chart

    '~~> Decide on the temp file name
    Dim strTempFileName As String = TmpDir & "\" & xlChartObj.Name

    '~~> Export the chart as jpg to the temp folder
    xlChart.Export(strTempFileName & ".JPG", "JPG", False)

    '~~> Here we set what needs to go into comments
    Dim msgComment As String = xlChartObj.Name & ":"

    '~~> Add the comment
    With xlRng
        .AddComment(msgComment)
        '~~> Uncomment the below part if you don’t want the comment to autosize as per chart size
        With .Comment.Shape
            .Height = xlChartObj.Height
            .Width = xlChartObj.Width
        End With
    End With

    Dim shp As Excel.Shape = xlRng.Comment.Shape
    '~~> Insert the picture
    shp.Fill.UserPicture(strTempFileName & ".JPG")

    '~~> Kill the temp file
    Kill(strTempFileName & ".JPG")

    '~~> Save the file
    xlWorkBook.SaveAs(Filename:="C:\SampleNew.xlsx", FileFormat:=51)

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

    '~~> Quit the Excel Application
    xlApp.Quit()

    '~~> Clean Up
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
End Sub

image


Hope this helps Smile

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

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/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.