VB Script Runtime Error: Object Required: 'Active Cell'

1
votes

Trying to run the following VBScript:

Set ObjExcel = createobject("Excel.Application")
Set objWb = objExcel.Workbooks.Open("C:\test.xlsx")
Set objSheet = objWb.Worksheets("Sheet1")


objSheet.Cells(1,1).Select
With ActiveCell.Font 
 .Bold = True 
 .Italic = True 
End With

Throwing an error as follows:

Line: 7 Char: 1 Error: Object Required: 'ActiveCell' Codes: 800A01A8 Source: Microsoft VBScript runtime error

So it ain't linking ActiveCell. I have spent hours on this and searched a lot of similar issue's online with no luck, any suggestions? Thanks in advance.

2
excelvbscript
The error message is clear: ActiveCell will be interpreted as an object in your script without any declaration. - Reporter
ok, I had thought that ActiveCell was a predefined function(if that is the correct term) in VB Script that maneuvered to the currently selected cell? If I have to declare it , would that then mean it is a variable name? thanks - CodeSpy

2 Answers

2
votes

".ActiveCell" (and all those other Active*s) are concepts for the interactive use of Excel. It means the Cell the user just clicked - probably in error, just wait, till he hit the one he meant. If you automate Excel you are getting rid of the user and therefore the need to keep track of his maniac clicks. Now a PROGRAM asks another PROGRAM to do the necessary tasks directly. No need to simulate a user's click (.Select) and search all over place which Cell was activated. The PROGRAM just says: "Please -

With objSheet.Cells(1,1).Font 
 .Bold = True 
 .Italic = True 
End With

thank you so much".

1
votes

Try this:

Set ObjExcel = createobject("Excel.Application")
ObjExcel.Visible=true                     'you can remove this line as per your requirement
Set objWb = objExcel.Workbooks.Open("C:\test.xlsx")
Set objSheet = objWb.Worksheets("Sheet1")
objSheet.Cells(1,1).Select
Set reqCell = ObjExcel.ActiveCell         'Setting reference to the active cell
With reqCell.Font 
 .Bold = True 
 .Italic = True 
End With