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