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
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
So you just need to add the following to your code:
xlToLeft = -4159 '(&HFFFFEFC1)
xlToRight = -4161 '(&HFFFFEFBF)
RE: VBA TO VBSCRIPT
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
.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
RE: VBA TO VBSCRIPT
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
RE: VBA TO VBSCRIPT
any chance you have the codes for xlDown?
Can you share any reference link?
Thank you
RE: VBA TO VBSCRIPT
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
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