Tuesday, June 19, 2007

Visual Basic Common Dialog

Opening Files with Common Dialog

Common Dialog not only replaces three controls (Drive, Directory and FileList), but also is easier to program. It is supported in Visual Basic and VBA as well. The new VB.NET has the same functionality in the OpenFileDialog class

Let us have a small form created for explaining CommonDialog. Let us have a small form with a Text Box and a Command Button. On Clicking the Command Button, the selected file should be displayed in the Text Box

Sample Form:

To use the CommonDialog you need to include the component to your project. You can do so as follows:

Once The component is included, the CommonDialog will be displayed in the ToolBox

Drag the CommonDialog to the form. You will see a small rectangle there. CommonDialog is visible in the Design time only (it is not visible during runtime)

Add the following code to show the CommonDialog box and show the selected file in the text box

Private Sub Command1_Click()

CommonDialog1.DialogTitle = "Select the File..."

CommonDialog1.Flags = cdlOFNFileMustExist

CommonDialog1.Filter = "Microsoft Excel Workbooks (*.xls)*.xls"


If Len(CommonDialog1.FileName) <> 0 Then

Text1.Text = CommonDialog1.FileName

End If

You can restrict the type of files to be selected using the filter Command. Some common filters are

Selecting Microsoft Word Documents
CommonDialog1.Filter = "Microsoft Word Documents (*.doc)*.docMicrosoft Word Documents (*.rtf)*.rtf"

Selecting Image Files
CommonDialog1.Filter = "Image Files(*.BMP;*.JPG;*.GIF)*.BMP;*.JPG;*.GIF"

Selecting Microsoft Word Documents (Including RTF Files)
CommonDialog1.Filter = "Microsoft Word Documents (*.doc;*.rtf)*.doc;*.rtf"

Selecting Microsoft Excel Workbooks
CommonDialog1.Filter = "Microsoft Excel Workbooks (*.xls)*.xls"

Selecting Excel Addins
CommonDialog1.Filter = "Microsoft Excel Addins (*.xla;*.xll)*.xla;*.xll"

Selecting Any files
CommonDialog1.Filter = "All files (*.*)*.*"

Selecting Text files
CommonDialog1.Filter = "Text files (*.txt)*.txt"

Selecting ASCII files
CommonDialog1.Filter = "ASCII files (*.txt;*.log)*.txt;*.log"

See also:

OpenFileDialog in Visual Basic .Net

Search and Open Files using Excel VBA (FileSearch)

Open Excel Files - Open Dialog - GetOpenFilename Method

Selecting a Folder in VB.Net

Browse a Folder / Select a Folder Thru Shell

SaveAs Dialog - Controlled Save

Monday, June 18, 2007

Excel VBA - install an Excel Add-in (XLA or XLL)

Adding Addins Automatically using VBA

Most of today's Excel VBA code are as Addins (XLA or XLL). As an organization progresses there comes many revisions for the Addin - hence the need to update the program.

Here is a simple way to add a new addin:

Sub Add_an_Addin()

Dim oAddin As AddIn
Dim oTempBk As Workbook

Set oTempBk = Workbooks.Add

Set oAddin = AddIns.Add("E:\CostBenefit1.0.xla", True)
oAddin.Installed = True


End Sub

If you wonder why a temporary workbooks is added - it is because to avoid the Run-time error '1004': Unable to get the Add property of the AddIns class or Run-time error '1004': Add method of addins class failed exceptions that are raised when there are no workbooks. Just be safe!!

Show All Processes using VBA

Get All Processes using Win API Functions

Const TH32CS_INHERIT = &H80000000
Const MAX_PATH As Integer = 260
dwSize As Long
cntUsage As Long
th32ProcessID As Long
th32DefaultHeapID As Long
th32ModuleID As Long
cntThreads As Long
th32ParentProcessID As Long
pcPriClassBase As Long
dwFlags As Long
szExeFile As String * MAX_PATH
End Type
Private Declare Function CreateToolhelp32Snapshot Lib "kernel32" (ByVal lFlags As Long, ByVal lProcessID As Long) As Long
Private Declare Sub CloseHandle Lib "kernel32" (ByVal hPass As Long)

' API Functions to get the processes
Private Declare Function Process32First Lib "kernel32" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
Private Declare Function Process32Next Lib "kernel32" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long

Sub Load_Process_To_ListBox()

Dim hSnapShot As Long '* Handle
Dim uProcess As PROCESSENTRY32 '* Process
Dim lRet '* Return Val

On Error Resume Next

'Takes a snapshot of the running processes and the heaps, modules, and threads used by the processes
hSnapShot = CreateToolhelp32Snapshot(TH32CS_SNAPALL, 0&)

uProcess.dwSize = Len(uProcess)

'Retrieve information about the first process encountered in our system snapshot

lRet = Process32First(hSnapShot, uProcess)

Do While lRet
lRet = Process32Next(hSnapShot, uProcess)

' Trim the unwanted characters at the end of process
lstProcess.AddItem Left$(uProcess.szExeFile, IIf(InStr(1, uProcess.szExeFile, Chr$(0)) > 0, InStr(1, uProcess.szExeFile, Chr$(0)) - 1, 0))

CloseHandle hSnapShot

End Sub

Private Sub UserForm_Initialize()

' Call the Function


End Sub

Add to Technorati Favorites

Companies House

Duport provide company formation, company credit reports and director reports.

Wednesday, June 13, 2007

Run a VB6.0 Executable from Excel/Word

Run an Executable from Excel VBA / Word VBA

If you need to use some grid for showing data / use the feautres in Visual Basic 6.0 that arenot available in VBA, you can create the application in VB6.0 or anyother program and show the User Interface in VBA code

Sub Run_VB6App_FromWord()

--- Some VBA Code here

sCmd = "C:\Program Files\MyFile.exe"
vntResult = OpenProcess(PROCESS_QUERY_INFORMATION, False, Shell(sCmd, 1))
GetExitCodeProcess vntResult, lngExitCode

' -----------------------------------------------------------
' Coded by Shasur for http://vbadud.blogspot.com
' -----------------------------------------------------------

GetExitCodeProcess vntResult, lngExitCode
Loop While lngExitCode = STILL_ACTIVE

--- some more VBA Code

End Sub

The above program will show the MyFile executable till the user clicks OK/Cancel. Once the application is closed the control will return to the calling VBA program

This used WinAPI Functions

Public Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Public Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

Now it depends on how you use the external application. The most often used scenario will be to store the output from the called application (External App) to Registry or Database

VBA Read Text Files (With Leading & Trailing Spaces)

Read Data frm Text Files (VBA)

Reading Text Files using VBA is one of the major development activity of programmers. There are multiple ways to read a file

1. Input # Statement
2. Input Function
3. Get Function
4. File System Object Functions

Input # Statement

Dim MyString, MyNumber
Open "c:\test.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Input #1, MyString, MyNumber ' Read data into two variables.
Debug.Print MyString, MyNumber ' Print data to the Immediate window.
Close #1 ' Close file.

However, the bug here is Input # does not take the leading or trailing spaces with it. That is, ' My Name is ' becomes 'My Name is'. This will not be the correct one as we need to get the spaces also

Then Input function comes handy

Dim MyChar
Open "c:\test.txt" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
MyChar = Input(1, #1) ' Get one character.
Debug.Print MyChar ' Print to the Immediate window.
Close #1 ' Close file.
However, the bug here will be the input that one needs - the number of characters to be extracted.

The obvious option is File system object

Sub Read_text_File()

Dim oFSO As New FileSystemObject
Dim oFS

Set oFS = oFSO.OpenTextFile("c:\textfile.TXT")

Do Until oFS.AtEndOfStream
sText = oFS.ReadLine

End Sub

This will read line-by line. all you need to add the Microsoft Scripting Runtime in the reference

Happy reading files:)

Extract Procedure Names from all Modules - VBA

Count No of Programs in a Workbook

Workbooks have Macros, If you need to know if the Workbook contains macros and then the following proc will get you the macros in the workbook

Sub Extract_Program()

Dim VBP As VBProject
Dim VBModule As CodeModule
Dim VBProc As VBComponent
Dim sLastProcName As String
Dim arProcName() As String
Dim iProcCount As Integer

Set VBP = ThisWorkbook.VBProject

For Each VBM In VBP.VBComponents

Set VBModule = VBM.CodeModule

i = 1
Do Until i >= VBModule.CountOfLines

procname = VBModule.ProcOfLine(i, vbext_pk_Proc)
i = i + 1
If LenB(procname) <> 0 Then
If procname <> sLastProcName Then
iProcCount = iProcCount + 1
ReDim Preserve arProcName(iProcCount)
arProcName(iProcCount) = procname
sLastProcName = procname
End If
End If

' List all procedures
For i = 1 To UBound(arProcName)
MsgBox arProcName(i)
Next i
End Sub

ColorIndex - Coloring Excel Sheet Cells

Highlight Color in Excel Cells

Colorindex is used to color the background of Excel Cells

Cells(1, 2).Interior.ColorIndex = 30


Here is the entire list of colors you can use:

RSS Feeds Submission Directory

Excel VBA - Delete Empty Rows

Delete Rows without Values

Here is a primitive simple function to delete rows that does not contain any value (I have taken Cols 1 to 10) for consideration.

Sub Delete_UnWanted_Rows()

For Each SHT In Sheets
iMax = SHT.Cells.SpecialCells(xlCellTypeLastCell).Row
For i2 = 2 To iMax
For i1 = 1 To 10
If LenB(SHT.Cells(i2, i1)) <> 0 Then
GoTo TakeNextRow
End If
Next i1
Application.StatusBar = SHT.Name & " " & i2
Next i2

Next SHT
Application.StatusBar = False
End Sub

You can do the same with Special Cells - LastCell also

Friday, June 08, 2007

Setting Default & Cancel Buttons in VBA/Visual Basic

VB/ VBA Setting Default & Cancel Buttons through code

Private Sub Form_Load()

'Sets cmdOK as the button control that is clicked when the user presses the Enter key.
cmdOK.Default = True

'Sets cmdCancel as the button control that is clicked when the user presses the ESC key.
cmdCancel.Cancel = True

End Sub

For doing the same in VB.Net Refer : http://dotnetdud.blogspot.com/2007/06/vbnet-setting-default-cancel-buttons.html

Saturday, June 02, 2007

Assigning Shortcut Keys - Excel Macros

Shortcut Key Assignment for Subroutines

It is always nice to have keyboard shortcuts for executing functions/subroutines rather than having to click the menu and its command

One way will to link the macro to a command button and assign the shortcut for the button. The other one is to assign the shortcut to the function using Application.OnKey

OnKey method executes a specified procedure when a particular key or key combination is pressed

Application.OnKey "%b", "ToWord"

is used to trigger the "ToWord" subroutine whenever Alt+b is pressed . Percentage symbol is used to substitute Alt key

Use the Caret (^) to symbol for Ctrl key and plus (+) for Shiftkey

Application.OnKey "^b", "ToWord"

Is for Ctrl + b

Other keys are :

Key Code
ENTER (numeric keypad) {ENTER}
ENTER ~ (tilde)
F1 through F15 {F1} through {F15}

To deassign/release the shortcut leave the Procedure empty

Application.OnKey "%b", ""

Disable Save (Ctrl S)

Application.OnKey "^s", ""

Using Function Keys in Visual Basic Forms

Function keys as Shortcut Keys in VB
Function keys are a boon for assigning shortcuts. They have more advantage than the regular Alt + or Ctrl + combination.

Function keys can be assigned to command buttons using simple tricks as explained below. For that we need to instuct the VB to handle them in t
he Keydown event by setting the Keypreview = True

Then you can have the necessary shortcut keys on the form

In the Form_KeyDown event redirect to necessary functions/procs based on the key

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case KeyCode

Case vbKeyF2

Call Proc_Fund_Transfer

Case vbKeyF3

Call Proc_Credit_Card

End Select

End Sub

Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group