×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA TO VBSCRIPT

VBA TO VBSCRIPT

VBA TO VBSCRIPT

(OP)
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

RE: VBA TO VBSCRIPT

vbscript doesn't know the correct values of xlToLeft or xlToRight (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:

xlToLeft = -4159 '(&HFFFFEFC1)
xlToRight = -4161 '(&HFFFFEFBF)

RE: VBA TO VBSCRIPT

(OP)
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

RE: VBA TO VBSCRIPT

Using...
.Rows or .Columns

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: VBA TO VBSCRIPT

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:

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

sh = "Journal"
… objExcel.Sheets(sh)

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

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) ' if this is the sheet you want
' or
' Set objSheet = objExcel.ActiveWorkbook.Worksheets("Journal")

With objSheet
    LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    LastRow = .Cells(.Rows.Count, 15).End(xlUp).Row
End With 

RE: VBA TO VBSCRIPT

(OP)
Thanks! it works now!

RE: VBA TO VBSCRIPT

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

Thank you

RE: VBA TO VBSCRIPT

(OP)
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

RE: VBA TO VBSCRIPT

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

RE: VBA TO VBSCRIPT

(OP)
thanks, will try

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close