Can VBA (Excel) be used to create COM objects

[shsu]

Hello,

Could you please tell me how to write a VB script using/with COM to open an excel sheet, modify the data and close the excel sheet. Is it possible to create COM objects/add-ins with VBA?

I have used Excel VBA script to open an Excel sheet, modify/insert the data and close the Excel sheet. Below is my code. Is it possible to reuse this code to produce the same end result but using COM object ?

############################################################
Private Sub CommandButton1_Click()

Dim xniku As New Excel.Application
Dim xdime As New Excel.Application
Dim icountn As Range
Dim icountd As Range
Dim totalrowsdim As Range
Dim user_named As String
Dim user_namen As String
Dim niku_state As String
Dim resource_id As String
Dim first_name As String
Dim last_name As String
Dim dim_product As String
Dim k As Integer

k = 0
xdime.Workbooks.Open "D:\Dimensions.xls"

MsgBox icountd.Rows.Count

For i = 2 To icountd.Rows.Count
user_named = xdime.ActiveSheet.Cells(i, 1)
MsgBox user_named
dim_product = xdime.ActiveSheet.Cells(i, 2)
MsgBox dim_product
xniku.Workbooks.Open "D:\Nikuclarity.xls"
Set icountn = Range("A1:B1", Range("A1").End(xlDown))
MsgBox icountn.Rows.Count
For j = 2 To icountn.Rows.Count
user_namen = xniku.ActiveSheet.Cells(j, 1)
MsgBox user_namen

If user_namen = user_named Then
MsgBox "both are equal"
xniku.ActiveSheet.Cells(6, i) = dim_product

End If
Next j
Next i

xniku.ActiveWorkbook.Save
xniku.Quit
xdime.ActiveWorkbook.Save

Set xniku = Nothing
End Sub

############################################################

Apologies if my questions are not clear. Appreciate your help……………

Thanks

 

[SkipVought]

This forum is not VB Script. It is VBA (Visual Basic for Applications)

So do you want to code in VBA or VB Script? Your code looks like VBA. Has some problems, though.

You can use the CreateObject method to create new objects. What is it that you need to do? And remember that VBA runs WITHIN an application like Excel or Word.

Skip,

_[red][/red]

 

[SkipVought]

... BTW, VBScript is Forum329

Skip,

_[red][/red]

 

[fumei]

Yes, please describe what you want to do. As Skip points out, this is the VBA forum.

Is it possible to create COM objects/add-ins with VBA?

This is actually a two part question.

The first part - is it possible to create COM objects with VBA? No. COM components are files (typically .exe or .dll files). VBA does not create .exe or .dll files.

The second part - is it possible to create add-ins? These can also be files, but not .exe or .dll. Yes. VBA can create add-ins. However, these files are read by, and are essentially part of, an application - hence Visual Basic for Applications. Word, for example, can create .dot files (templates files) that are strictly used as add-ins, as holders of code.

However, as Skip points out, VBA runs within an application. You can not make VBA code execute without an instance of a VBA compliant application.

Gerry
My paintings and sculpture

 

[shsu]

Dear Fumei and skipvought,

Many thanks for your valuable time in responding to my query.

Regards,
Sugena