IMPORTANT NOTICE

Leave a Comment Posted by on September 12, 2012

After blogging for almost one and half years in wordpress, I finally have my own website :)

If you have been following this blog then I would request you to update your bookmarks. I will be deactivating this blog w.e.f 1st Oct 2012

My Website: http://www.siddharthrout.com/

Cheers

Information

Vb.Net Two Dot Rule when working with Office Applications

1 Comment Posted by on August 6, 2012

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.
Excel, Vb.Net , , , , , ,

Biggest multipurpose FREE Excel Add-in

Leave a Comment Posted by on July 29, 2012

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

Excel, Vb.Net, VSTO ,

Embed Excel Documents in VB.Net Application

12 Comments Posted by on July 2, 2012

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, Vb.Net

Microsoft MVP : Re-Awarded

6 Comments Posted by on July 1, 2012

 

EDIT

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

http://www.siddharthrout.com/2012/07/01/microsoft-mvp-re-awarded/

 

image

Today when I woke up, I was greeted with an unexpected email. I say unexpected because I was expecting the email on the 2nd of July (1st being a Sunday).

Dear Siddharth Rout,
Congratulations! We are pleased to present you with the 2012 Microsoft® MVP Award! ……

I am sure you can imagine how happy I was! This is the second time in succession I’ve been given this award.

I would like to convey my heartiest thanks to the following

1. Microsoft for recognizing and appreciating my contributions

2. Tanmay Kapoor (my MVP Lead)

3. The Visual Basic Team

4. Anyone else who has contributed in getting me this MVP awarded again.

I hope my contribution to the community not only continues but also exceeds last years benchmarks Smile

Information , , ,

Excel Text To Columns From VB.net

1 Comment Posted by on June 29, 2012

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

Excel, Vb.Net , , , ,

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

2 Comments Posted by on June 5, 2012

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 - http://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 - http://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
Excel, Vb.Net , ,

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

Leave a Comment Posted by on May 2, 2012

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

Excel, Vb.Net , ,

VB.Net – Interacting with Comments in Excel Cells

4 Comments Posted by on April 28, 2012

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

Vb.Net , , , , , ,

Scrolling Excel chart in Powerpoint

Leave a Comment Posted by on April 21, 2012

EDIT

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

http://www.siddharthrout.com/2012/04/21/scrolling-excel-chart-in-powerpoint/

 

While answering a question in stackoverflow.com, I came across an interesting question. How do we create a scrollable Excel chart in PowerPoint. In Excel it is very easy to make a scrollable chart using ScrollBar – Form Control. But how do we do it in PowerPoint?

I will cover this in 3 parts

1) Creating the Excel File

2) Setting up your presentation

3) The code

CREATING THE EXCEL FILE

Open a new Excel File and feed in some sample data as shown in the screen shot. I have filled 200 rows with sample data. Once the Data ready, create a line chart. Your Excel File should look like this.

image

Save the file to say, C: or any other location of your choice.

SETTING UP YOUR PRESENTATION

Open MS PowerPoint and go to slide 1. Click on the tab INSERT | OBJECT

image

You will be presented with Insert Object Dialog Box. Select the “Create From File” Option and then click on the “Browse” button. Select the Excel file that we had created earlier and select an icon by clicking “Display as Icon” and click on OK.

image

Your presentation will now look like this

image

Next navigate to the DEVELOPER Tab. (See Snapshot) In the developer tab, click on additional controls button and select “Microsoft Web Browser” and insert that control in your respective slide. Ensure that it hides the Excel Object that we inserted earlier. Size it accordingly. Also place a Command Button. Name it “Show Chart” or anything else what you feel is right.

image

And you are done with setting up your presentation

THE CODE

Press ALT + F11 or Click on tab DEVELOPER | VISUAL BASIC on the right hand side to open the visual basic editor.

image

Paste the code given below in the Slide1 code area.

image

Option Explicit

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

Dim ImageFile As String

Private Sub CommandButton1_Click()
    ExtractToTemp
    WebBrowser1.Navigate ImageFile
End Sub

Sub ExtractToTemp()
    Dim oSl As PowerPoint.Slide
    Dim oSh As PowerPoint.Shape

    Dim oXLApp As Object, oXLWB As Object, oXLSht As Object
    Dim mychart As Object

    Set oSl = ActivePresentation.Slides(1)

    Set oSh = oSl.Shapes(1)

    With oSh.OLEFormat.Object.Sheets(1)
        .Shapes(1).Copy
    End With

    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")

    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0

    oXLApp.Visible = False

    '~~> Open the relevant file
    Set oXLWB = oXLApp.Workbooks.Add
    Set oXLSht = oXLWB.Worksheets(1)

    oXLSht.Paste

    '~~> Save Picture Object
    ImageFile = TempPath & "Tester.jpg"

    If Len(Dir(ImageFile)) > 0 Then Kill ImageFile

    Set mychart = oXLSht.ChartObjects(1).Chart
    mychart.Export FileName:=ImageFile, FilterName:="jpg"

    '~~> Wait till the file is saved
    Do
        If FileExists(ImageFile) = True Then Exit Do
        DoEvents
    Loop

    oXLWB.Close SaveChanges:=False
    oXLApp.Quit
    Set oXLWb = Nothing
    Set oXLApp = Nothing 
End Sub

'~~> Get User's TempPath
Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function

'~~> Function tot check if file exists
Public Function FileExists(strFullPath As String) As Boolean
    On Error GoTo Whoa
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileExists = True
Whoa:
    On Error GoTo 0
End Function

And you are done. Smile

Run the presentation by pressing F5 and click on the “Show Chart Button”. You screen show now look like this.

image

If you have made this presentation then this presentation can also be distributed easily.

Hope this helps…

Excel, General VBA, PowerPoint , , ,

← Older posts

Follow

Get every new post delivered to your Inbox.