Hello Friends,
In VBA macro, we can edit the Sheet Code Name and It has many advantages…
like We Don’t Use
Workbooks(“Book1.xlsx”) or Workbooks(“Collection_Example.xlsx”)
or
Workbooks(1) or ActiveWorkbook or ThisWorkbook
or
Worksheets(“Sheet1”) or Worksheets(1) or Sheets(“Sheet1”) or Sheets(1)
We can directly use the “Sheet Code Name” Like this
' Get The Range For Reading Data
Dim rng As Range
Set rng = shData.Range("A1").CurrentRegion
and …
' Writing Data in E1
Dim item As Variant, row As Long
row = 1
For Each item In myColl
shData.Cells(row, 5).Value = item
row = row + 1
Next item
Here, shData is "Sheet Code Name"
shData.Range(“A1”).CurrentRegion
and
shData.Cells(row, 5).Value = item
Like this Feature in VBA … Does LibreOffice Calc have Same Feature have ? in Macro.
Because, Writing Long Code Very Messy …Please Help …
Here is the Screen Shots For Easy Under Standings…
.
…
…
I have Writing Both VBA and BASIC Code here and get the results
- VBA
======
a) WorkSheets or Sheets Name or Index
=============================
b) Sheet Code Name
c) VBA Code and get Results in E1
Sub Read_Write_IntheWorkSheets_WithHeader()
Dim myColl As Collection
Set myColl = New Collection
' Get The Range For Reading Data
Dim rng As Range
Set rng = shData.Range("A1").CurrentRegion
' Reading Data Including Header
Dim i As Long
For i = 1 To rng.Rows.Count
If rng.Cells(i, 2).Value > 70 Or i = 1 Then
myColl.Add rng.Cells(i, 1).Value
End If
Next i
' Writing Data in E1
Dim item As Variant, row As Long
row = 1
For Each item In myColl
shData.Cells(row, 5).Value = item
row = row + 1
Next item
End Sub
- BASIC
=======
a) LibreOffice_Calc_ SheetName or Sheet Index
======================================
b) LibreOffice _ Calc _ BASIC Code and get Results
Sub Reading_Data_From_Sheets
Dim myColl As New Collection
Set myColl = New Collection
' Identifying The Data Range For Reading
Doc = ThisComponent : Sheet = Doc.Sheets.getByName("Marks_Class_B")
Cell = Sheet.getCellRangeByName("A1")
Cursor = Sheet.createCursorByRange(Cell)
Cursor.collapseToCurrentRegion
' Select GuI
view = Doc.CurrentController : frame = view.getFrame()
frame.Activate : view.Select(Cursor)
' Reading Data Starts From Header Row
Dim i As Long
For i = 0 To Cursor.Rows.Count
If Cursor.Spreadsheet.getCellByPosition(1, i).Value > 70 or i = 0Then
myColl.Add Cursor.Spreadsheet.getCellByposition(0, i).String
End if
Next i
' Write The Data in to the Range("E1")
Dim item As Variant, row As Long
row = 0
For Each item In myColl
Sheet.getCellByPosition(4, row).String = item
row = row + 1
Next item
End Sub
Please Help…the "Sheet Code Name"
