[Solved] Calc Macro Issues - Comma separated text to rows

[SilentGH]

I have spent a number of hours recently learning OpenOffice Basic in order to create a macro to take comma separated values in the 1st column of a spreadsheet & put each value in its separate row.

Basically, I'm trying to turn this...

BOM.jpg (22.87 KiB) Viewed 7577 times

To this...

Separated BOM.jpg (54.41 KiB) Viewed 7577 times

This is the code I've come up with...

Code: Select all

Sub Main

Doc = ThisComponent
Sheet = Doc.Sheets(0)
c = Sheet.createCursor
c.gotoEndOfUsedArea(false)

'Variable declarations
Dim iCol, iRow As Integer     'Counter variables for loops
Dim ColArray() As String      'Store all columns in a single row
Dim FirstColArr() As String   'Hold each comma separated value in 1st column
Dim DataArray(0,0) As String  'Store all values collected from sheet

'Stores number of rows & columns filled with data
LastRow = c.RangeAddress.EndRow
LastCol = c.RangeAddress.EndColumn

'Loop that repeats for all the rows that contain data
For row = 0 To LastRow
	'Store all columns of a single row
	For col = 0 To LastCol
		ColArray(col) = Sheet.getCellByPosition(col, row).String
	Next col
	
	'Store each value in 1st column thats separated by a comma
	FirstColArr = split(ColArray(0), ",")
	'Remove any spaces prior to each value stored
	For i = Lbound(FirstColArr) To Ubound(FirstColArr)
		If Left(FirstColArr(i), 1) = " " Then
			FirstColArr(i) = Mid(FirstColArr(i), 2, 15)
		End If
	Next i
	
	'Store all columns of a single row of data for each comma
	'separated value of 1st column
	For n = 0 To Ubound(FirstColArr) 
		Redim Preserve DataArray(iCol, iRow)
		DataArray(0, iRow) = FirstColArr(n)
		For iCol = 1 To LastCol
			DataArray(iCol, iRow) = ColArray(iCol)
		Next iCol    
		iRow = iRow + 1
	Next n
Next row

iRow = iRow - 1
For y = 0 To iRow
	For iCol = 0 To LastCol
  		Sheet.getCellByPosition(iCol, y).String = DataArray(iCol, y)
  	Next iCol
Next y

End Sub

However, it keeps coming up with the error Inadmissible value or data type, Index out of defined range. I've been messing with it long enough to find that if I manually set the number of columns its looking at by changing the following...

Dim ColArray() As String
to
Dim ColArray(3) As String in the variable declarations

AND

Redim Preserve DataArray(iCol, iRow)
to
Redim Preserve DataArray(3, iRow)

then it'll work fine. But, then its also locked to only looking at 4 columns instead of a variable number of columns & rows. I can't figure this out for the life of me. Any idea what I'm doing wrong here?

[Zizi64]

I can not see your attached pictures now.

[robleyd]

@SilentGH

Hi, and welcome to the community forum.
On which line is the error appearing?

@Zizi64

Might be something at your end as they are still appearing for me; tried clearing my cache just to make sure I wasn't seeing a cached version...

[Zizi64]

All of other attached pictures appeared in my Firefox browser, except yours.

The "name" (or link?) of your pictures are:

Code: Select all

https://lh3.googleusercontent.com/JejZg0dHYLsPnH-z3lEDnpJoCzaaVhojUgZ1YUkikVLPtRiXXF47degiN27ldUukXBl4V0DDwjNc0_868XCeAxiOUIXa3_gz-Cu7uKno2KqEtX1ZHdbCJOS1au-qypnuuvxsXrn4yFP3CHwYwuXDSz6vf3-yg7HsLmswkYSqx04LMgCpGJSDRfWwI7kZSGV_HAKGRqhME3ULktNSIPZEsiVU3HtM4Uxmu-GbZY5mUWV-2hq2jrH1hh_JbagjE3HsgUl6h--RmogxQF297zS42T5OlamEV8NTA4A_3LPUpD9NYYG8-SQhypRDnegfpCFy7v55--sTsKWOFArmbZ0s-F9C1aWkve6TR13kcb-5ZVgbsxQ3uDXWtdfEfRg_N2tNUiL2VhdtIIO5ons1uu01Mk3AAYOE0jxxNYXv5ucbORkHZZYS_7Vke_-WUiNle1Q2gRHLgoIieZKqbPnMyW1bslPyk9E_OT6t93OlNt1LzEfSS-s91M757Wg8OyTO6TgN573M4CzMMbEvtMyQw4WeL4bqHNR5XoZAodky44ZB7aHYznJjDXS-fbKFOVMDLdySYjcSaj8FVdaxWkmw1G_nfGnSteJELna4koUsTbIKrdvaR4HmPkR6=w368-h87-no

and

Code: Select all

https://lh3.googleusercontent.com/EhZAYuEOWUKZOK4yENj4FepB0bN82caYOlDBWgiDA2QLS4eQZmiGyRe1Whz98lGNPCz7767rfpaG3nyXEfer-O2fSox2o2mrNkFCG0_Chxbw0hY_WbaZz4KiwCDj40TOqdB3hIGE70xOuUIrJJ1xuYR1XpV_TELcrGTWeFsdqoksgDJjjUCnFael7PUOGMcSUJIFTltm43t3aG1n-tE-Gi16Xrda3BKDxVedsxJcuXOFbLKxIARyTJVigsiwHwax1V9lqIGSR7QUBaQ4HXPwFNYWovEn0NU7cdyiW3i8GbGPeYkjo6xK9MuEbP6pfZIw3D_1Y4wGQAPgZQXwD3TweiCR8vc09N6t2djutATiyR4RZDNYWg8o4wybEcaPFq0b_skFhNJD-j1s7Kq3mlom_wG1-L4N8Y4UWKtX-shSzNLHqE2UHP_thQjNKhSDrbGybZDS9hIm2fZGvLeVRSHz41Sug6-qLkElgMv-RQCaZIynmTTtneV_PF8EYSEbIy6OPT6Hpo90RBA5cXazk3Xa3r0TcRtahn7cbYanOwqmLK2hfnBSYPUMxeAfUwzVA_VKB1LzzutwXKVTvCkAhLa4l7_gzUWhscnCz_xAV3xMncxj8PwFf5mW=w368-h223-no

I think, they have too long names. They are not clickable, not visible.

Appeared something similar "picture":
Saved as...:

Nothing.png (2.59 KiB) Viewed 7604 times

PrntScr:

[robleyd]

Images now disappeared completely and replaced by the text Image.

@SilentGH
[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire. The link also contains information on how to anonymise your document if it contains confidential information.

Also works for images; perhaps you could edit your original post and use this method to upload your screen captures?

[SilentGH]

Sorry about that; this is my first time using forums, I was worried that might happen I edited the original post & attached the screenshots. They are both below the 125kb limit, in JPG format. Hopefully they should show up now.

@robleyd

The error is showing up on the line ColArray(col) = Sheet.getCellByPosition(col, row).String

[Zizi64]

The error is showing up on the line ColArray(col) = Sheet.getCellByPosition(col, row).String

And what is the Error message?

[SilentGH]

It keeps coming up with the error Inadmissible value or data type, Index out of defined range.

[MrProgrammer]

Basically, I'm trying to turn this {picture}
To this {picture}

It is far more helpful to attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply), and not a picture instead of the document itself. I won't respond further to this post until you make an attachment.

I have spent a number of hours recently learning OpenOffice Basic in order to create a macro

I wish I had a dollar for every hour beginners have wasted trying to write evil macros when their goal could be accomplished much faster using the standard features of OpenOffice. The XY Problem

I [want] to take comma separated values in the 1st column of a spreadsheet & put each value in its separate row.

All you need to do is create simple formulas in columns E, F, and G, then use Text to Columns. Open the attachment, click the G column header, Data → Text to Columns → OK.

201709101325.ods

(8.28 KiB) Downloaded 291 times

Edit: Better: Use two columns. E1 set to =CHAR(9)&B1&CHAR(9)&C1&CHAR(9)&D1 then fill down. F1 set to =SUBSTITUTE(A1;", ";E1&CHAR(10))&E1 then fill down. Use Text to Columns on F. This is much simpler than a 46-line macro.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Villeroy]

I know at least 4 different methods to import csv into this office suite, into spreadsheets, databases, text documents or via databases into spreadsheets and text documents. None of them requires any macro code. Each of them could be accomplished by some kind of macro though.
If you really want to become a programmer you should definitively NOT start with a dead language. All flavours of Basic have died many years ago. Modern languages can do anything you want without depending on any office suite at all. Some modern (scripting) languages are easier to learn because they are better streamlined and because there are by far more resources and useful code snippets. StarBasic (the Basic flavour you try to use) has a community of may be ten thousand people world wide. Python and Java on the other hand are most popular programming languages with many millions of adepts. In the context of this office suite you can use Python and Java to extend the office suite with new types of objects (which you can't do with Basic), you can write macros similar to Basic macros and you can write stand-alone programs that can communicate with this office suite. Oh, and Python comes with its own module to parse csv files: https://docs.python.org/2/library/csv.html. There are modules to parse many types of files, send and receive mail, run servers, use servers, do statistics, astronomy and everything you can dream of. Search for recent publications on Visual Basic. Only beginners books. Nothing which covers any practical or theoretical use case for this particular language. There are still Basic books for beginners because in Windows environments you may stumble upon this clumsy old fashioned stuff.

[SilentGH]

It is far more helpful to attach a document and not a picture instead of the document itself ... I wish I had a dollar for every hour beginners have wasted trying to write evil macros when their goal could be accomplished much faster using the standard features of OpenOffice.

I posted the pictures merely as an example to portray what I was trying to get the code to do, & was wanting to get some pointers on the code specifically in case I was making an obvious syntax error or something else that a more experienced individual would be able to easily point out. The code does work if you make the changes I outlined in the original post, its just locked to a set number of columns. I mostly spent the time because I got carried away with the interest in exercising my rusted (& limited) coding experience from my college days...

I didn't realize it was so easy to accomplish it with formulas & Text To Columns though; thanks for the help!

If you really want to become a programmer you should definitively NOT start with a dead language.

Yeah, I began to realize that soon after I posted the topic. I would love to try the same task or others with Java just as a personal project for learning. Maybe I'll start digging through that whole world... Thanks for the tips.

[Zizi64]

Villeroy wrote:
If you really want to become a programmer you should definitively NOT start with a dead language.

BASIC = Beginner's All-purpose Symbolic Instruction Code

Beginners will always be there.

[Villeroy]

Villeroy wrote:
If you really want to become a programmer you should definitively NOT start with a dead language.

BASIC = Beginner's All-purpose Symbolic Instruction Code

Beginners will always be there.

This acronym was adequate in the early days of computing. Modern languages are easier to learn. Learning a historic computer language for the only purpose to control an office suite is unproductive.

[Zizi64]

This acronym was adequate in the early days of computing. Modern languages are easier to learn. Learning a historic computer language for the only purpose to control an office suite is unproductive.

Yes, I agree... but:
Only one more question...
Why not has AOO and LO a working, easy-to-use IDE (like the StarBasic IDE) for the "Modern languages"?

[Villeroy]

This acronym was adequate in the early days of computing. Modern languages are easier to learn. Learning a historic computer language for the only purpose to control an office suite is unproductive.

Yes, I agree... but:
Only one more question...
Why not has AOO and LO a working, easy-to-use IDE (like the StarBasic IDE) for the "Modern languages"?

Because VBA is the only macro language for MS Office and StarBasic looks like a sweet promise that this is somehow the same thing. We all know it isn't because the language is not the thing you talk to. The same sweet promise emerges when an Access user opens a Base document for the first time.
Excel '93 and '95 had a VBA IDE that was very similar to our StarBasic IDE which exists since StarOffice 5. From Excel '97 to 2003 the VBA IDE had been massively improved whereas our IDE is still the same as in 1995. Any other IDE is better than the StarBasic IDE. After you have learned the Python language or Java, you know your favourite IDE or simple code editor. For quick adjustments of Python code on Windows systems with no decent code editor I use Writer with white space highlitning.
Outside MS Office there is no progress in Visual Basic. MS gave up on VB after it took them too many years to rewrite VBA for the Mac. Office 2007 for the Mac had no working macro language because VBA was never designed to be used on non-Windows systems. This language has no future but they've got to maintain it for all the "VBA experts" who use VBA without ever having learned how to program. We see them quite often on this forum.