Convert text to column and import the data to another spreadsheet

I have a text file that needs the data to be converted to columns into a spreadsheet based on the delimiter ‘|’ that’s present in the text file . I have written a macro that opens the text file and directly produces the results in column of a spreadsheet . But i would like the entire data of the obtained spreadsheet be copied to another spreadsheet file that has 4 different sheets . How can i copy the contents of the generated sheet to another spreadsheet file with the help of a macro ?

This is my current macro :

Sub Pipes_Open()
Dim filePath as String
filePath = InputBox("Enter Text file Path")
if filePath = vbNullString then exit sub
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 = "124,0,0,1,1/2/2/2/3/2/4/2/5/2/6/2/7/2"    'ASSCII 124 = |
FileURL = convertToURL(filePath)
oCSV = StarDesktop.loadComponentFromURL(FileURL, "_blank",0,Propval()) 
End Sub

vbNullString? Do you mean “empty string”? So, why not If filePath = "" Then...?

In which place of which of the four sheets you want to copy this data?

You can do it with the help of .uno: commands.
The codelines below are not tested (“Written on dry ground.”)
Errors expected!
===Editing 2018-12-06 23:50 (UTC+01:00)===
Errors were present. They were commented and corrected in the below code.

...
dispH = CreateUnoService("com.sun.star.frame.DispatchHelper")
sourceSheet = oCSV.Sheets(0)
sourceRangeStart = sourceSheet.GetCellByPosition(0, 0)
rangeCursor = sourceSheet.CreateCursorByRange(sourceRangeStart)
rangeCursor.GotoEndOfUsedArea(True)
oCSV.CurrentController.Select(rangeCursor)
frCSV = oCSV.CurrentController.Frame
dispH.ExecuteDispatch(frCSV, ".uno:Copy", "", 0, Array()) REM Correction: Two parameters inserted.
otherUrl = "/FolderPath/targetFile.ods"
REM Assignments are examples:
targetSheetNum = 3
targetCellName  = "Z1"
REM ... Change as you need it.
otherDoc = StarDesktop.LoadComponentFromUrl(otherUrl, "_blank", 0, Array())
targetSheet = otherDoc.Sheets(targetSheetNum)
targetCell = targetSheet.GetCellRangeByName(targetCellName)
otherDoc.CurrentController.Select(targetCell)
frOther = otherDoc.CurrentController.Frame
dispH.ExecuteDispatch(frOther, ".uno:Paste", "", 0, Array()) REM Correction: Two parameters inserted.
otherDoc.Store()
otherDoc.Close(True)
...

Another way avoiding the “slot machine” dispatcher is to use the DataArrays:

...
sourceSheet = oCSV.Sheets(0)
sourceRangeStart = sourceSheet.GetCellByPosition(0, 0)
rangeCursor = sourceSheet.CreateCursorByRange(sourceRangeStart)
rangeCursor.GotoEndOfUsedArea(True)
otherUrl = "/FolderPasth/targetFile.ods"
REM Assignments are examples:
targetSheetNum = 3
targetCellX  = 25 : targetCellY = 0
REM ... Change as you need it.
otherDoc = StarDesktop.LoadComponentFromUrl(otherUrl, "_blank", 0, Array())
targetSheet = otherDoc.Sheets(targetSheetNum)
REM Faulty original LOC (2 serious errors): With sourceRange
REM Corrected LOC:
With rangeCursor.RangeAddress
  targetRange = targetSheet.GetCellRangeByPosition( _
            targetCellX, targetCellY, _
            targetCellX + .EndColumn - StartColumn, targetCellY + .EndRow - .StartRow)
End With
targetRange.SetDataArray(rangeCursor.GetDataArray())
otherDoc.Store()
otherDoc.Close(True)
...

@Lupp Can you please test your code . i have tried and it throws error telling that Unsupported URL

(Guessing a probable cause:)
The line of code
otherUrl = "/FolderPath/targetFile.ods"
is just an “abstract” example. Things of this kind you have to adapt to your needs.
If you report errors when running scripts always execute stepwise, watch the critical variables, indicate the line, give the full exact message …
It’s your turn to make help possible.

First version of code:
Twice the same careless mistake: Two next-to-unused parameters omitted in ExecuteDispatch() calls.
See correction by editing.
Second version of code:
See correction by editing.

Typical errors occurrung when writing code outside the IDE and without testing.