VBA TO VBSCRIPT

[rj771972]

I have this code bits that work in VBA and I need the VBScript version.
All I am trying to do, is get the latest column number of row 1 and the last row of column 15
Last two lines do not work.

Any ideas?

Dim objExcel, strExcelPath, objSheet
strExcelPath = "C:\tmp\test.xlsx"
Set objExcel = CreateObject("Excel.Application")
objExcel.WorkBooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
sh = "Journal"
LastColumn=objSheet.Cells(1, .Columns.Count).End(xlToLeft).Column
Lastrow = objExcel.Sheets(sh).Cells(.Columns.Count, 15).End(xlToRight).Column

 

[strongm]

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]

 

[rj771972]

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

 

[SkipVought]

Using...
.Rows or .Columns

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

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):

[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]

 

[rj771972]

Thanks! it works now!

 

[rj771972]

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

Thank you

 

[rj771972]

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

 

[combo]

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

 

[rj771972]

thanks, will try