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