Category Archives: .NET

modProgBar

modProgBar

Option Explicit On

Imports System.Windows.Forms

Module modProgBar

Public theProgBar As ProgressBar
Public theProgBarTitle As Label
Public theProgBarTitleMsg As String
Public theCallBtn As Button
Public theCallBtn2 As Button

Public Sub InitProgressBar(ByVal progMsg As String)
theProgBar.Minimum = 0
theProgBar.Maximum = 100
theProgBarTitleMsg = progMsg
theProgBarTitle.Text = progMsg & ” …”
theProgBar.Enabled = True
theProgBarTitle.Enabled = True
theCallBtn.Enabled = False
theCallBtn2.Enabled = False
End Sub

Public Sub StepProgressBar(ByVal thisNum As Long, ByVal totalNum As Long)
Dim currentPercent As Integer
‘currentPercent = Int((thisNum / totalNum) * 100)
currentPercent = modCommon.GetCeiling((thisNum / totalNum) * 100)
theProgBar.Value = currentPercent
theProgBarTitle.Text = theProgBarTitleMsg & ” (” & CStr(currentPercent) & “%) …”
System.Windows.Forms.Application.DoEvents()
End Sub

Public Sub EndProgressDialog(Optional ByVal isStep As Boolean = False)
theProgBar.Value = 0
IIf(isStep, theProgBarTitle.Text = AppName & ” – Step Done!”, theProgBarTitle.Text = AppName & ” – Task Done!”)
theProgBar.Enabled = False
theProgBarTitle.Enabled = False
theCallBtn.Enabled = True
theCallBtn2.Enabled = True
End Sub

End Module

button

With F
theProgBar = .progBar : theProgBar.Enabled = False
theProgBarTitle = .labProgTitle : theProgBarTitle.Text = “” : theProgBarTitle.Enabled = False
theCallBtn = .cmdOK
theCallBtn2 = .cmdCancel

.ShowDialog()

End With

module

Dim runflag As Long
runflag = 0 : modProgBar.InitProgressBar(“Exporting : “)

Do While Blah
runflag = runflag + 1 : modProgBar.StepProgressBar(runflag, Count)
Loop

modProgBar.EndProgressDialog()

Advertisements

Check if folder exists

Dim fs
fs = CreateObject(“Scripting.FileSystemObject”)
If fs.FolderExists(Me.txtFolder.Text) Then
modExcel.Export(Me.txtFolder.Text)
End If

Start Indices of VBA Collection and ArcGIS IPointCollection

Note that VBA Collection starts with an annoying 1, while IpointCollection starts with 0. phew.

The much hated Excel Chart Object

For more details, visit here:

http://msdn.microsoft.com/en-us/library/aa213725(v=office.11).aspx

 

When you find that the Xapp.SHEET call in VB.NET hangs, this might be the problem:
What you are trying to call is a CHART OBJECT, not a SHEET, although it looks like one!
Chart objects only have a tab at the bottom of Excel, but they do not contain cells.

To activate a sheet, make this call:
XApp.Charts(Sheetnumber).Activate()

I hope this helps some people out there!

The Complete guide to calling Excel Objects in VB .NET

This guide explains how to use Excel Objects in VB .NET

1. Import Microsoft.Office.Interop

2. Call Excel inside a Try-Catch Statement

Dim XApp As Excel.Application
Dim XBooks As Excel.Workbooks
Dim XBook As Excel.Workbook

Dim XSheet As Excel.Worksheet
Dim XRange As Excel.Range
XApp = CreateObject("Excel.Application")

XApp.Visible = False 'Run Excel in the background

XBooks = XApp.Workbooks
XBook = XBooks.Open(templateXLSX)

XSheet = XApp.Application.Sheets("Raw Data")

3. Manipulate Excel Object

XRange = XApp.Range("A1")

XRange.Value2 = "NEW VALUE"

4. Save the Excel Files

XBook.Close(False) ‘The boolean argument indicates whether the file is saved before closing

5. Clean all Excel objects

Cleaning objects are required, or else the Excel object will not go away in Task Manager

     While (System.Runtime.InteropServices.Marshal.ReleaseComObject(XBook_New) <> 0)
            End While
            XBook = Nothing

            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(XBooks) <> 0)
            End While
            XBooks = Nothing
            XApp.Quit()

            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(XApp) <> 0)
            End While
            XApp = Nothing

For each object, do ReleaseComObject and then set its value to Nothing

If the spreadsheets are updated via a Loop, check carefully that every object is cleaned before it is called again.

For example:

XRange = XApp.Range(“A1″)

XRange.Value2=”NEW VALUE 1”

System.Runtime.InteropServices.Marshal.ReleaseComObject(XRange)

XRange = XApp.Range(“A2”) ‘XRange is cleaned before it is called.

 

Also, call Garbage Collector twice as follows:

       GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

6. For all language environments to run your program without MUI installed, add this to your code:

   Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
   System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

at the beginning after setting Xapp, and at the end

    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

That’s all!