Category Archives: Excel

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

Scrolling Excel chart in Powerpoint


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…

Check if an Excel File has a Macro


EDIT

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

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

 

Today while answering a question in MSDN Excel Forum, I came across an interesting question. Can we check if the Excel File has a “Macro”. When I say “Macro”, I am not referring to just any “Code”.

I started off with a small piece of code and as the discussion went on in the thread, I realized that I had not taken several scenarios into consideration. For ex. What if the user had “Require Variable Declaration” checked. My code was just counting the lines and then deciding whether there was a macro or not. I would like to thank Hans Vogelaar for suggesting different scenarios which made it possible to narrow down the code to the specifics.

So how do we check if the file has a “Macro”?

Logic: Strictly speaking Macros start with “Sub“ or “Private Sub” or “Public Sub”. So if we check for “Sub” then we can decide if the file has any macros or not.

Code:

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim HasMacro As Boolean
    Dim StrCode As String
    Dim i As Long

    '~~> Open the file to check if it has any MACRO

    Set wb = Workbooks.Open("C:\Users\Siddharth Rout\Desktop\book2.xlsm")

    Select Case UCase(Split(wb.Name, ".")(UBound(Split(wb.Name, "."))))
        '<~~ Excel files which can have a macro.
    Case "XLS", "XLSM", "XLTM", "XLT", "XLA", "XLSB", "XLAM"
        With wb.VBProject
            '~~> Components are like sheet1, thisworkbook, module etc.
            If .VBComponents.Count > 0 Then
                For i = 1 To .VBComponents.Count
                    '~~> get the entire code in the module
                    StrCode = .VBComponents.Item(i).CodeModule.Lines(1, _
                    .VBComponents.Item(i).CodeModule.CountOfLines)

                    StrCode = " " & Replace(StrCode, vbCrLf, " ") & " "

                    If InStr(StrCode, " Sub ") > 0 Then
                        HasMacro = True
                        Exit For
                    End If
                Next
            End If
        End With
    End Select

    wb.Close SaveChanges:=False

    If HasMacro Then
        MsgBox "The workbook has macro"
    Else
        MsgBox "The workbook doesn't have a macro"
    End If
End Sub

However an Userform might also have “Sub” so how do we ignore that?

All VBComponents have a type. To check that simply run this code in an Excel file which has Sheets, Thisworkbook, Module, Userform and a Class Module.

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim i As Long

    Set wb = ActiveWorkbook

    With wb.VBProject
        '~~> Components are like sheet1, thisworkbook, module etc.
        If .VBComponents.Count > 0 Then
            For i = 1 To .VBComponents.Count
                Debug.Print .VBComponents.Item(i).Name
                Debug.Print .VBComponents.Item(i).Type
            Next
        End If
    End With
End Sub
You will notice that these are the types
Name : ThisWorkbook Type : 100
Name : Sheet1       Type : 100
Name : Module1      Type : 1
Name : UserForm1    Type : 3
Name : Class1       Type : 2
So you can actually trap the “type” in the above code to ignore say Userforms
With wb.VBProject
    '~~> Components are like sheet1, thisworkbook, module etc.
    If .VBComponents.Count > 0 Then
        For i = 1 To .VBComponents.Count
            If .VBComponents.Item(i).Type = 3 Then
                '~~> Rest of your code
            End If
        Next
    End If
End With
You also use the Excel Constants instead of the above numbers for example, when you type
.VBComponents.Item(i).Type =
Intellisense automatically gives you the options. See picture.
image
Taking this post a step forward.
  • How to Check if an Excel File has any code

To check if there is any “Code” or not in an excel file you can use this code. This takes account of only “Option Explicit”. You can amend it to also take into considerations the following. One can also just check if the first word is “Option” to trap all the scenarios mentioned below.

Option Compare Binary
Option Compare Text
Option Private Module
Option Base 0
Option Base 1

Code:

Sub Sample()
    Dim wb As Workbook
    Dim Count_of_Lines As Long
    Dim StrCode As String

    Set wb = Workbooks.Open("C:\Users\Siddharth Rout\Desktop\book2.xlsm")

    Select Case UCase(Split(wb.Name, ".")(UBound(Split(wb.Name, "."))))

    '<~~ Excel files which can have a macro.
    Case "XLS", "XLSM", "XLTM", "XLT", "XLA", "XLSB", "XLAM"
       '~~> Taking this approach as there are very few extensions which support macros
    Case Else
        MsgBox "The workbook doesn't have any Code"
        wb.Close SaveChanges:=False
        Exit Sub
    End Select

    Count_of_Lines = 0

    With wb.VBProject
        '~~> Components are like sheet1, thisworkbook, module etc.
        If .VBComponents.Count > 0 Then
            For i = 1 To .VBComponents.Count
                '~~> Get the entire code in the module
                StrCode = Trim(.VBComponents.Item(i).CodeModule.Lines(1, _
                .VBComponents.Item(i).CodeModule.CountOfLines))
                If checkstatus(StrCode) = False Then
                    Count_of_Lines = Count_of_Lines + .VBComponents.Item(i).CodeModule.CountOfLines
                End If
             Next
         End If
    End With

    If Count_of_Lines > 0 Then
        MsgBox "The workbook has Code"
    Else
        MsgBox "The workbook doesn't have any Code"
    End If
End Sub

'~~> Checking if the code doesn't have just blank lines or "Option Explicit" only
Function checkstatus(strg As String) As Boolean
    Dim ar
    Dim strTemp As String

    strTemp = strg

    If InStr(1, strTemp, vbNewLine) Then
        Do While InStr(1, strTemp, vbNewLine) > 0
             strTemp = Replace(strTemp, vbNewLine, "")
        Loop
        strTemp = Trim(strTemp)
    Else
        strTemp = Trim(strg)
    End If

    If Trim(strTemp) = "Option Explicit" Or _
    Len(Trim(strTemp)) = 0 Or _
    Left(Trim(strTemp), 1) = "'" _
    Then checkstatus = True
 End Function
EDIT
This edit was required as Doug Glancy gave some nice suggestions.
To access the VBA components as shown in the code above, the user needs to have checked “Trust access to the VBA project object model” in Macro Security. To do this, follow these steps
Excel 2003: Click on menu Tools | Macro | Security to access the”Security” dialog Box. Under “Trusted Publisher” tab, check the box which says “Trust access to  the Visul Basic Project

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

Calling Excel Macros programmatically in VB.Net


EDIT

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

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

 

Let’s say we have the following Macros in Excel

Sub RunMe()
    MsgBox "Called from VB.net Client", vbInformation, "Demo to run Excel macros from VB.net"
End Sub

Sub ShowMsg(msg As String, title As String)
    MsgBox msg, vbInformation, title
End Sub

Let’s save the Excel File as Sample.xlsm to C:\. If you notice the first macro doesn’t take an argument and the second one take 2 arguments. Let’s try and call that from vb.net.

Now open the a new project in VB.net and put two command buttons on it. Your project should look like this.

image

Now add a reference to Excel Object Library. Covered here (Section: Setting up VB.Net to Work with Excel).

Once you have the references set up, use this code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Define your Excel Objects
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook

        '~~> Start Excel and open the workbook.
        xlWorkBook = xlApp.Workbooks.Open("C:\book1.xlsm")

        '~~> Run the macros.
        xlApp.Run("RunMe")

        '~~> Clean-up: Close the workbook and quit Excel.
        xlWorkBook.Close(False)

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

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

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '~~> Define your Excel Objects
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook

        '~~> Start Excel and open the workbook.
        xlWorkBook = xlApp.Workbooks.Open("C:\book1.xlsm")

        '~~> Run the macros.
        xlApp.Run("ShowMsg", "Hello from VB .NET Client", "Demo 2nd Button")

        '~~> Clean-up: Close the workbook and quit Excel.
        xlWorkBook.Close(False)

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

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

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

SNAPSHOTS

1) Button 1

image

2) Button 2

image

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

Hope this helps Smile

VBA – Data Validation and Enforcing it to work


EDIT

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

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

 

While answering a question today in StackOverflow, I came across an interesting question. And that inspired me to write a blog post on it.

PROBLEM: How to enforce Data Validation to work from VBA. By default you can set a data validation condition from VBA but it doesn’t work if you manually try to set a value to the cell. For example, this piece of code adds a DataValidation to Cell A1 in Sheet1 but doesn’t throw any error message when you supply a different value to the cell.

Sub Sample()
    With Sheets("Sheet1").Range("A1")
        .Validation.Delete
        .Validation.Add Type:=xlValidateList, Formula1:="TRUE,FALSE"
        .Value = "SID" '<~~ Trying to pass an invalid value
    End With
End Sub

SOLUTION:

The solution to the above problem is a “Sideways” enforcing of DataValidation from Worksheet_Change() event. The Worksheet_Change() event is a procedure of the Worksheet Object and as such, it should reside in the private module of the Worksheet Object. This event fires whenever there is a change in the worksheet. There are few changes which of course cannot be trapped from the Worksheet_Change() event. For example – Resizing of Columns. So back to the point. The logic that we have to use:

1) Check if the change is happening in the relevant cell. For this we can use Intersect() method.

2) And if the change is happening in the the relevant cell, then check if the cell has a DataValidation.

3) If the cell has DataValidation then check for the “Type” of DataValidation

4) Once the type has been ascertained, check if the current value of the cell conforms to the formula of the DataValidation

CODE

Const dvMessage = "Incorrect Value. Please ensure that the value conforms to the Data Validation set on the cell"

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.EnableEvents = False

        On Error Resume Next
        If Not Target.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
            Dim currentValidation As Excel.Validation
            Set currentValidation = Target.Validation

            If currentValidation.Type = xlValidateList Then
                Dim MyArray() As String
                Dim boolFound As Boolean

                MyArray = Split(currentValidation.Formula1, ",")

                For i = 0 To UBound(MyArray)
                    If UCase(Target.Value) = UCase(MyArray(i)) Then
                        boolFound = True
                        Exit For
                    End If
                Next i

                If boolFound = False Then
                    MsgBox dvMessage
                    Target.ClearContents
                End If
            End If
        End If
        On Error GoTo 0
    End If
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

With slight modification, the above code can also work very well for xlValidateWholeNumber, xlValidateCustom, xlValidateDecimal, xlValidateDate, xlValidateTime, xlValidateTextLength and xlValidateCustom

Hope this helps Smile