Ask Your Question
0

Convert text to column and import the data to another spreadsheet

asked 2018-12-05 11:43:54 +0100

itsahmedfiroz gravatar image

updated 2018-12-05 12:02:06 +0100

JohnSUN gravatar image

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
edit retag flag offensive close merge delete

Comments

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?

JohnSUN gravatar imageJohnSUN ( 2018-12-05 12:04:51 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-12-05 13:19:30 +0100

Lupp gravatar image

updated 2018-12-06 23:49:04 +0100

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)
...
edit flag offensive delete link more

Comments

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

itsahmedfiroz gravatar imageitsahmedfiroz ( 2018-12-06 05:47:59 +0100 )edit

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

Lupp gravatar imageLupp ( 2018-12-06 11:04:28 +0100 )edit

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.

Lupp gravatar imageLupp ( 2018-12-06 23:43:54 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-05 11:43:54 +0100

Seen: 30 times

Last updated: Dec 06