Add Macro to Workbook programatically using Excel VBA

How to Add Macro to Workbook using Excel VBA

Here is a simple procedure to add a macro to the current workbook using VBComponents

Sub Add_Macro_To_ThisWorkbook()

Dim VBP As VBProject
Dim VBM As VBComponent
Dim VBModule As CodeModule
Dim VBProc As VBComponent

Set VBP = ThisWorkbook.VBProject

Set VBModule = VBP.VBComponents.Item("ThisWorkbook").CodeModule

VBModule.AddFromString ("Sub Sample_Macro" & vbCrLf & "ret = msgbox (""Hello VBADUD"") " & vbCrLf & "End Sub")

End Sub

Add Controls Popup Menu using Powerpoint VBA

Powerpoint Application - Create Popup menu using VBA

The following code is a simple one to create popup menu in Powerpoint using VBA

Sub Add_Ctrl_To_Popup(ByVal sControlName As String, ByVal sMacroName As String)

On Error GoTo DisplayErr

' ---------------------------------------------------------------'
Written By Shasur for http://vbadud.blogspot.com
'---------------------------------------------------------------
Dim ctlCB As CommandBarDim ctlCommand As CommandBarControl

Set ctlCB = Application.CommandBars("Shapes")

If ctlCB Is Nothing Then Exit Sub

Set ctlCommand = ctlCB.Controls.Add

ctlCommand.Caption = sControlName

ctlCommand.OnAction = sMacroName

DisplayErr:

If Err <> 0 Then

MsgBox Err.Description

Err.Clear
Resume Next

End If
End Sub

Following sub will call the function

Sub Drive_Add_Ctrl_To_Poppup()
Add_Ctrl_To_Poppup "NewMenu", "Test"
End Sub

The above function will add an item to popup menu using VBA and assign a macro to it

See also:

Creating Menu’s and CommandBars in Powerpoint using VBA

Disable Cut & Copy from Popup menu (Excel VBA/Word VBA)

enable popup menu

Disable Right Click using VBA

Add Control To PopupMenu

Opening Comma Separate File (CSV) through ADO

Sort CSV File through Excel VBA ADO/ Open CSV File using ADO

CSV files are easy way of storing data apart from XML files. There are many times we need the data from CSV file to be extracted in a specific format / specific data alone to be extracted. In such cases we can use ADO to extract restricted data using SQL Query

The main changes in connection string from using Access to using CSV would be the following :

Source=c:\temp\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)""

Source : Folder where the file exists

Extended Properties: Text (denoting Text File), HDR =Yes (If Header is present in the Text File),

FMT=Delimited(,) (denoting comma delimiter)

The following would be the SQL Query

sQuery = "Select * From VBA.csv ORDER BY ID"

We have only specified the folder name in the connection string and here we specify the file name (VBA.csv)

The following code needs reference to Microsoft ActiveX Data Objects library

Sub Open_Sort_CSV()

Dim cN As ADODB.Connection '* Connection String

Dim RS As ADODB.Recordset '* Record Set

Dim sQuery As String '* Query String

On Error GoTo ADO_ERROR

' -----------------------------------------

' Coded by Shasur for vbadud.blogspot.com

' -----------------------------------------

cN = New ADODB.Connection

cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"

cN.ConnectionTimeout = 40

cN.Open()

RS = New ADODB.Recordset

sQuery = "Select * From VBA.csv ORDER BY ID"

RS.ActiveConnection = cN

RS.Source = sQuery

RS.Open()

If RS.EOF <> True Then

While RS.EOF = False

Open "c:\temp\vba_sorted.csv" For Append As 1

Print #1, RS.Fields(0) & "," & RS.Fields(1)

RS.MoveNext()

Close #1

End While

End If

If RS.State <> adStateClosed Then

RS.Close()

End If

If Not RS Is Nothing Then RS = Nothing

If Not cN Is Nothing Then cN = Nothing

ADO_ERROR:

If Err <> 0 Then

Debug.Assert(Err = 0)

MsgBox(Err.Description)

Resume Next

End If

End Sub

Sorting Text files (comma separated) is not that easy. One option is to open the file in Excel, sort the columns and save it back (Refer ). However, this cannot be done if the rows are more than that Excel can accomodate in that case you can use ADO to sort the file and write the file back to CSV

Common Errors

You will get the following error if the specified file is not available “The Microsoft Jet database engine could not find the object 'VBA.csv'. Make sure the object exists and that you spell its name and the path name correctly.”

ADO Error :- Query must have at least one destination field

The above error occurs when there are no records in the Text file

ADO Error :- Operation is not allowed when the object is closed.

The above error occurs when some operations are done on closed recordset etc











ADO Connection string for Text File, ADO Connection string for CSV File, Excel VBA ADO for Text Files, ADO Text File Connection String, Connection String CSV File ADO, Open Text File using ADO, Sort Text File using ADO, Open and Sort Text Files using ADO,

See also:

C# CSV to XML Conversion, CSV to XML Conversion using LINQ

Insert CSV File to Array using C#

Convert Excel Comments to Text using VBA

VBA Copy comments to a Range in Excel

If a worksheet is dotted with lot of comments and you want to respond to each one of these, it is better to extract the comments and place it in a column. This would help in responding to the comments. The following code will extract all comments and place it in column ‘F’ along with the value of the original cell

Sub Convert_Comment_To_Text()

Dim oCom As Comment

Dim sVal As String

Dim i As Integer

For i = 1 To ActiveSheet.Comments.Count

sVal = ""

Set oCom = ActiveSheet.Comments(i)

sVal = ActiveSheet.Cells(oCom.Parent.Row, oCom.Parent.Column).Value

ActiveSheet.Range("F" & CStr(oCom.Parent.Row)).Value = "'" & sVal & " -- " & oCom.Text

Next

End Sub

Before Running the Macro
Comments placed in Column F

See also:

Delete Comments from Excel Workbook using VBA