Need to know how to create simple text file using BASIC

Keyboard macros or custom scripts

Need to know how to create simple text file using BASIC

Postby jeffkan1 » Mon Sep 13, 2010 11:24 pm

Hello, I'm using Lotus Symphony Spreadsheets (a Calc document) and I have a need to create a simple text file from a macro. The file's name would be from a cell on the spreadsheet.

I'm fairly new to OpenOffice BASIC and its complexity. I get the idea of "ThisComponent" referring to the document that holds the macro. I have been searching for a couple of hours and have had no luck in finding how to create the text file, give it a file extension of something other than .txt, write text to the file, and save it. I did find the loadComponentFromURL( ) method, but I was unable to determine if that would help me create a simple text file.

To give you an idea of what Excel VBA does, here's some code from it:

Code: Select all   Expand viewCollapse view
    Dim sheet_name1 As String
    Dim file_name1 As String
    Dim sql As String
    Dim intFileNo As Integer
    Dim length As Integer
       
    sheet_name1 = Worksheets("TOP").Cells(20, 2)
    file_name1 = Worksheets("TOP").Cells(6, 3) & sheet_name1 & ".ddl"
   
    intFileNo = FreeFile
    Open file_name1 For Output As #intFileNo
    . . . .
    Print #intFileNo, sql    ' writes the text in sql to the file
    . . . .
    Close intFileNo


Anyone have some suggestions for the code needed to create the text file, write to it and save it? I would even settle for a way to create a Writer document, write to it from the (Calc) macro, and save it.

Thanks!
OpenOffice ?.? and Lotus Symphony 1.3 on Windows XP
jeffkan1
 
Posts: 3
Joined: Mon Sep 13, 2010 10:15 pm

Re: Need to know how to create simple text file using BASIC

Postby jeffkan1 » Tue Sep 14, 2010 8:49 pm

Update... I've got code in my Lotus Symphony spreadsheet macro that will create a file and write text to it. It all works as needed. However, when the code has completed, I get this error:

“org.eclipse.swt.SWTException An error has occurred. See error log for more details. Widget is disposed”

Any ideas?

Here's the code:
Code: Select all   Expand viewCollapse view
   Dim oSFA As Object, oOutStream as Object, oOutText as Object
   Dim sFilePath as String
   oSFA = createUNOService ("com.sun.star.ucb.SimpleFileAccess")
    sFilePath = "C:\basefile.ddl"
    If oSFA.exists(sFilePath) Then
      oSFA.kill(sFilePath) 'if file exists, delete it
    End If
   
   oOutStream = oSFA.openFileWrite(sFilePath)
   oOutText = createUNOService ("com.sun.star.io.TextOutputStream")
   oOutText.setOutputStream(oOutStream)

   oOutText.WriteString("Hello World")
   oOutText.closeOutput()
OpenOffice ?.? and Lotus Symphony 1.3 on Windows XP
jeffkan1
 
Posts: 3
Joined: Mon Sep 13, 2010 10:15 pm

Re: Need to know how to create simple text file using BASIC

Postby rudolfo » Wed Sep 15, 2010 2:02 pm

I don't know why macro writers often overlook the build in help.
Macros and Programming -> Command Reference is a good resource when working with BASIC:
Code: Select all   Expand viewCollapse view
Sub ExamplePrint
Print "ABC"
Print "ABC","123"
i = FreeFile()
Open "C:\Temp.txt" For Output As i
Print #i, "ABC"
Close #i
end Sub

This example is from there and about writing to a file (handle). If you know VBA it should look familiar to you.

"org.eclipse.swt" looks rather like an error from the Gui Toolkit (maybe a button that is used to start the macro)?
How do you run the macro? You might need a wrapper around the pure OOo related code if the Gui expects a certain return code "true" or "false". Just some ideas ...
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Need to know how to create simple text file using BASIC

Postby jeffkan1 » Wed Sep 15, 2010 5:03 pm

Thank you for the assistance Rudolfo.

I do use the built-in help. Even when I'm told something is in the help area, I can't find it. I looked for the above example you referenced, and I can only find this, for the PRINT statement:
Code: Select all   Expand viewCollapse view
Sub ExamplePrint
Print "ABC"
Print "ABC","123"
end Sub


It's clear to me that the help provided with Lotus Symphony for writing macros is either constructed poorly, lacking in content, or both. I've found the most helpful content on OOo forums.

The FreeFile command looks to be exactly what I need. It creates the file if it didn't previously exist, and it overwrites what was there if it did exist. On the OpenOffice.org site, I found the below example:

Code: Select all   Expand viewCollapse view
Dim FileNo As Integer
Dim Filename As String

Filename = "c:\data.txt"            ' Define file name
FileNo = FreeFile               ' Establish free file handle

Open Filename For Output As #FileNo         ' Open file (writing mode)
Print #FileNo, "This is a line of text"      ' Save line
Print #FileNo, "This is another line of text"   ' Save line
Close #FileNo                  ' Close file
OpenOffice ?.? and Lotus Symphony 1.3 on Windows XP
jeffkan1
 
Posts: 3
Joined: Mon Sep 13, 2010 10:15 pm

Re: Need to know how to create simple text file using BASIC

Postby rudolfo » Wed Sep 15, 2010 9:28 pm

Actually your code with the UNO io.TextOutputStream looked okay for me.
But maybe some of the loaded UNO objects did not finalize cleanly internally. So it should be worth a try with the "FreeFile" and "Print" aproach.

BTW, with the statement about the online help I had also myself in mind. I had a time where any problem or open question when writing a macro triggered a "Okay, I have to look in the Uno API for this, won't find it elsewhere" in my mind. And it might well be that the content of the online help has improved. Actually the example that you have found on the OpenOffice.org site is in the Online help of 3.1.1 (for FreeFile, not for Print). Well, less (read this as no) comments and the file handle variable is named #iNumber ...
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Need to know how to create simple text file using BASIC

Postby zabolyx » Mon Nov 29, 2010 11:35 pm

You could also go the cheap route of opening a hidden calc document and writing to that. Each row you write in will be it's own line in the resulting txt file.

Then save it as the file name you desire with the filters as txt and without delimiters. I create a script file for iMacros with this method.

Code: Select all   Expand viewCollapse view
'store listings data form CRM creation
Global sAgent(0) as String
Global sHash(0) as String
Global sIDs(0) as String
Global sDateEmailed(0) as String
Global sHeader(0) as String
Global sFooter(0) as String
Global sLogo(0) as String
Global sMetro(0) as String

Sub mCRMCreateFile
   
   'stores the script doc's open settings
   Dim oCRMDocOptions(0) as New com.sun.star.beans.PropertyValue
   
   'stores the document objects
   Dim oCRMDoc as Object
   Dim oCRMSheet as Object
   
   'setup the settings to open the crm script file with
   oCRMDocOptions(0).Name = "Hidden"
   oCRMDocOptions(0).Value = True
   
   'open a blank spreadsheet to build the script with
   oCRMDoc = starDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, oCRMDocOptions())
   
   'setup the first sheet of the new doc
   oCRMSheet = oCRMDoc.sheets().getByIndex(0)
   
   'write the starter and login data to the file
   oCRMSheet.getCellRangeByName("A1").String = oInternalSettingsSheet.getCellRangeByName("L137").String
   oCRMSheet.getCellRangeByName("A2").String = oInternalSettingsSheet.getCellRangeByName("L138").String
   oCRMSheet.getCellRangeByName("A3").String = oInternalSettingsSheet.getCellRangeByName("L139").String
   oCRMSheet.getCellRangeByName("A4").String = oInternalSettingsSheet.getCellRangeByName("L140").String
   oCRMSheet.getCellRangeByName("A5").String = oInternalSettingsSheet.getCellRangeByName("L141").String
   oCRMSheet.getCellRangeByName("A6").String = oInternalSettingsSheet.getCellRangeByName("L142").String & oOptionsSheet.getCellRangeByName("BC85").String
   oCRMSheet.getCellRangeByName("A7").String = oInternalSettingsSheet.getCellRangeByName("L143").String & oOptionsSheet.getCellRangeByName("BC86").String
   oCRMSheet.getCellRangeByName("A8").String = oInternalSettingsSheet.getCellRangeByName("L144").String
   
   'store the row currently writting to in script file
   Dim iCRMRow as Integer
   iCRMRow = 9

   'set the secondary label on the progress dialog   
   oProgressLabel2.Text = "Writing script"

   'count through the compacted array to build the script with
   For iTempCounter1 = 1 to UBOUND(sAgent())
      
      'check header
      If sHeader(iTempCounter1)<>"X" OR sHeader(iTempCounter1)<>"x" Then sHeader(iTempCounter1) = "<BR>Requested<SP>Header"
      If sFooter(iTempCounter1)<>"X" OR sFooter(iTempCounter1)<>"x" Then sFooter(iTempCounter1) = "<BR>Requested<SP>Footer"
      If sLogo(iTempCounter1)<>"X" OR sLogo(iTempCounter1)<>"x" Then sLogo(iTempCounter1) = "<BR>Requested<SP>Logo"
      
      
      'combine and save data to sheet
      'url search with hash
      oCRMSheet.getCellRangeByName("A" & iCRMRow).String = oInternalSettingsSheet.getCellRangeByName("L145").String & sHash(iTempCounter1)
      'click schedule call button
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 1).String = oInternalSettingsSheet.getCellRangeByName("L146").String
      'subject
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 2).String = oInternalSettingsSheet.getCellRangeByName("L147").String & "Market<SP>Assist<SP>-<SP>" & sIDs(iTempCounter1)
      'metro
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 3).String = oInternalSettingsSheet.getCellRangeByName("L148").String & sMetro(iTempCounter1)
      'direction
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 4).String = oInternalSettingsSheet.getCellRangeByName("L149").String
      'held
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 5).String = oInternalSettingsSheet.getCellRangeByName("L150").String
      'contacted
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 6).String = oInternalSettingsSheet.getCellRangeByName("L151").String
      'call Type
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 7).String = oInternalSettingsSheet.getCellRangeByName("L152").String
      'contact Type
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 8).String = oInternalSettingsSheet.getCellRangeByName("L153").String
      'notes
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 9).String = oInternalSettingsSheet.getCellRangeByName("L154").String & "Sent<SP>email<BR><BR>" & sHeader(iTempCounter1) & sFooter(iTempCounter1) & sLogo(iTempCounter1) & "<BR><BR>" & oInternalSettingsSheet.getCellRangeByName("Y19").String & "<SP>-<SP>" & sDateEmailed(iTempCounter1)
      'click save
      oCRMSheet.getCellRangeByName("A" & iCRMRow + 10).String = oInternalSettingsSheet.getCellRangeByName("L155").String
   
      'increment the spot in the script file to
      iCRMRow = iCRMRow + 11

      'set the secondary progressbar
      oProgressBar2.Value = (iTempCounter1/(UBOUND(sAgent()))) * 100
      
   'next record to process
   Next iTempCounter1
   
   'set the primary progressbar
   oProgressBar1.Value = 80

   'rebuild the options to save CRM doc with
   Dim oCRMSaveOptions(1) as New com.sun.star.beans.PropertyValue
   
   'set the options to save the file
   oCRMSaveOptions(0).Name = "FilterName"
   oCRMSaveOptions(0).Value = "Text - txt - csv (StarCalc)"
   oCRMSaveOptions(1).Name = "FilterOptions"
   oCRMSaveOptions(1).Value = "59,0,11,1,"

   'store the save location
   Dim sSavePath as String
   
   'check if there is a path already set
   If oInternalSettingsSheet.getCellRangeByName("BG4").String <> "" Then
      
      'get the location
      sSavePath = oInternalSettingsSheet.getCellRangeByName("BG4").String
      
   Else
      
      'call sub to request save path
      sSavePath = fCommonGetFolder
      
   'end the current if statement
   End If

   'build the link of the file to get
   sSaveLink = ConvertToURL(sSavePath & "/CRM Script - " & oOptionsSheet.getCellRangeByName("H2").String & ".iim" )
   
   'save the CRM script file
   oCRMDoc.storeAsURL(sSaveLink , oCRMSaveOptions())
   
   'set the primary progressbar
   oProgressBar1.Value = 80

   'close the CRM script file
   oCRMDoc.Close(True)
   
End Sub





Sub mCRMCheckItems
   
   'store the number of rows to test
   Dim iNumOfRows as Integer
   
   'get the number of records to test
   iNumOfRows = oInternalSettingsSheet.getCellRangeByName("Y15").Value
   
   'store the counter used to determine the found items
   Dim iNumberFound as Integer
   iNumberFound = 0

   'set the secondary label on the progress dialog   
   oProgressLabel2.Text = "Checking items for script"
   
   'count through the rows to be testing
   For iRowCounter1 = 2 to iNumOfRows + 1
      
      'check if the current row is dated as done and not already been emailed
      If oProcessSheet.getCellRangeByName("R" & iRowCounter1).String <> "" AND oProcessSheet.getCellRangeByName("BJ" & iRowCounter1).String = "Email" Then

         'increment the number of items in the array         
         iNumberFound = iNumberFound + 1
         
         'increment the array to store the found record
         ReDim Preserve sAgent(iNumberFound) as String
         ReDim Preserve sHash(iNumberFound) as String
         ReDim Preserve sIDs(iNumberFound) as String
         ReDim Preserve sDateEmailed(iNumberFound) as String
         ReDim Preserve sHeader(iNumberFound) as String
         ReDim Preserve sFooter(iNumberFound) as String
         ReDim Preserve sLogo(iNumberFound) as String
         ReDim Preserve sMetro(iNumberFound) as String
         
         'save the data into the array
         sAgent(iNumberFound) = oProcessSheet.getCellRangeByName("N" & iRowCounter1).String
         sHash(iNumberFound) = oProcessSheet.getCellRangeByName("AB" & iRowCounter1).String
         sIDs(iNumberFound) = oProcessSheet.getCellRangeByName("B" & iRowCounter1).String
         sDateEmailed(iNumberFound) = oProcessSheet.getCellRangeByName("AK" & iRowCounter1).String
         sHeader(iNumberFound) = oProcessSheet.getCellRangeByName("AE" & iRowCounter1).String
         sFooter(iNumberFound) = oProcessSheet.getCellRangeByName("AF" & iRowCounter1).String
         sLogo(iNumberFound) = oProcessSheet.getCellRangeByName("AG" & iRowCounter1).String
         sMetro(iNumberFound) = oProcessSheet.getCellRangeByName("D" & iRowCounter1).String

         'mark the record as ready for reporting in the process column
         oProcessSheet.getCellRangeByName("BJ" & iRowCounter1).String = "Report"
         
         'set the CRM script date
         oProcessSheet.getCellRangeByName("AL" & iRowCounter1).DataArray = oInternalSettingsSheet.getCellRangeByName("Y3").DataArray
         
      'end the current if statement
      End If
      
      'set the secondary progressbar
      oProgressBar2.Value = (iRowCounter1/(iNumOfRows + 1)) * 100
      
   'next row to test
   Next iRowCounter1

   'set the primary progressbar
   oProgressBar1.Value = 33

   'set the secondary label on the progress dialog   
   oProgressLabel2.Text = "Gathering data"
   
   'count through the array to get the correct metro info
   For iTempCounter1 = 1 to iNumberFound
      
      'count through the rows to check on the process sheet
      For iRowCounter2 = 63 to 112
      
         'test the metro on the process sheet and set the metro
         If sMetro(iTempCounter1) = oInternalSettingsSheet.getCellRangeByName("BA" & iRowCounter2).String Then sMetro(iTempCounter1) = oInternalSettingsSheet.getCellRangeByName("BJ" & iRowCounter2).String
         
      'next row to check
      Next iRowCounter2
      
      'set the secondary progressbar
      oProgressBar2.Value = (iTempCounter1/(iNumberFound)) * 100
      
   'next metro to get
   Next iTempCounter1

   'set the primary progressbar
   oProgressBar1.Value = 66
   
End Sub


My code takes predefined strings that will be used for each line of the resulting script and place them into the new document. Then repeats several lines with the needed data embedded into the line to run through. These lines are the oInternalSettingSheet items.

To simplify the code you really only need to worry about this code here

This first section generates a new blank calc doc to work with.... Hidden in the background so the user will not see it by the "oCRMDocOptions(0).Value = True" line.

Code: Select all   Expand viewCollapse view
   'stores the script doc's open settings
   Dim oCRMDocOptions(0) as New com.sun.star.beans.PropertyValue
   
   'stores the document objects
   Dim oCRMDoc as Object
   Dim oCRMSheet as Object
   
   'setup the settings to open the crm script file with
   oCRMDocOptions(0).Name = "Hidden"
   oCRMDocOptions(0).Value = True
   
   'open a blank spreadsheet to build the script with
   oCRMDoc = starDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, oCRMDocOptions())
   
   'setup the first sheet of the new doc
   oCRMSheet = oCRMDoc.sheets().getByIndex(0)


Anything you want to write to the file can go here. Using any loops as needed to simplify the work.

Then this section will save the file to the location specified

Code: Select all   Expand viewCollapse view
   'rebuild the options to save CRM doc with
   Dim oCRMSaveOptions(1) as New com.sun.star.beans.PropertyValue
   
   'set the options to save the file
   oCRMSaveOptions(0).Name = "FilterName"
   oCRMSaveOptions(0).Value = "Text - txt - csv (StarCalc)" 'THIS ALLOWS IT TO BE SAVED AS A TXT FILE
   oCRMSaveOptions(1).Name = "FilterOptions"
   oCRMSaveOptions(1).Value = "59,0,11,1," 'THIS IS THE FORMATTING I USED TO ACHIEVE THE NO DELIMITER AND ASCII FORMAT

   'store the save location
   Dim sSavePath as String
        sSavePath = THIS IS WHERE YOU PUT THE FILE PATH AND FILE NAME

   'build the link of the file to get
   sSaveLink = ConvertToURL(sSavePath)
   
   'save the CRM script file
   oCRMDoc.storeAsURL(sSaveLink , oCRMSaveOptions())

   'close the CRM script file
   oCRMDoc.Close(True)



It does look harder than it is. This will also overwrite the old file every time as well (seems that you were doing that before). I've also got a simple folder picker and file picker if that info need be used as well, in fact the folder picker is used in the full code above but the code is not shown... PM me for more info.
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user

My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: MSN [Bot] and 4 guests