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

Creating a macro - Writing a Script - Using the API

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

Postby MaxG » Fri Jan 18, 2019 8:34 am

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   Expand viewCollapse view
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   Expand viewCollapse view
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
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

Postby JeJe » Fri Jan 18, 2019 11:12 pm

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby Lupp » Sat Jan 19, 2019 12:14 am

Elöderly code! The Line Input wouldn't work witout the # chartacter, I suppose.
Code: Select all   Expand viewCollapse view
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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2402
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

Postby MaxG » Sat Jan 19, 2019 1:45 am

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   Expand viewCollapse view
  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   Expand viewCollapse view
  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
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

Postby JeJe » Sat Jan 19, 2019 1:53 am

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   Expand viewCollapse view

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

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby Villeroy » Sat Jan 19, 2019 2:33 am

Works without office suite:
Code: Select all   Expand viewCollapse view
$ 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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26623
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby JeJe » Sat Jan 19, 2019 2:37 am

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby MaxG » Sat Jan 19, 2019 3:06 am

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   Expand viewCollapse view
  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   Expand viewCollapse view
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   Expand viewCollapse view
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
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

Postby Villeroy » Sat Jan 19, 2019 3:17 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26623
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MaxG » Sat Jan 19, 2019 3:31 am

JeJe wrote:tab is Chr(9). You can find these values in a table of character codes or msgbox Asc(" ")
Code: Select all   Expand viewCollapse view
txtArray = Split(CurrentLine,"   ") 'its a tab in between the quotes.


Yes, thanks, I realised this line with Chr(9)...
Code: Select all   Expand viewCollapse view
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

Postby MaxG » Sat Jan 19, 2019 3:37 am

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
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

Postby Villeroy » Sat Jan 19, 2019 4:02 am

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   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26623
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MaxG » Sat Jan 19, 2019 5:12 am

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   Expand viewCollapse view
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

Postby MaxG » Sat Jan 19, 2019 6:22 am

I am pulling my hair out :(
What are the properties of iCell in
Code: Select all   Expand viewCollapse view
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

Postby MaxG » Sat Jan 19, 2019 8:07 am

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   Expand viewCollapse view
  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   Expand viewCollapse view
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

Postby MaxG » Sat Jan 19, 2019 10:57 am

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   Expand viewCollapse view
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
MaxG
 
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

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

Postby Villeroy » Sun Jan 20, 2019 2:37 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26623
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MaxG » Sun Jan 20, 2019 3:15 pm

Villeroy wrote:well, it's your data you are putting at risk.


Because...?
OpenOffice 4.1.5 on Windows 10
MaxG
 
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am

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

Postby Villeroy » Sun Jan 20, 2019 3:21 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26623
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MaxG » Mon Jan 21, 2019 10:33 am

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   Expand viewCollapse view
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
MaxG
 
Posts: 13
Joined: Fri Jan 18, 2019 8:00 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests