Selecting Excel Cells with data in VBScript

Question

I have been trying to manipulate Excel files with only VBScript code. It's a stand-alone VBScript that is opening and closing excel files. Problem is with Range(Selection, Selection.End(xlDown)).Select code. When I run this code in excel macro it is selecting all rows with continuous data in a particular cell. As I want it to do. Problem is when i try to run this code from VBScript. Type of file is .vbs

I have created Excel object: Set objExcel = CreateObject("Excel.Application") inside the script. and code objExcel.Range("A5").select is selecting the right cell. But objExcel.Range(Selection, Selection.End(xlDown)).Select is doing nothing. It is the same functionality when you hold shift + ctrl and press down arrow in excel. When I record macro in excel and run it there are also no problems, the same code is running in VBA normally. Rows are getting selected. Only issue is when I convert that code to stand-alone VBScript that nothing is happening.

I need a way to select continuous cells with data in it. Same like ctrl+shift + down,up,left,right. If you know any other way of achieving it I'm OK with it.

@BigBen wouldn't encourage the use of "magic numbers", just define the constant in the code `Const xlDown = -4121`. — user692942, Nov 27 '18 at 20:35

score 1 · Accepted Answer · answered Nov 27 '18 at 18:11

1

VBScript doesn't understand the constants used by the VBA setup such as xlDown.

You need to reference the actual value for xlDown instead, which you can get from the MS Docs site

In this specific instance you mention, the constant value is -4121 so your command in vbscript would be:

objExcel.Range(Selection, Selection.End(-4121)).Select i

However, you should always try to avoid using Select in VBA/VBScript due to the performance hit, check here for more info

answered Nov 27 '18 at 18:11

Dave

4,328
2
24
33

2

@IGRACH Here's the specific [link](https://learn.microsoft.com/en-us/office/vba/api/excel.xldirection) for the `xlDirection` enum. – BigBen Nov 27 '18 at 18:14
It's still not selecting. `objExcel.Workbooks(File_Name).Activate objExcel.Range("A5").select objExcel.Range(Selection, Selection.End(-4121)).Select i` Its not going passed `objExcel.Range("A5").select` line. – IGRACH Nov 27 '18 at 18:32
1

Not the first time this has [been answered](https://stackoverflow.com/a/14019338/692942). Even had to dup flag [one of my own](https://stackoverflow.com/a/39956808/692942) over two years ago. Not surprising seen as though VBScript has been around twenty plus years. – user692942 Nov 27 '18 at 20:33

score 1 · Answer 2 · edited Nov 28 '18 at 19:34

I figured it out when I used a test script.

The problem was with xlDirection. The code started working when I started using numbers from the documentation. Also there was an issue was with the Selection keyword in objExcel.Range(Selection, Selection.End(-4121)).Select. It returned Object required:'Selection'. I added objExel. to both instances of Selection:

objExcel.Range(objExcel.Selection, objExcel.Selection.End(-4121)).Select.

It's now working as it should.

Selecting Excel Cells with data in VBScript

2 Answers2