VBA TO VBSCRIPT

• Sep 20, 2019

• #2

strongm

MIS

May 24, 2001

20,264

GB

vbscript doesn't know the correct values of [tt]xlToLeft[/tt] or [tt]xlToRight[/tt] (they are declared as global constants in Excel, but vbscript does not have access to that)

So you just need to add the following to your code:

[tt]xlToLeft = -4159 '(&HFFFFEFC1)
xlToRight = -4161 '(&HFFFFEFBF)[/tt]

 

Upvote 0 Downvote

• Sep 20, 2019

• Thread starter
• #3

rj771972

Programmer

May 17, 2013

12

US

Thanks, but I'm afraid it still does not work. It calls for a problem in the last line first column.
please see attached error message


Dim objExcel, strExcelPath, objSheet

' You need to create the Example.xls file first.
strExcelPath = "C:\tmp\test.xlsx"

' Open specified spreadsheet and select the first worksheet.
Set objExcel = CreateObject("Excel.Application")
objExcel.WorkBooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
sh = "Journal"
xlToLeft = -4159 '(&HFFFFEFC1)
xlToRight = -4161 '(&HFFFFEFBF)

'LastRow = objExcel.Sheets(sh).Cells(.Rows.Count, 4).End(xlUp).Row
'LastColumn = objExcel.Sheets(sh).Cells(.Columns.Count, 15).End(xlToRight).Column
' Modify a cell. row 3, col 2
BB=objSheet.Cells(1, .Columns.Count).End(xlToLeft).Column

 

 https://files.engineering.com/getfile.aspx?folder=f307afed-71eb-441d-af81-77e22ac0628e&file=Capture.JPG

Upvote 0 Downvote

• Sep 20, 2019

• #4

S

SkipVought

Programmer

Dec 4, 2001

47,492

US

Using...
.Rows or .Columns

...you need a With...End With structure in your code to reference an appropriate object.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

Upvote 0 Downvote

• Sep 20, 2019

• #5

strongm

MIS

May 24, 2001

20,264

GB

Ok, see the problem here is that you state that your (OP) code works in VBA - but as presented here, it would not, as neither .Rows.Count nor .Columns.Count would work, since both need an object reference, which your code does not provide (and this is what your error message is referring to); I just assumed that we were not seeing all your code. I assume you probably want the worksheet, but even then you are inconsistent about how you are selecting the sheet:

[tt]Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

sh = "Journal"
… objExcel.Sheets(sh)
[/tt]
But even if we fix that up your original LastRow function is NOT doing what you describe. The different (commented out) version in your second post is somewhat better (but the LastColumn function is not)!

So try something like the following for your OP code (not your second post):

Code:

[blue]Dim objExcel, strExcelPath, objSheet
xlToLeft = -4159 
xlUp = -4162 
strExcelPath = "C:\tmp\test.xlsx"
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1) [COLOR=green]' if this is the sheet you want[/color]
[COLOR=green]' or
' Set objSheet = objExcel.ActiveWorkbook.Worksheets("Journal")
[/color]
With objSheet
    LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    LastRow = .Cells(.Rows.Count, 15).End(xlUp).Row
End With[/blue]

 

Upvote 0 Downvote

• Sep 20, 2019

• Thread starter
• #6

rj771972

Programmer

May 17, 2013

12

US

Thanks! it works now!

 

Upvote 0 Downvote

• Sep 20, 2019

• Thread starter
• #7

rj771972

Programmer

May 17, 2013

12

US

thank, you are right
any chance you have the codes for xlDown?
Can you share any reference link?

Thank you

 

Upvote 0 Downvote

• Sep 21, 2019

• Thread starter
• #8

rj771972

Programmer

May 17, 2013

12

US

here is another one. This time VBSCRIPT for powerpoint:

the line .Shapes.AddTextbox does not work

Dim applPP
Dim prsntPP
Dim slidePP
Dim shapePP
Dim shpCurrShape
Dim slideCount
Dim oPicture
Dim ppPath, ppName
Set applPP = CreateObject("PowerPoint.Application")
'applPP.Visible = msoTrue
Dim PPTFileIsOpen
presntn = "C:\Users\george\Documents\vbs_test\MySimulations_20190813.pptx"

MSGBOX presntn
' -------------- check if ppt open --------------- start
Dim xc

PPTFileIsOpen = False
karl = applPP.Presentations.Count
For xc = 1 To applPP.Presentations.Count
If applPP.Presentations(xc).FullName = presntn Then
PPTFileIsOpen = True
End If
Next
' -------------- check if ppt open --------------- start

If PPTFileIsOpen = False Then
Set prsntPP = applPP.Presentations.Open(presntn)
End If

ns = prsntPP.Slides.Count
'-------------------------
'ADD FIRST SLIDE:
'The title slide (ppLayoutTitleOnly) has 1 shape, Shape(1) is the title, you can add your own shapes to the slide:
'add a title slide to the new presentation:

Set slidePP = prsntPP.Slides.Add(ns + 1, 12) ' 12 IS THE EMPTY


slideCount = prsntPP.Slides.Count

Dim myTextBox


With prsntPP.Slides(slideCount)
Set myTextBox = .Shapes.AddTextbox _
(msoTextOrientationHorizontal,100,50,400,40)
End With

 

Upvote 0 Downvote

• Sep 21, 2019

• #9

combo

Technical User

Jan 1, 2003

4,198

PL

You need to replace named office applications constants by underlying values or define variables with the same name and assign proper values.
To check values you need, open excel or powerpoint, open VBE window and next object browser. In Excel, Office and PowerPoint libraries find required constants (xlDown, msoTrue, ppLayoutTitleOnly, msoTextOrientationHorizontal).

combo

 

Upvote 0 Downvote

• Sep 21, 2019

• Thread starter
• #10

rj771972

Programmer

May 17, 2013

12

US

thanks, will try

 

Upvote 0 Downvote