Category Archives: VBA

Update null value in Microsoft Access Database


The regular SQL is null update query  may fail while updating Access database. Access DB has some different is null checking system. Put the Is Null statement at the end of the where section may fix the problem.

Here is the query

UPDATE Sales SET Sales.Taxable = 0
 WHERE Sales.Taxable Is Null

Saving values to registry in VB6


Visual Basic simplyfies the way you can store and retrieve values. Usually you can place value in configuration files with easy code. If you want to go more advanced , can keep them at Windows Registry, were the OS keep track of its ihabitants.

Visual Basic 6 allows you store and retrieve values by using SaveSetting and GetSettings methods

Call SaveSetting("MyApp", "Software", "Key", "12312AZ90")
Print GetSetting("MyApp", "Software", "Key")

YouTube Video

Exporting DB tables to Excel in VB6


It is something like complicated when you want to export reports from reporting objects like List view in VB6. In fact Excel object has provided a way to directly export contents of a table into Excel format in a quick manner using DAO or ADO connection.

To illustrate this we need,

  1. A DAO, ADO database connection
  2. Excel Objects
  3. Loop to print column heading

Connection

You may be familiar with the database connections in VB6, the following is an example of ADO data connection.

Dim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordset

adodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\emp.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Accounts “, adodb_db, adOpenKeyset

The excel objects

The excel objects are required to create a new Excel file in order to export the rows of a table.

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

The columns

The CopyFromRecordset of Excel object didn’t fetch columns, so we do it manually as follows.

If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst

‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next c

End if

Copying the rows to Excel

The Excel Worksheet object’s CopyFromRecordset help you to copy records to excel rows, all you need to specify the cell range.

oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rs

The complete code

Here is the complete code

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Dim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordset

adodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\SherPharma\SIGMA1819.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Account_Transactions “, adodb_db, adOpenKeyset

‘Set rs = MedicalShop.OpenRecordset(“select entryno,itemname,total from Sales_Particulars “)
If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst

‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next c
oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rs

oExcel.Visible = vbTrue

End If

 

Create a Auto generating Excel expense sheet with VBA


Microsoft Excel becomes one of my favourite application since I marry Excel macros, it help me lot to minimize tedious jobs. Here I will explain how you can perform an advanced Macro operation with the help of VB coding operations in Microsoft Excel 2013/2010/2007.

Making of Expense Sheet

 Firstly design your expense sheet as you like and Name the worksheet says ‘XP monthly’ which is short for Expense Monthly. It will be the base sheet which is working as source for additional sheet.

Sheet Renaming and checking for duplicate sheet

This task will require VB coding, just hit Alt+F11 to activate VB Editor and drop the following codes in Sheet1(XP Monthly).
Private Sub Worksheet_Activate()
Dim j, str, temp
If Name “xps” Or Name “XP Monthly” Or Left(Name, 3) = “XP-” Then

‘ FIND THE SPACE IN IN SHEET NAME FOR TITLE
For j = 4 To Len(Name)
 temp = Mid(Name, j, 1)
 If temp ” ” Then
   str = str & temp
 Else
  Exit For
 End If
Next
Range(“A7”) = str ‘ Name of the Month
‘………………………………………………………………………
End If
‘If (Left(Name, 3) = “xps” Or Name = “Monthly”) Then
Dim flag
flag = 0
‘ FIND THE PERIODS IN SHEET NAME
For j = Len(Name) To 1 Step -1
 temp = Mid(Name, j, 1)
If temp = “.” Then
 flag = 1
 Exit For
End If
Next
‘………………………………………………………………………
If (flag > 0) Then MsgBox “Naming is possible”
Dim nameStr As String
nameStr = “XP-” & MonthName(Month(Date)) & ” ” & Year(Date)
Dim i
‘ CHECK WHETHER THE SHEET NAME ALREADY TAKEN
Dim h, yy
For h = 1 To Worksheets.Count
 If Sheets.Item(h).Name = nameStr Then
  foo = True
  MsgBox “Sorry the XPs Sheet already exist for this month,Rename the sheet manually”, vbCritical, “Naming Error”
  Exit For
 End If
Next
If foo = False Then
 i = MsgBox(“Do you want to alter the worksheet name as ” & “‘” & nameStr & “‘” & “?”, vbYesNo, “Naming offer”)
 If i = vbYes Then Name = nameStr
End If
End If
End Sub

For avoiding the accidental renaming we add some numbers (NewXPS)with sheet name when it is duplicating.Above action will be perform when user switch between sheets during which Activate event of Sheet is fired.

Copying the Base sheet

Now we are ready create new Macro for copying the Base sheet for current month.Press Alt+F8 and enter macro name XPSheet and hit Create button and drop the following Macro code.

Sub NewXPS()
‘ NewXPS Macro
Sheets(“XP Monthly”).Select
Sheets(“XP Monthly”).Copy After:=Sheets(1)
ActiveSheet.Name = “XP-” & MonthName(Month(Date)) & ” ” & Year(Date) & ” ” & Rnd((1))
End Sub

The Rand() will generate random number file name, which used to avoid duplicating sheets. The sheet will be renamed later as current month, if there is no duplicate sheet otherwise you have rename it manually.

How to use this Macro enabled sheet

Whenever you need a new Expense sheet just run the Macro and switch between sheets once, it will rename automatically. VB Macro programming offer flexibility and ease of peace, working with MS Excel.

You can download and use complete Work Book from our Doc storage.

Clear and Format all textboxes at runtime in Visual Basic


All we know Visual Basic 6 is the simplest ways to develop Windows application for desktop computer. In this special article we are going to learn a code poem which automatically clear the text box content with few line of code. The idea sound good, isn’t it?

Firstly lets make a Sub to clear the fields. I have two method the first one let you clear selected text box which utilize the paramArray argument and the second will clear the entire field in a form. 
Open your Visual Basic 6.0 and enter the following sub routine.

Sub ClearBox for selected boxes

Sub ClearBox (ParamArray boxes() As Variant)
Dim ct As Variant
For Each ct In boxes
 If TypeOf ct Is TextBox Then
    ct.Text = “”
 End If
Next
End Sub

This sub will clear the passed text box controls  on the form,  The sub utilizes the paramArray feature, so that the program can accept any number of arguments/controls. Remember that paramArray object should be type of variant.

Clear all the boxes

The second method will clear all the boxes on the form. Lets take a look at the second code. 

Sub ClearBox_Auto(f As  Form)
 Dim ob As Object
 Dim ct As Control

 For Each ob In Form1.Controls
  Set ct = ob
  If TypeOf ct Is TextBox Then
    ct.Text = “”
  End If
 Next
 End Sub

Form’s controls property is returning the object collection and we collect each of then to ‘ob’ and then assign to a control variable and then can do the similar type of operations done in Clear Box. sub

Auto Number Format ‘Sub’

Finally I have a special format box which  utilise Forms controls collection to gather controls appeared in the User Interface and  apply specific number format to all the number field as follows.

Sub AutoNumberFormat(f As  Form)
 Dim ob As Object
 Dim ct As Control

 For Each ob In Form1.Controls
  Set ct = ob
  If TypeOf ct Is TextBox Then
     If IsNumeric(ct.Text) Then
        ct.Text = Format(ct, “0.00”)
     End If
  End If
 Next

End Sub


Call the Box function

Let invoke the subroutines on the click event of command button 
‘Clear selected boxes
 Call ClearBox(TextBox1, TextBox2, TextBox3)
‘Clear All the boxes
Call ClearBox_Auto(Me)

‘Format numbered fields
Call AutoNumberFormat(Me)

Note: 

The code is working on Microsoft Word VBA and Visual Studio 6.0   alike. For using on Word VBA please replace the Form (in argument) with UserForm

 Join us on Disqus.