[Solved] Macro to export Calc cells as plain text .yml file?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
carmeops
Posts: 4
Joined: Sat Oct 18, 2014 3:16 pm

[Solved] Macro to export Calc cells as plain text .yml file?

Post by carmeops »

Hello everyone, i'm not well versed into macros, but i could really use one here, if someone would be kind enough to explain me how to create it

here is my situation:
I have a calc document with only 1 sheet, in that sheet is only 2 row of text (tons of columns tho)

i need to paste the content of each cells from the second row into their own separated .yml file, created files being named by the content of the first row cell above it + .yml

and i have no idea how to do that .. please save me from hand copy pasting and creating hundreds of files myself :knock:
Last edited by carmeops on Sat Oct 18, 2014 11:29 pm, edited 2 times in total.
OpenOffice 3.4.1
Windows 7 pro 64
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Macro to export calc cells as plain text .yml files?

Post by JohnSUN-Pensioner »

Maybe you find the answers in these discussions - here or here
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
carmeops
Posts: 4
Joined: Sat Oct 18, 2014 3:16 pm

Re: Macro to export calc cells as plain text .yml files?

Post by carmeops »

i saw them, but they are rather to turn a whole sheet into a txt file

what i need is 1 cell = 1 file, from B1 to B192 with file name A1 to A192 i don't know where to start from with those two discussions
OpenOffice 3.4.1
Windows 7 pro 64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to export calc cells as plain text .yml files?

Post by Villeroy »

If you can not write any programs you can not do it.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Macro to export calc cells as plain text .yml files?

Post by JohnSUN-Pensioner »

Villeroy absolutely right. You can start to learn programming languages​​. Or you can expect that someone will write the macro for you. But the verbal description of the problem for this little. Show the sample file of source data... Show the file with the desired result...
carmeops wrote:i don't know where to start from
Start can be

Code: Select all

Sub ExportAll
Dim oCell As Object
Dim oCursor As Object
Dim aData As Variant
Dim aFNames As Variant
Dim aDataToFiles As Variant
Dim i&
	oCell = ThisComponent.getCurrentController().getActiveSheet().GetCellbyPosition(0, 0)
	oCursor = oSheet.createCursorByRange(oCell)
	oCursor.GotoEndOfUsedArea(True)
	aData = oCursor.getDataArray()
	aFNames = aData(0)
	aDataToFiles = aData(1)
	For i=LBound(aFNames) To UBound(aFNames)
REM ...write data from aDataToFiles(i) to file with name aFNames(i)...
	Next i
End Sub
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
carmeops
Posts: 4
Joined: Sat Oct 18, 2014 3:16 pm

Re: Macro to export calc cells as plain text .yml files?

Post by carmeops »

JohnSUN-Pensioner wrote:Show the sample file of source data... Show the file with the desired result...
not sure how to do that, also the forum doesn't let me upload .yml file, so i changed it .txt

here is the content of the 2 first cells

A1:

Code: Select all

ArcticShop
A2:

Code: Select all

ShopName: ArcticShop
signs:
  text: '[ArcB]'
  NeedPermissionToCreateSign: true
DisplayName: Arctic &2Buy
shop:
  Chapter:
    ExtraPermission: ''
    MenuItem:
    - name:&2Book Chapter
    - lore:&cAccess to this Shop menu#&cfrom the &4&lSky Eden &6Book
    - id:386
    - amount:1
    InventoryLocation: 5
    Message: '&4You can now access this shop from the book'
    PriceType: free
    RewardType: permission
    Reward:
    - Boss.Arc
  Back:
    ExtraPermission: ''
    MenuItem:
    - name:&4Back to Main menu
    - lore:Back to Main menu
    - type:REDSTONE
    - amount:1
    InventoryLocation: 1
    Message: ''
    PriceType: free
    RewardType: shop
    Reward: Menu
  Switch:
    ExtraPermission: ''
    MenuItem:
    - name:&4Switch
    - lore:To the Sell version of the shop
    - id:51
    - amount:1
    InventoryLocation: 9
    Message: ''
    PriceType: free
    Reward: ArcticShopS
    RewardType: shop
  Next:
    ExtraPermission: ''
    MenuItem:
    - name:&6Next Page
    - lore:Change Page
    - id:339
    - durability:1
    - amount:1
    InventoryLocation: 7
    Message: ''
    PriceType: free
    Reward: ArcticShop8
    RewardType: shop
  Previous:
    ExtraPermission: ''
    MenuItem:
    - name:&6Previous Page
    - lore:Change Page
    - id:339
    - amount:1
    InventoryLocation: 3
    Message: ''
    PriceType: free
    Reward: ArcticShop64
    RewardType: shop
  Coal:
    ExtraPermission: ''
    MenuItem:
    - id:263
    - durability:0
    - lore:$9.26
    - amount:1
    InventoryLocation: 10
    RewardType: item
    Reward:
    - - id:263
      - durability:0
      - amount:1
    PriceType: money
    Price: 9.26
  FireCharge:
    ExtraPermission: ''
    MenuItem:
    - id:385
    - durability:0
    - lore:$43.53
    - amount:1
    InventoryLocation: 11
    RewardType: item
    Reward:
    - - id:385
      - durability:0
      - amount:1
    PriceType: money
    Price: 43.53
  Ice:
    ExtraPermission: ''
    MenuItem:
    - id:79
    - durability:0
    - lore:$12.5
    - amount:1
    InventoryLocation: 12
    RewardType: item
    Reward:
    - - id:79
      - durability:0
      - amount:1
    PriceType: money
    Price: 12.5
  PackedIce:
    ExtraPermission: ''
    MenuItem:
    - id:174
    - durability:0
    - lore:$25
    - amount:1
    InventoryLocation: 13
    RewardType: item
    Reward:
    - - id:174
      - durability:0
      - amount:1
    PriceType: money
    Price: 25
  SnowBlock:
    ExtraPermission: ''
    MenuItem:
    - id:80
    - durability:0
    - lore:$50.4
    - amount:1
    InventoryLocation: 14
    RewardType: item
    Reward:
    - - id:80
      - durability:0
      - amount:1
    PriceType: money
    Price: 50.4
  Snowtile:
    ExtraPermission: ''
    MenuItem:
    - id:78
    - durability:0
    - lore:$14
    - amount:1
    InventoryLocation: 15
    RewardType: item
    Reward:
    - - id:78
      - durability:0
      - amount:1
    PriceType: money
    Price: 14
  Snowball:
    ExtraPermission: ''
    MenuItem:
    - id:332
    - durability:0
    - lore:$12.6
    - amount:1
    InventoryLocation: 16
    RewardType: item
    Reward:
    - - id:332
      - durability:0
      - amount:1
    PriceType: money
    Price: 12.6

and the expected result in the attachments

all the file names are in the cells A1 to GJ1 and all the content for those files are in the cells A2 to GJ2

i tested this code:

Code: Select all

Sub CopyCellTextToCalc 

Doc = ThisComponent 
oSheet=thiscomponent.getcurrentcontroller.activesheet 
oCell = ThisComponent.getCurrentSelection() 
TestoCella = oCell.String 
Dim oDoc As Object 
Dim oDocProperties(0) as new com.sun.star.beans.PropertyValue 
oDoc = ThisComponent 
oDocProperties(0).Name = "Hidden" 
oDocProperties(0).Value = True 
oDocDic = StarDesktop.loadComponentFromURL( convertToURL("C:\ArcticShop.yml") , "_blank", 0, oDocProperties() ) 
oText = oDocDic.getText() 
oCursor = oText.CreateTextCursor() 
oCursor.gotoEnd(False) 
oText.insertString(oCursor, oCell.String & chr(13), false) 
oDocDic.store 
oDocDic.close(False) 
End Sub 
rem --------------------------------------------------------------------------
but it only add content to 1 file, i need something that create the file and the content in it, and do that for all the cells in the second row, with different filename for each
Attachments
ArcticShop.txt
(3.33 KiB) Downloaded 200 times
OpenOffice 3.4.1
Windows 7 pro 64
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Macro to export calc cells as plain text .yml files?

Post by JohnSUN-Pensioner »

OK, let it be so:

Code: Select all

Sub ExportAll
Dim oSheet As Object
Dim oCell As Object
Dim oCursor As Object
Dim aData As Variant
Dim aFNames As Variant
Dim aDataToFiles As Variant
Dim i&
Dim iNumber As Integer
Dim fName$
Const PathToNewFiles="C:\temp\"
Const extYML=".yml"
	oSheet = ThisComponent.getCurrentController().getActiveSheet()
	oCell = oSheet.GetCellbyPosition(0, 0)
	oCursor = oSheet.createCursorByRange(oCell)
	oCursor.GotoEndOfUsedArea(True)
	aData = oCursor.getDataArray()
	aFNames = aData(0)
	aDataToFiles = aData(1)
	For i=LBound(aFNames) To UBound(aFNames)
		iNumber = Freefile
		fName = ConvertToUrl(PathToNewFiles+aFNames(i)+extYML)
		Open fName For Output As #iNumber
		Print #iNumber, aDataToFiles(i)
		Close #iNumber
	Next i
End Sub
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
carmeops
Posts: 4
Joined: Sat Oct 18, 2014 3:16 pm

Re: Macro to export calc cells as plain text .yml files?

Post by carmeops »

It worked perfectly, wonderfully, i love you man, if you were next to me irl, i would kiss you

you just saved my next few week-ends, thanks
OpenOffice 3.4.1
Windows 7 pro 64
Post Reply