Six weeks ago I migrated from Windows 10 to Linux Miint.
I was using OpenOffice on Windows, and am now using LibreOffice on Mint.
One file has a macro to import updated records from a text file on another Linux machine into Calc.
This macro no longer works.
The other machine (rpi32) is ‘mapped’ as:
# [2019-11-10 07:37] maxg@maxg-pc-mint ~ $
la /home/maxg/rpi32/sppro/
total 32
drwxr-xr-x 2 1001 1001 4096 Jun 25 21:11 .
drwxr-xr-x 6 1001 1001 4096 Aug 4 11:33 ..
-rwx------ 1 1001 1001 7531 Nov 10 02:02 PowerFlowSummaryLog.txt
The text file is listed above (PowerFlowSummaryLog.txt)
The macro is:
Sub DataFromFile
' reading txt, and adding lines not in the spreadhseet into first/next empty row
' all indexes start at zero
'
' 190120 MaxG v1.1 - clean-up and documentation
' 190120 MaxG v1.0 - added: write all missing TXT records to next empty spreadsheet rows
' 190119 MaxG v0.2 - added: write array for last TXT row
' 190118 MaxG v0.1 - PoC; read TXT, write raw line
' establish environment
myWorkBook = ThisComponent
'targetSheet = myWorkBook.Sheets(3) ' 3 = sheet 4
targetSheet = myWorkBook.Sheets.getByName("PowerFlow")
targetColumn = targetSheet.Columns(0) ' column A
sourceText = FreeFile
Select Case getGUIType
Case 1: sourceFile = convertToURL("\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt")
case 4: sourceFile = convertToURL("/home/maxg/sppro/PowerFlowSummaryLog.txt")
End Select
Dim eRgs As Object ' ?
Dim u As Integer ' ?
eRgs = targetColumn.queryEmptyCells ' get empty cells
u = eRgs.Count - 1 ' u seems to be always 0; why?
' catch error: if no rows left, and exit program
If u < 0 Then
MsgBox("No empty row in column.")
Exit Sub
End If
Dim getFirstEmptyRow As Integer ' in spreadsheet
Dim lastNonEmptyRow As Integer ' in spreadsheet
' determine the first empty row in column
getFirstEmptyRowInColumn = Split(eRgs.RowDescriptions(0), " ")
' extract first empty row
firstEmptyRow = cInt(getFirstEmptyRowInColumn(1)) - 1
' set last non-empty row
lastNonEmptyRow = firstEmptyRow - 1
Dim oLastSheetDate As Object ' object last date in the spreadsheet
Dim lastSheetDate As Date ' last date in the spreadsheet
Dim rowAdanceCounter As Integer ' row advance counter; to increment row position by one per date
Dim txtDate As Date ' date from row in text file
' get the last date from the sheet
oLastSheetDate = targetSheet.getCellByPosition(0, lastNonEmptyRow)
'MsgBox "oLastSheetDate | row=" & oLastSheetDate.getValue & " | " & lastNonEmptyRow + 1
lastSheetDate = oLastSheetDate.getValue
rowAdanceCounter = 0 ' init to 0, increment in import loop
Open sourceFile For Input As sourceText
' go through source text file line by line until EOF
While Not EOF(sourceText)
' get one line at a time
Line Input #sourceText, currentLine
' turn text values delimited by TAB into an array
arrCurrentLine = Split(currentLine, Chr(9))
' get date of line
txtDate = arrCurrentLine(0)
'MsgBox "txtDate=" & txtDate
If txtDate > lastSheetDate Then
' now put data into the spreadsheet
'MsgBox "txtDate > lastSheetDate -> row to add"
Dim numItems As Integer ' number of array items
Dim cell As Object ' cell to work with
Dim i As Integer ' foor loop counter
i = 0
numItems = Ubound(arrCurrentLine)
' run through the text line array and place one value in cells moving right
For i = 0 to numItems
' set desired cell position
cell = targetSheet.getCellByPosition(i, firstEmptyRow + rowAdanceCounter)
' loop through text line array
If i <> 0 then
' write cell as number
cell.Value = arrCurrentLine(i)
Else
' write first cell (a date) as date via .FormulaLocal
cell.FormulaLocal = arrCurrentLine(i)
End If
Next i
rowAdanceCounter = rowAdanceCounter + 1
End If
Wend
Close sourceText
End Sub
The bit I have changed trying to make it work is this part:
Select Case getGUIType
Case 1: sourceFile = convertToURL("\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt")
case 4: sourceFile = convertToURL("/home/maxg/sppro/PowerFlowSummaryLog.txt")
End Select
Which was before (on Windows/OO):
sourceFile = convertToURL("\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt")
At present the macro errors out with: Basic I/O error pointing to this line:
Open sourceFile For Input As sourceText
Any hints would be much appreciated.
) next to the answer.
