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.