[Solved] Read last row in .txt + copy after last row in Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

[Solved] Read last row in .txt + copy after last row in Calc

Post by MaxG »

I wrote this routine in Excel.vba and then thought copy it to Calc and it will work... well, as probably most know (and I learned), this is not how it works.
The code has a scheduler to open a TXT file, get the last line and copy it into the first empty row in calc.

Code: Select all

Sub OpenAndImportTxtFile()
  Dim wbI As Workbook
  Dim wbO As Workbook
  Dim wsI As Workbook
  Dim X As Integer
  Dim nextrow As Long
  
  Application.DisplayAlerts = False
  Set wbI = ThisWorkbook
  Set wbO = Workbooks.Open("\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt")
  nextrow = ThisWorkbook.Sheets("PowerFlow").Range("A" & Rows.Count).End(xlUp).Row + 1
  wbO.Sheets(1).Rows("1:1").Copy
  ThisWorkbook.Sheets("Powerflow").Range("A" & nextrow).PasteSpecial xlPasteValues
  wbO.Close SaveChanges:=False
  Application.DisplayAlerts = True
End Sub

Sub Schedule_Run()
  ' first call appropriate procedure
  Call OpenAndImportTxtFile
  ' next, set new calling time
  Application.OnTime TimeValue("02:22:22"), "Schedule_Run"
End Sub
After trailing to convert this into OO, I am getting nowhere.
I recorded a macro, but it does not record the actions in the text file, nor do I know how to find the first empty cell in column A.
Any hints / pointers appreciated.

[added later]

Some code I found, and started playing with...
Next is finding the last non-empty cell in a column (or the first empty cell after content).
Then figuring out how the text (tab delimited values) can be inserted so they populate across the columns (in that row).

Code: Select all

Sub DataFromFile
    Dim FileNo      As Integer
    Dim CurrentLine As String
    Dim File        As String

    currentSheet = ThisComponent.CurrentController.ActiveSheet
    fileName     =  "\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt"

    ' Create a new file handler and open it for reading
    FileNo = FreeFile
    Open fileName For Input As #FileNo

    ' Read file until EOF is reached
    Do While not eof(FileNo)
      ' Read line 
      Line Input #FileNo, CurrentLine   

      ' Define the range to put the data in as A4:A999 '
      currentCell = currentSheet.getCellRangeByName("A1:A999").getCellByPosition(0,0)
    Loop

    currentCell.String = CurrentLine

    Close #FileNo
End Sub
Last edited by MaxG on Sat Jan 19, 2019 11:05 am, edited 4 times in total.
OpenOffice 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Read last line in TXt file and copy after last row in ca

Post by JeJe »

There's a current thread for finding the empty cell:

viewtopic.php?f=21&t=96475

You can use the Split function to separate out your values so you can put them in separate columns
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Read last line in TXt file and copy after last row in ca

Post by Lupp »

Elöderly code! The Line Input wouldn't work witout the # chartacter, I suppose.

Code: Select all

Sub doIt
doc0 = ThisComponent
sheet0 = doc0.Sheets(0)
theColumn = sheet0.Columns(2)
REM Insert the full absolute filepath of your text file below.
fn = convertToURL("C:\Users\Someone\Documents\aFolder\myTextFile.txt")
hF = FreeFile
Open fn For Input As hF
While NOT (EoF(hF))
  Line Input #hF, theLine
Wend
Close hF
eRgs = theColumn.queryEmptyCells
u = eRgs.Count-1
If u<0 Then 
  MsgBox("No empty row in colujmn.")
  Exit Sub
End If
eRg = eRgs(u)
iCell = eRg.getCellByPosition(0, 0)
iCell.String = theLine
End Sub
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: Read last line in TXt file and copy after last row in ca

Post by MaxG »

JeJe wrote:You can use the Split function to separate out your values so you can put them in separate columns
Thanks :) but don't get me started on this one; I was fluffing around with this for a few hours, searched the web and couldn't find (for the first time) what I am after: "open office calc basic split delimiter tab" I have:

Code: Select all

  Dim txtArray As Variant
  txtArray = Split(CurrentLine,"\t")
  Print txtArray(0)
  'Print txtArray(1)
  'Print txtArray(2)
  'Print txtArray(3)
Which throws an error: inadmissible value or data type. Index out of defined range.
any other split function I have used splits into an array().

Same with the following:

Code: Select all

  For Each i In txtArray()
    currentCell = currentSheet.getCellRangeByName("A1:A400").getCellByPosition(0,i+7)
    currentCell.String = txtArray(i)
  Next i
Lupp wrote:Elöderly code! The Line Input wouldn't work witout the # chartacter, I suppose.
I will check this out, thanks.
[added later] Wow, works, like the simplicity of it.
Last edited by MaxG on Sat Jan 19, 2019 2:03 am, edited 1 time in total.
OpenOffice 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Read last line in TXt file and copy after last row in ca

Post by JeJe »

tab is Chr(9). You can find these values in a table of character codes or msgbox Asc(" ")

Edit:
characters like the quote chr(34) or return and line feed characters Chr(13), Chr(10) you have to put
in chr() form but for tab you just type the tabkey in between the quotes.

Code: Select all


txtArray = Split(CurrentLine,"	") 'its a tab in between the quotes. 
for i = 0 to ubound(txtArray)
msgbox txtArray(i)
next 

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Read last line in TXt file and copy after last row in ca

Post by Villeroy »

Works without office suite:

Code: Select all

$ echo $(tail --lines 1 input.txt) >> output.txt
appends the last line of file input.txt to file output.txt on OSX or Linux.
Does anybody know how to do this with Windows?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Read last line in TXt file and copy after last row in ca

Post by JeJe »

Villeroy

https://stackoverflow.com/questions/513 ... t-with-bat

calling

shell ("path to bat file" )
from OO and

together with a bat file with

ECHO Some text>>"path to target file"

does indeed add "Some text" to the end of the file. I'd imagine there's some way to externalise "Some text"

Reading lines:

https://stackoverflow.com/questions/274 ... tch-script
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: Read last line in TXt file and copy after last row in ca

Post by MaxG »

Lupp wrote:Elöderly code!
Whatever "Elöderly" means... however, it works :)
It is short and sweet too. Nice. Thanks.

Your code essentially opens a text file, and adds the last line to the first empty cell in a column!

The only bit missing for me is splitting the text line at the TAB and place the values in the next column of the identified row.
As in the first empty row is found, then place the values in the following columns of that row.

I experimented earlier with the last row as convertToURL(); which did not work (maybe 'of course').
... and did not keep the code. Something with an array of arguments that had a delimiter, and field type in there.
Most notably, the first field is a date, all others are numbers.

[added later] found the code; still have to figure out how to adopt it...

Code: Select all

  Dim Propval(1) as New com.sun.star.beans.PropertyValue
  
  Propval(0).Name = "FilterName"
  Propval(0).Value = "Text - txt - csv (StarCalc)"
  Propval(1).Name = "FilterOptions"
  Propval(1).Value ="9,34,0,1,1"   'ASCII 9 = tab
  
  oCSV = StarDesktop.loadComponentFromURL(sourceFile, "_blank", 0, Propval())
... this opens a new file and imports the text into columns. No dabbling in making this work as I need it to.


My text file looks like this:

Code: Select all

01/01/2019	52.150	24.530	10.671	-9.354	0.000	26.507	2.12	54.30	100.00	52.36	56.17	26.00	30.00	26.20	27.83
02/01/2019	49.620	26.780	10.352	-9.499	0.000	22.168	1.59	55.80	100.00	52.18	56.26	26.00	30.00	26.11	27.85
03/01/2019	39.750	23.820	10.473	-9.210	0.000	14.958	0.72	54.90	100.00	52.15	56.06	27.00	30.00	26.09	27.91
...
16/01/2019	53.980	24.670	9.764	-8.861	0.000	28.657	2.38	57.00	100.00	52.09	56.04	27.00	31.00	26.06	27.95
17/01/2019	53.140	25.160	10.362	-9.395	0.000	27.291	2.21	54.70	100.00	52.07	56.18	27.00	31.00	26.04	27.89
18/01/2019	51.730	25.510	9.845	-9.033	0.000	25.822	2.04	57.00	100.00	52.47	56.17	27.00	30.00	26.25	27.83
I also slightly reworked your code to:

Code: Select all

Sub DataFromFile
  ' reading txt, and adding last line to first empty row
  ' all indexes start at zero
  
  myWorkBook   = ThisComponent
  targetSheet  = myWorkBook.Sheets(3)   ' sheet 4
  targetColumn = targetSheet.Columns(0) ' column A
  
  sourceFile = convertToURL("\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt")
  hF = FreeFile
  Open sourceFile For Input As hF
  
  While Not EOF(hF)
    Line Input #hF, currentLine
  Wend
  
  Close hF
  
  eRgs = targetColumn.queryEmptyCells
  u = eRgs.Count - 1
  
  If u < 0 Then 
    MsgBox("No empty row in column.")
    Exit Sub
  End If
  
  eRg = eRgs(u)
  iCell = eRg.getCellByPosition(0, 0)
  iCell.String = currentLine
  
End Sub
Last edited by MaxG on Sat Jan 19, 2019 3:28 am, edited 1 time in total.
OpenOffice 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Read last line in TXt file and copy after last row in ca

Post by Villeroy »

MaxG wrote:My text file looks like this:
Are columns separated by tabs?
If this is the target file which gets the last row of the source file, how many rows does the source file have? Is it a fixed amount of rows?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: Read last line in TXt file and copy after last row in ca

Post by MaxG »

JeJe wrote:tab is Chr(9). You can find these values in a table of character codes or msgbox Asc(" ")

Code: Select all

txtArray = Split(CurrentLine,"	") 'its a tab in between the quotes. 
Yes, thanks, I realised this line with Chr(9)...

Code: Select all

txtArray = Split(CurrentLine, Chr(9))
OpenOffice 4.1.5 on Windows 10
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: Read last line in TXt file and copy after last row in ca

Post by MaxG »

Villeroy wrote:
MaxG wrote:My text file looks like this:
1. Are columns separated by tabs?
2. If this is the target file which gets the last row of the source file, how many rows does the source file have? Is it a fixed amount of rows?
1. Yes, tab delimited...
2. No, it is the source file where I need to import the last line once a day into an existing spreadsheet. this file starts every month with one line, and another line is added every day. So the file I have shown, has -- where the ellipsis is -- the entries for the 4th to the 15th.
OpenOffice 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Read last line in TXt file and copy after last row in ca

Post by Villeroy »

Download the template SheetMerger.ots from viewtopic.php?f=21&t=77069
It is designed to merge all lines of spreadsheet or text files within a specific directory. But we can modify its behaviour easily.
Open the template and save it as spreadsheet document (*.ods).
Hit the [...] button to specify the path of your incoming file.
Since the file is always the same, enter the file name "PowerFlowSummaryLog.txt".
Ignore the other options.
This document gets the incoming last rows of "PowerFlowSummaryLog.txt" in the specified directory.
---------------------------
Open your input file PowerFlowSummaryLog.txt manually like this:
menu:File>Open...
File Type: "Text (csv)" among the spreadsheet formats.
In the text import dialog:
Language: English(Australian or GB, not USA)
Column separator: Tab
Detect special numbers: Checked
-----------------
Now your active document should be the manually opened text file.
Call Tools>Macros>Run...
Dbl-Click the spreadsheet document with the setup, library "SheetMerge", module "FilterOptions" and run "setFilterOptions"
This will fill out the FilterOptions cell in your import document with the correct filter options of the currently active text file.
------------------
Open the macro editor and visit your document's library "SheetMerge", module "Module1", routine "Sub processFile"
Replace the whole routine with this simplified version:

Code: Select all

Sub processFile(sURL, sfile)
	doc = StarDesktop.loadComponentFromURL(sURL, "_blank", 0, aOptions)
	urg = getUsedRange(doc.Sheets.getByIndex(nIndex))
	src = urg.getRangeAddress()
	src.StartRow = src.EndRow
	rg = getRangeByAddress(doc, src)
	a() = rg.getDataArray()
	x = uBound(a())
	y = uBound(a(0))
	adr = oCurrRg.getRangeAddress()
	adr.EndColumn = adr.StartColumn + y
	adr.StartRow = adr.EndRow + 1
	adr.EndRow = adr.EndRow
	sh = oCurrRg.getSpreadsheet()
	sh.insertCells(adr, com.sun.star.sheet.CellInsertMode.DOWN)
	rg = getRangeByAddress(sh, adr)
	rg.setDataArray(a())
	doc.close(True)
	oCurrRg = getCurrentRegion(oCurrRg)
End Sub
-------------------------------
When a new version of the source file arrived hit the [Go] button.
On the "Data" sheet you should see the last line appended to the previous lines.

----------------------------------
The macro inserts new cells below the bottom of the current list before it dumps the data from the source file's last row into the new cells. This updates any references in formulas, charts etc. to the new size of the target range below the cell named "Target". Other macros simply dump new data below the current range overwriting any data below and without updating any references.
Put any column headers into the target cell's row.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: Read last line in TXt file and copy after last row in ca

Post by MaxG »

Thanks @Villeroy ... I do appreciate the effort. Since this macro needs to be finally automated to run once per day, manual intervention is just not feasible... and that I am almost there with the code below, I'd like to invite help to get last lines sorted. This is what I have got now...

Code: Select all

Sub DataFromFile
  ' reading txt, and adding last line to first empty row
  ' all indexes start at zero
  
  myWorkBook   = ThisComponent
  targetSheet  = myWorkBook.Sheets(3)   ' sheet 4
  targetColumn = targetSheet.Columns(0) ' column A
  sourceFile   = convertToURL("\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt")
  
  hF = FreeFile
  Open sourceFile For Input As hF
  
  While Not EOF(hF)
    Line Input #hF, currentLine
  Wend
  
  Close hF
  
  eRgs = targetColumn.queryEmptyCells
  u    = eRgs.Count - 1
  
  If u < 0 Then 
    MsgBox("No empty row in column.")
    Exit Sub
  End If
  
  eRg = eRgs(u)
  iCell = eRg.getCellByPosition(0, 0)
  
  'iCell.String = currentLine
  arrCurrentLine = Split(currentLine, Chr(9))
  
  Dim i As Long
  Dim numItems As Long
  
  i = 0
  numItems = Ubound(arrCurrentLine)
  Print numItems
  
  For i = 0 to numItems
    iCell.String = arrCurrentLine(i)
  Next i
  
End Sub
The last For loop puts the fields into the first empty cell, (logically) overwriting one value with the next.
I now need to figure how I move one cell to the right, until all fields have been 'dispensed' from the array 'arrCurrentLine'.
OpenOffice 4.1.5 on Windows 10
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: Read last line in TXT file and copy after last row in ca

Post by MaxG »

I am pulling my hair out :(
What are the properties of iCell in

Code: Select all

iCell = eRg.getCellByPosition(0, 0)
Where do I find the non-empty cell coordinates?
So, I can use these to increment the column value.

Any hints appreciated.
OpenOffice 4.1.5 on Windows 10
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: Read last line in TXt file and copy after last row in ca

Post by MaxG »

Alright...

My code now works in principle; except the insert happens in row 0 (the first row), rather than at the first empty row; in my case row 8.
The code section of interest is between ' ----------------------------------------

The problem sits here:

Code: Select all

  getFirstEmptyRow = eRgs.RowDescriptions(0)
  MsgBox "getFirstEmptyRow=" & getFirstEmptyRow
  Print cInt(getFirstEmptyRow)
getFirstEmptyRow is a String and shows as "8"
Once I cast it to an integer it is 0.
Print cInt(getFirstEmptyRow) --> "0"

Any hints appreciated.

Code: Select all

Sub DataFromFile
  ' reading txt, and adding last line to first empty row
  ' all indexes start at zero
  
  Dim eRgs As Object
  Dim iCell As Object
  Dim u As Integer
  
  myWorkBook   = ThisComponent
  'targetSheet  = myWorkBook.Sheets(3)   ' sheet 4
  targetSheet  = myWorkBook.Sheets.getByName("Sheet4")
  targetColumn = targetSheet.Columns(0) ' column A
  sourceFile   = convertToURL("\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt")
  sourceText   = FreeFile
  
  Open sourceFile For Input As sourceText
  
  ' go through source text file
  While Not EOF(sourceText)
    Line Input #sourceText, currentLine
  Wend
  ' currentLine contains at this point the last line
  
  Close sourceText
  
  eRgs = targetColumn.queryEmptyCells
  u    = eRgs.Count - 1
  ' u seems to be always 0; why?
  
  ' catch error: no rows left
  If u < 0 Then 
    MsgBox("No empty row in column.")
    Exit Sub
  End If
  
  eRg = eRgs(u)
  iCell = eRg.getCellByPosition(0, 0)
  
  'iCell.String = currentLine
  
  ' ----------------------------------------
  Dim i As Long
  Dim numItems As Integer
  Dim cell As Object
  
  i = 0
  arrCurrentLine = Split(currentLine, Chr(9))
  numItems = Ubound(arrCurrentLine)
  'MsgBox "numItems=" & numItems
  
  getFirstEmptyRow = eRgs.RowDescriptions(0)
  MsgBox "getFirstEmptyRow=" & getFirstEmptyRow
  Print cInt(getFirstEmptyRow)
  
  For i = 0 to numItems
    cell = targetSheet.getCellByPosition(i, getFirstEmptyRow)
    
    If i <> 0 then
      cell.Value = arrCurrentLine(i)
    Else
      cell.String = arrCurrentLine(i)
    End If
  Next i

  ' ----------------------------------------
End Sub
OpenOffice 4.1.5 on Windows 10
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: Read last line in TXT file and copy after last row in ca

Post by MaxG »

Alright... problem solved...
Thank you all for your input; much appreciated.

Here is the code, not elegant, but works; to address my initial scope:

Code: Select all

Sub DataFromFile
  ' reading txt, and adding last line to first empty row
  ' all indexes start at zero
  
  Dim eRgs As Object
  Dim iCell As Object
  Dim u As Integer
  
  myWorkBook   = ThisComponent
  'targetSheet  = myWorkBook.Sheets(3)   ' sheet 4
  targetSheet  = myWorkBook.Sheets.getByName("Sheet4")
  targetColumn = targetSheet.Columns(0) ' column A
  sourceFile   = convertToURL("\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt")
  sourceText   = FreeFile
  
  Open sourceFile For Input As sourceText
  
  ' go through source text file
  While Not EOF(sourceText)
    Line Input #sourceText, currentLine
  Wend
  ' currentLine contains at this point the last line
  
  Close sourceText
  
  eRgs = targetColumn.queryEmptyCells
  u    = eRgs.Count - 1
  ' u seems to be always 0; why?
  
  ' catch error: no rows left
  If u < 0 Then 
    MsgBox("No empty row in column.")
    Exit Sub
  End If
  
  'eRg = eRgs(u)
  'iCell = eRg.getCellByPosition(0, 0)
  'iCell.String = currentLine
  
  ' ----------------------------------------
  Dim i As Long
  Dim numItems As Integer
  Dim cell As Object
  
  i = 0
  arrCurrentLine = Split(currentLine, Chr(9))
  numItems = Ubound(arrCurrentLine)
  
  getFirstEmptyRowInColumn = Split(eRgs.RowDescriptions(0), " ")
  getFirstEmptyRow = cInt(getFirstEmptyRowInColumn(1)) - 1
  'MsgBox "getFirstEmptyRow=" & getFirstEmptyRow
  
  For i = 0 to numItems
    cell = targetSheet.getCellByPosition(i, getFirstEmptyRow)
    
    If i <> 0 then
      cell.Value = arrCurrentLine(i)
    Else
      cell.String = arrCurrentLine(i)
    End If
  Next i
  ' ----------------------------------------
End Sub
OpenOffice 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Read last row in .txt + copy after last row in

Post by Villeroy »

well, it's your data you are putting at risk.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: [Solved] Read last row in .txt + copy after last row in

Post by MaxG »

Villeroy wrote:well, it's your data you are putting at risk.
Because...?
OpenOffice 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Read last row in .txt + copy after last row in

Post by Villeroy »

Because you copy together some text snippets without understanding what they do.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MaxG
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

Re: [Solved] Read last row in .txt + copy after last row in

Post by MaxG »

It's not a commercial product.
It's not a language I want to learn.
It's a minimal viable product I am happy with.
It does what it is supposed to do.
I am dandy.

And just for sh!ts and giggles, here the code I finally ended up with...
... it does not only do the last line, but any record that's missing, if I miss a few dates.

Code: Select all

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
  sourceFile   = convertToURL("\\RPI32\maxg\sppro\PowerFlowSummaryLog.txt")
  sourceText   = FreeFile
  
  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
OpenOffice 4.1.5 on Windows 10
Post Reply