Private Sub CommandButton1_Click()
'Define an excel object
Dim objExcel As Object

'Open the excel sheet which needs to be changed
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open Filename:=Application.GetOpenFilename

'Replace text of a cell in the 'Risk, Issue Metrics' worksheet
objExcel.Sheets("Risk, Issue Metrics").Select
objExcel.Cells(9, 1).Value = "No. of minor risks open (i.e. with risk
exposure between 3.5 and 0)"

'A subroutine is located in the same worksheet. This needs to be updated
with a new code here
'How do it do it?

'Save updated excel sheet and close it
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing
End Sub

Hi
see:
http://cpearson.com/excel/vbe.htm

--
Regards
Frank Kabel
Frankfurt, Germany


Pavan Kishore K. S. wrote:
Private Sub CommandButton1_Click()
'Define an excel object
Dim objExcel As Object

'Open the excel sheet which needs to be changed
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open Filename:=Application.GetOpenFilename

'Replace text of a cell in the 'Risk, Issue Metrics' worksheet
objExcel.Sheets("Risk, Issue Metrics").Select
objExcel.Cells(9, 1).Value = "No. of minor risks open (i.e. with
risk exposure between 3.5 and 0)"

'A subroutine is located in the same worksheet. This needs to be
updated with a new code here
'How do it do it?

'Save updated excel sheet and close it
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing
End Sub

Hi Frank,

Thanks for the website. I am facing a specific problem.. My new code looks
like as given below. Please locate the comment with the question:

Private Sub CommandButton1_Click()
'Define an excel object
Dim objExcel As Object, ExcelToBeOpened As String, sFileName As String

'Open the excel sheet which needs to be changed
Set objExcel = CreateObject("Excel.Application")
ExcelToBeOpened = Application.GetOpenFilename(filefilter:="Excel Files,
*.xls")
objExcel.Workbooks.Open Filename:=ExcelToBeOpened
objExcel.Visible = True

Dim VBComp As VBComponent
Set VBComp = objExcel.VBProject.VBComponents.Add(vbext_ct_StdMo dule)
'This is where i am getting the error. It is not accepting the
'objExcel'. How should i reference the excel sheet i just oepend?
VBComp.Name = "NewModule"
Application.Visible = True

'Replace text of a cell in the 'Risk, Issue Metrics' worksheet
objExcel.Sheets("Risk, Issue Metrics").Select
objExcel.Cells(9, 1).Value = "No. of minor risks open (i.e. with risk
exposure between 3.5 and 0)"

objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing
End Sub

Thanks,

Pavan

"Frank Kabel" wrote:

Hi
see:
http://cpearson.com/excel/vbe.htm

--
Regards
Frank Kabel
Frankfurt, Germany


Pavan Kishore K. S. wrote:
Private Sub CommandButton1_Click()
'Define an excel object
Dim objExcel As Object

'Open the excel sheet which needs to be changed
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open Filename:=Application.GetOpenFilename

'Replace text of a cell in the 'Risk, Issue Metrics' worksheet
objExcel.Sheets("Risk, Issue Metrics").Select
objExcel.Cells(9, 1).Value = "No. of minor risks open (i.e. with
risk exposure between 3.5 and 0)"

'A subroutine is located in the same worksheet. This needs to be
updated with a new code here
'How do it do it?

'Save updated excel sheet and close it
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing
End Sub

Set VBComp = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct _StdModule)

--
Regards,
Tom Ogilvy


"Pavan Kishore K. S." wrote in message
...
Hi Frank,

Thanks for the website. I am facing a specific problem.. My new code looks
like as given below. Please locate the comment with the question:

Private Sub CommandButton1_Click()
'Define an excel object
Dim objExcel As Object, ExcelToBeOpened As String, sFileName As String

'Open the excel sheet which needs to be changed
Set objExcel = CreateObject("Excel.Application")
ExcelToBeOpened = Application.GetOpenFilename(filefilter:="Excel
Files,
*.xls")
objExcel.Workbooks.Open Filename:=ExcelToBeOpened
objExcel.Visible = True

Dim VBComp As VBComponent
Set VBComp = objExcel.VBProject.VBComponents.Add(vbext_ct_StdMo dule)
'This is where i am getting the error. It is not accepting the
'objExcel'. How should i reference the excel sheet i just oepend?
VBComp.Name = "NewModule"
Application.Visible = True

'Replace text of a cell in the 'Risk, Issue Metrics' worksheet
objExcel.Sheets("Risk, Issue Metrics").Select
objExcel.Cells(9, 1).Value = "No. of minor risks open (i.e. with risk
exposure between 3.5 and 0)"

objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing
End Sub

Thanks,

Pavan

"Frank Kabel" wrote:

Hi
see:
http://cpearson.com/excel/vbe.htm

--
Regards
Frank Kabel
Frankfurt, Germany


Pavan Kishore K. S. wrote:
Private Sub CommandButton1_Click()
'Define an excel object
Dim objExcel As Object

'Open the excel sheet which needs to be changed
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open Filename:=Application.GetOpenFilename

'Replace text of a cell in the 'Risk, Issue Metrics' worksheet
objExcel.Sheets("Risk, Issue Metrics").Select
objExcel.Cells(9, 1).Value = "No. of minor risks open (i.e. with
risk exposure between 3.5 and 0)"

'A subroutine is located in the same worksheet. This needs to be
updated with a new code here
'How do it do it?

'Save updated excel sheet and close it
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing
End Sub

Ok.. i managed to solve this.. here is the code for others to use

rivate Sub CommandButton1_Click()
'Define an excel object
Dim objExcel As Object, ExcelToBeOpened As String, sFileName As String

'Open the excel sheet which needs to be changed
Set objExcel = CreateObject("Excel.Application")
ExcelToBeOpened = Application.GetOpenFilename(filefilter:="Excel Files,
*.xls")
'Introduce error handling. We will have an error when we run out of files
On Error GoTo fileNotOpened
objExcel.Workbooks.Open Filename:=ExcelToBeOpened
objExcel.Visible = True

'Replace text of a cell in the 'Risk, Issue Metrics' worksheet
objExcel.Sheets("Risk, Issue Metrics").Select
objExcel.Cells(9, 1).Value = "No. of minor risks open (i.e. with risk
exposure between 3.5 and 0)"

Application.Visible = True
'Check if the computer has access to the vbprojects
Dim VBP As Object ' as VBProject
If Val(Application.Version) = 10 Then
On Error Resume Next
Set VBP = ActiveWorkbook.VBProject
If Err.Number < 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic
Project.'", _
vbCritical
Exit Sub
End If
End If

'Delete all code inside a VBProject
'
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Set VBComps = objExcel.Workbooks(1).VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

'Copy module with the new code
'
CopyModule ActiveWorkbook, "RiskIssueLog", objExcel.Workbooks(1)

objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing

fileNotOpened:
'Diable error handling
On Error GoTo 0


End Sub

-------
Sub CopyModule(SourceWB As Workbook, strModuleName As String, TargetWB As
Workbook)

' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", Workbooks ("Book2.xls")
Dim strFolder As String, strTempFile As String
strFolder = SourceWB.Path
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & ""
strTempFile = strFolder & "~tmpexport.txt"
On Error Resume Next
SourceWB.VBProject.VBComponents(strModuleName).Exp ort strTempFile
'TargetWB.VBProject.VBComponents.Import strTempFile
TargetWB.VBProject.VBComponents("Sheet6").CodeModu le.AddFromFile
strTempFile
TargetWB.VBProject.VBComponents("RiskIssueLog").Na me = "Sheet6"

'Code to view all the VBProject components in the excel sheet being opened
'
'Dim VBComp As VBComponent
'Dim Msg As String
' For Each VBComp In TargetWB.VBProject.VBComponents
' Msg = Msg & VBComp.Name & " Type: " & CompTypeToName(VBComp) &
Chr(13)
'Next VBComp
'MsgBox Msg

'MsgBox TargetWB.VBProject.VBComponents(5).Name


Kill strTempFile
On Error GoTo 0

End Sub
---------
All the best

Pavan

"Tom Ogilvy" wrote:

Set VBComp = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct _StdModule)

--
Regards,
Tom Ogilvy


"Pavan Kishore K. S." wrote in message
...
Hi Frank,

Thanks for the website. I am facing a specific problem.. My new code looks
like as given below. Please locate the comment with the question:

Private Sub CommandButton1_Click()
'Define an excel object
Dim objExcel As Object, ExcelToBeOpened As String, sFileName As String

'Open the excel sheet which needs to be changed
Set objExcel = CreateObject("Excel.Application")
ExcelToBeOpened = Application.GetOpenFilename(filefilter:="Excel
Files,
*.xls")
objExcel.Workbooks.Open Filename:=ExcelToBeOpened
objExcel.Visible = True

Dim VBComp As VBComponent
Set VBComp = objExcel.VBProject.VBComponents.Add(vbext_ct_StdMo dule)
'This is where i am getting the error. It is not accepting the
'objExcel'. How should i reference the excel sheet i just oepend?
VBComp.Name = "NewModule"
Application.Visible = True

'Replace text of a cell in the 'Risk, Issue Metrics' worksheet
objExcel.Sheets("Risk, Issue Metrics").Select
objExcel.Cells(9, 1).Value = "No. of minor risks open (i.e. with risk
exposure between 3.5 and 0)"

objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing
End Sub

Thanks,

Pavan

"Frank Kabel" wrote:

Hi
see:
http://cpearson.com/excel/vbe.htm

--
Regards
Frank Kabel
Frankfurt, Germany


Pavan Kishore K. S. wrote:
Private Sub CommandButton1_Click()
'Define an excel object
Dim objExcel As Object

'Open the excel sheet which needs to be changed
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open Filename:=Application.GetOpenFilename

'Replace text of a cell in the 'Risk, Issue Metrics' worksheet
objExcel.Sheets("Risk, Issue Metrics").Select
objExcel.Cells(9, 1).Value = "No. of minor risks open (i.e. with
risk exposure between 3.5 and 0)"

'A subroutine is located in the same worksheet. This needs to be
updated with a new code here
'How do it do it?

'Save updated excel sheet and close it
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing
End Sub