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"

Err_Chart:

If Err <> 0 Then

Debug.Print Err.Description

Err.Clear

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

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;"

oCon.Open

Set oRS = New ADODB.Recordset

oRS.ActiveConnection = oCon

oRS.Source = "Select * From Table1"

oRS.Open

Range("A1").CopyFromRecordset oRS

oRS.Close

oCon.Close

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

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

On Error GoTo ADO_ERROR

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

Cn.Open

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

Cn.Close

End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing

If Not Cn Is Nothing Then Set Cn = Nothing

ADO_ERROR:

If Err <> 0 Then

Debug.Assert Err = 0

MsgBox Err.Description

Err.Clear

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