Sunday, March 15, 2009

How to Save a Chart as Image using Excel VBA

Here is the way to save the active chart in Excel 2007 to a JPG file. It is better to size the chart appropriately before exporting it as an image.

Sub Save_ChartAsImage()

Dim oCht As Chart

Set oCht = ActiveChart

On erRROR GoTo Err_Chart

oCht.Export Filename:="C:\PopularICON.jpg", Filtername:="JPG"


If Err <> 0 Then

Debug.Print Err.Description


End If

End Sub

The code uses Export method to save the chart in graphics format

How to Install Analysis ToolPak in Excel 2007

How to Install Analysis ToolPak in Excel 2007

    1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.

In the Manage list, select Excel Add-ins, and then click Go.

In the Add-ins available list, select the Analysis ToolPak box, and then click OK.

If necessary, follow the instructions in the Setup program.

The analysis pack will be loaded and displayed on the menu

Saturday, March 14, 2009

How to Connect SQL Express 2005 from VBA

Excel VBA retrieve data from SQL Server 2005

Here is a way to connect to SQL Express 2005 from Excel VBA

Sub Connect2SQLXpress()
Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oCon = New ADODB.Connection
oCon.ConnectionString = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=DB1; Trusted_Connection=yes;"
Set oRS = New ADODB.Recordset
oRS.ActiveConnection = oCon
oRS.Source = "Select * From Table1"
Range("A1").CopyFromRecordset oRS
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing
End Sub

The code uses ActiveX Data Objects (ADO). You need to add a reference to it as shown below

Saturday, March 07, 2009

How to update an Access Table using VBA

How to update an Access Table using ADO

The following code snippet would be helpful to update an Access 2007 database table using VBA. The code uses ADO and requires a reference to ActiveX Data Objects Library

The sample uses a simple table which contains a name and a location field.

The code uses the SQL update query to update the database. The query is executed by the ADO’s command execute method

Sub Simple_SQL_Update_Data()

Dim Cn As ADODB.Connection '* Connection String

Dim oCm As ADODB.Command '* Command Object

Dim sName As String

Dim sLocation As String

Dim iRecAffected As Integer


Set Cn = New ADODB.Connection

Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\Documents\SampleDB.accdb;Persist Security Info=False"

Cn.ConnectionTimeout = 40


sName = "Krishna Vepakomma"

sLocation = "Cincinnati, OH"

Set oCm = New ADODB.Command

oCm.ActiveConnection = Cn

oCm.CommandText = "Update SampleTable Set Location ='" & sLocation & "' where UserName='" & sName & "'"

oCm.Execute iRecAffected

If iRecAffected = 0 Then

MsgBox "No records inserted"

End If

If Cn.State <> adStateClosed Then


End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing

If Not Cn Is Nothing Then Set Cn = Nothing


If Err <> 0 Then

Debug.Assert Err = 0

MsgBox Err.Description


Resume Next

End If

End Sub

Table Before Update Command

Table After Update Command

No value given for one or more required parameters. (ADO Error)

There are many reasons for this error:

  1. Parameter name not spelt correctly
  2. Case is not correct in parameter (firstname instead of FirstName )
  3. Passing incorrect type, for example, numeric instead of string - pass the string within quote

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 Google Group