Re: [tdf-discuss] Macro Difficulties
Hi Charles, *,
On Wed, Mar 13, 2013 at 4:36 PM, Charles Jenkins wrote:
>
> You're right. My attempt to fool with options didn't work at first because I
> misidentified the file's character set, so I thought the "csv" in the filter
> name was significant and embarked on the quest to convert to CSV.
Ah, OK :-) - teh csv filter is used synonymous for all character
separated values, not specific to a comma, although that is the most
used ones. One could argue whether tsv deserves a special mention or
not, but you don't add hsv (hyphen separated) or all other kinds of
other fileextensions
> Now that I have the right character set, everything works perfectly!!!
>
> I have put up a post on SAP's community network site to let others know it's
> possible to export from Business One to LibreOffice; hopefully it will help
> the switchers out there. I start my post off by publicly thanking you and
> Eduardo.
Great to hear that it works, and of course thanks for sharing your
findings with others!
> If you'd like to see the post, it's at: http://scn.sap.com/thread/3325286
Allow me some nitpicking :-)
> Public Sub StarOpenTsvFile(tsvPath As String)
> Dim starDesktop As Object
> Dim url As String
> Dim doc As Object
> Dim parms(1) As New com.sun.star.beans.PropertyValue
> parms(0).Name = "FilterName"
> parms(0).Value = "Text - txt - csv (StarCalc)" ' Name is incorrect, but
> required
The name is of course correct, as that is what the filter is called.
It is for text based ("Text") Spreadsheets ("StarCalc"), most commonly
with txt and csv extensions. It is not limited to comma separated
files. I prefer to read csv as character separated, as that matches it
much better, as multiple delimers, or also no delimter at all (when
using fixed-width columns) can be used.
It doesn't explicitly list tsv or other possible filename extensions though.
> parms(1).Name = "FilterOptions"
> parms(1).Value = "9,,65535,1" ' Tab seps, no string delim, UTF-16
> charset, begin at line 1
having a pointer to documentation for these kind of "magic strings" is
always nice to have :-)
Otherwise: Thanks again for sharing the solution - too often one does
just read "Solved my problem, bye" and doesn't get to know what the
actual solution was, so thumbs up :-)
ciao
Christian
--
Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.documentfoundation.org/www/discuss/
All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
Christian,
You're right. My attempt to fool with options didn't work at first because I
misidentified the file's character set, so I thought the "csv" in the filter
name was significant and embarked on the quest to convert to CSV.
Now that I have the right character set, everything works perfectly!!!
I have put up a post on SAP's community network site to let others know it's
possible to export from Business One to LibreOffice; hopefully it will help the
switchers out there. I start my post off by publicly thanking you and Eduardo.
If you'd like to see the post, it's at: http://scn.sap.com/thread/3325286
That post contains full instructions on how to modify AutoOpen.xls. But for
those who want to cut right to the chase without reading it, here's the
subroutine LibreOffice can use to open a tab-separated text file output by SAP
Business One:
Public Sub StarOpenTsvFile(tsvPath As String)
Dim starDesktop As Object
Dim url As String
Dim doc As Object
Dim parms(1) As New com.sun.star.beans.PropertyValue
parms(0).Name = "FilterName"
parms(0).Value = "Text - txt - csv (StarCalc)" ' Name is incorrect, but
required
parms(1).Name = "FilterOptions"
parms(1).Value = "9,,65535,1" ' Tab seps, no string delim, UTF-16 charset,
begin at line 1
starDesktop = createUnoService("com.sun.star.frame.Desktop")
url = ConvertToUrl(tsvPath)
doc = starDesktop.loadComponentFromURL(url, "_blank", 0, parms)
End Sub
Thanks again!
--
Charles
--
Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.documentfoundation.org/www/discuss/
All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
Hi Charles, *, On Tue, Mar 12, 2013 at 1:07 PM, Charles Jenkins wrote: > > Now if only I can find a way to convert my tab-separated .txt file to a > comma-separated .csv file, I think all would be golden. Hmm. Either I'm completely missing the point here, or you do :-) With the properties, you explicitly set the filter to the spreadsheet one (FilterName), and with the other (FilterOptions), you define how the file is structured - you can configure what separator is used, what column types they have. Just read the linked wiki page. Here once again: http://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options#Filter_Options_for_the_CSV_Filter ciao Christian -- Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.documentfoundation.org/www/discuss/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
Thank you very much, Eduardo and Christian. I guess I misread that post I linked to, because it left me with the impression that creating those properties couldn't be done in regular VB. Now if only I can find a way to convert my tab-separated .txt file to a comma-separated .csv file, I think all would be golden. I tried using VB to open the text file, read from it, convert each line, and write to an output file. Unfortunately, on Windows, the output is garbage. From what I can tell, it looks like LibreOffice's LINE INPUT command reads in garbage from the source file, probably because of an error in Unicode conversion. Could LibreOffice VBA call an extension written in Python? Converting TSV to CSV should be trivial, and I'm betting a Python extension would do it flawlessly. -- Charles -- Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.documentfoundation.org/www/discuss/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
Hi Charles, *,
On Mon, Mar 11, 2013 at 3:55 PM, Charles Jenkins wrote:
> dim dummy() ' Empty array of parameters
>
> starDesktop = createUnoService("com.sun.star.frame.Desktop")
> url = ConvertToUrl( ExcelPath )
> doc = starDesktop.loadComponentFromURL( url, "_blank", 0, dummy )
>
>> I think I need what is described in
>> http://knowledgebase.progress.com/articles/Article/P147655 -- an extension
>> that can wrap strings into the property values required by
>> loadComponentFromUrl(), so I can fill the array of parameters in a way that
>> tells LO it will be loading the text file into a spreadsheet.
The "extension" just is a helper function for pretty-printing, you
surely don't need that, but can enter the values right away.
Dim args() as new com.sun.star.beans.PropertyValue
args(0).Name = "FilterName"
args(0).Value = "Text - txt - csv (StarCalc)"
args(1).Name = "FilterOptions"
args(1).Value = ""
http://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options#Filter_Options_for_the_CSV_Filter
HTH,
ciao
Christian
--
Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.documentfoundation.org/www/discuss/
All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
I'm making some progress. Here's my code so far:
==
Public Sub OpenExcelFile(excelPath As String)
Attempt1:
on error goto Fail1
' The Excel method that doesn't work
' under LO, and which I don't own copyright to
exit sub
Fail1:
resume Attempt2
Attempt2:
dim starDesktop as object
dim url as string
dim doc as object
dim dummy() ' Empty array of parameters
starDesktop = createUnoService("com.sun.star.frame.Desktop")
url = ConvertToUrl( ExcelPath )
doc = starDesktop.loadComponentFromURL( url, "_blank", 0, dummy )
End Sub
==
The problem is, since the file output by SAP ends with the ".txt" extension,
Calc opens the new file as a Writer document, not a spreadsheet.
I think I need what is described in
http://knowledgebase.progress.com/articles/Article/P147655 -- an extension that
can wrap strings into the property values required by loadComponentFromUrl(),
so I can fill the array of parameters in a way that tells LO it will be loading
the text file into a spreadsheet.
Does anyone out there happen to have experience with such an extension, or know
how to get the open-source code for something similar? (The LO Extensions site
has a CSV-opening extension, but I'd have to modify it to remove the UI, and
there's no indication at all of how to get the source!)
--
Charles
--
Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.documentfoundation.org/www/discuss/
All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
Thank you, everyone who offered help with my macro problem in trying to make a worksheet that will open another file as a worksheet. With the live doc link, I found what I think I need and altered the existing macro to try the Excel way first, then with the help of ON ERROR GOTO, try to open the file through the StarDesktop object. I'd love to report success, but that work was as the end of the day on Friday, and when I saved the workbook in Excel format, LO completely stripped out the macros ~>:-$ I'll try again today, using Excel to create the file with the hybrid macro in it. -- Charles -- Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.documentfoundation.org/www/discuss/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
>> The documentation on LO >> BASIC is so sparse that I can't find anything about opening >> spreadsheets. The LO help file promises documentation at >> OpenOffice.org; but clicking that link takes me instead to >> https://wiki.documentfoundation.org/Documentation/BASIC_Guide, which >> is a placeholder page containing only a link to another >> website--which doesn't respond. > > > The BASIC-Guide is still on > > http://wiki.openoffice.org/wiki/Documentation/BASIC_Guide I have corrected the link on our wiki to point to the correct page, as above. --Jean -- Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.documentfoundation.org/www/discuss/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
Hi Charles, Charles Jenkins schrieb: This is a story about the usability of LO BASIC and the documentation thereof. And also a plea for help. I'm trying to get LibreOffice to work with SAP Business One (hereafter called B1) in order to eliminate the need to pay for expensive licenses for Microsoft Excel. B1 has a toolbar button that's supposed to generate a spreadsheet for data viewed as a table onscreen. Unfortunately, instead of really creating a spreadsheet, B1 just dumps out a tab-separated text file and then opens a spreadsheet called "AutoOpen.xls" that in turn loads the text file into a new spreadsheet. This clunky way of doing things has the advantage that there's no worry about file formats. Whatever version of Excel you're running, it can open the text file. This method *could* work in LibreOffice too, but unfortunately the macro command used in AutoOpen.xls doesn't exist in LO BASIC. The command is Workbooks.OpenText I'm not sure if the problem is that OpenText doesn't exist, or if there is no Workbooks object to begin with. The documentation on LO BASIC is so sparse that I can't find anything about opening spreadsheets. The LO help file promises documentation at OpenOffice.org; but clicking that link takes me instead to https://wiki.documentfoundation.org/Documentation/BASIC_Guide, which is a placeholder page containing only a link to another website--which doesn't respond. The BASIC-Guide is still on http://wiki.openoffice.org/wiki/Documentation/BASIC_Guide You might also want to look at http://wiki.openoffice.org/wiki/Documentation/DevGuide/Basic/OpenOffice.org_Basic Old, but in parts still useful is "StarOffice 8 Programming Guide for BASIC". It is no longer available from Oracle, but you will find it on the web. And you need "IDL Reference Complete LibreOffice 4.0 API reference". It is in the SDK or use it online http://api.libreoffice.org/docs/common/ref/com/sun/star/module-ix.html (By the way, the help file does document an Open command, but that command is used to open simple data files to be processed and closed by the script. It doesn't cause LO to open a document in the UI.) The help has a lot of commands, which do not fit to modern systems. The simple file access commands no longer work, never use them. After finding nothing documented, I enabled macro recording, began recording a macro, and used File > Open to open the text file/spreadsheet myself. Obviously, I was hoping to then examine the macro to learn what objects and functions LO uses to open files. The macro recorder uses the dispatcher. You cannot learn from it to write own macros. But opening a new file causes macro recording to end without a comment, warning, or error…and without saving anything of the macro in progress. Can anyone give me clues to creating a macro which can open a spreadsheet? Kind regards Regina -- Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.documentfoundation.org/www/discuss/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
2013/3/7 Tony Pursell : > On 7 March 2013 16:43, Charles Jenkins wrote: > >> This is a story about the usability of LO BASIC and the documentation >> thereof. And also a plea for help. >> >> I'm trying to get LibreOffice to work with SAP Business One (hereafter >> called B1) in order to eliminate the need to pay for expensive licenses for >> Microsoft Excel. >> >> B1 has a toolbar button that's supposed to generate a spreadsheet for data >> viewed as a table onscreen. Unfortunately, instead of really creating a >> spreadsheet, B1 just dumps out a tab-separated text file and then opens a >> spreadsheet called "AutoOpen.xls" that in turn loads the text file into a >> new spreadsheet. >> >> This clunky way of doing things has the advantage that there's no worry >> about file formats. Whatever version of Excel you're running, it can open >> the text file. This method *could* work in LibreOffice too, but >> unfortunately the macro command used in AutoOpen.xls doesn't exist in LO >> BASIC. The command is Workbooks.OpenText >> >> I'm not sure if the problem is that OpenText doesn't exist, or if there is >> no Workbooks object to begin with. The documentation on LO BASIC is so >> sparse that I can't find anything about opening spreadsheets. The LO help >> file promises documentation at OpenOffice.org; but clicking that link takes >> me instead to >> https://wiki.documentfoundation.org/Documentation/BASIC_Guide, which is a >> placeholder page containing only a link to another website--which doesn't >> respond. >> >> (By the way, the help file does document an Open command, but that command >> is used to open simple data files to be processed and closed by the script. >> It doesn't cause LO to open a document in the UI.) >> >> After finding nothing documented, I enabled macro recording, began >> recording a macro, and used File > Open to open the text file/spreadsheet >> myself. Obviously, I was hoping to then examine the macro to learn what >> objects and functions LO uses to open files. But opening a new file causes >> macro recording to end without a comment, warning, or error…and without >> saving anything of the macro in progress. >> >> Can anyone give me clues to creating a macro which can open a spreadsheet? >> >> > Try this > > http://www.pitonyak.org/oo.php > > Tony Sorry, should have read your post before I posted an identical one (we sent exactly the same link)… Johnny Rosenberg > > -- > Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org > Problems? > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette > List archive: http://listarchives.documentfoundation.org/www/discuss/ > All messages sent to this list will be publicly archived and cannot be deleted -- Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.documentfoundation.org/www/discuss/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [tdf-discuss] Macro Difficulties
On 7 March 2013 16:43, Charles Jenkins wrote: > This is a story about the usability of LO BASIC and the documentation > thereof. And also a plea for help. > > I'm trying to get LibreOffice to work with SAP Business One (hereafter > called B1) in order to eliminate the need to pay for expensive licenses for > Microsoft Excel. > > B1 has a toolbar button that's supposed to generate a spreadsheet for data > viewed as a table onscreen. Unfortunately, instead of really creating a > spreadsheet, B1 just dumps out a tab-separated text file and then opens a > spreadsheet called "AutoOpen.xls" that in turn loads the text file into a > new spreadsheet. > > This clunky way of doing things has the advantage that there's no worry > about file formats. Whatever version of Excel you're running, it can open > the text file. This method *could* work in LibreOffice too, but > unfortunately the macro command used in AutoOpen.xls doesn't exist in LO > BASIC. The command is Workbooks.OpenText > > I'm not sure if the problem is that OpenText doesn't exist, or if there is > no Workbooks object to begin with. The documentation on LO BASIC is so > sparse that I can't find anything about opening spreadsheets. The LO help > file promises documentation at OpenOffice.org; but clicking that link takes > me instead to > https://wiki.documentfoundation.org/Documentation/BASIC_Guide, which is a > placeholder page containing only a link to another website--which doesn't > respond. > > (By the way, the help file does document an Open command, but that command > is used to open simple data files to be processed and closed by the script. > It doesn't cause LO to open a document in the UI.) > > After finding nothing documented, I enabled macro recording, began > recording a macro, and used File > Open to open the text file/spreadsheet > myself. Obviously, I was hoping to then examine the macro to learn what > objects and functions LO uses to open files. But opening a new file causes > macro recording to end without a comment, warning, or error…and without > saving anything of the macro in progress. > > Can anyone give me clues to creating a macro which can open a spreadsheet? > > Try this http://www.pitonyak.org/oo.php Tony -- Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.documentfoundation.org/www/discuss/ All messages sent to this list will be publicly archived and cannot be deleted
[tdf-discuss] Macro Difficulties
This is a story about the usability of LO BASIC and the documentation thereof. And also a plea for help. I'm trying to get LibreOffice to work with SAP Business One (hereafter called B1) in order to eliminate the need to pay for expensive licenses for Microsoft Excel. B1 has a toolbar button that's supposed to generate a spreadsheet for data viewed as a table onscreen. Unfortunately, instead of really creating a spreadsheet, B1 just dumps out a tab-separated text file and then opens a spreadsheet called "AutoOpen.xls" that in turn loads the text file into a new spreadsheet. This clunky way of doing things has the advantage that there's no worry about file formats. Whatever version of Excel you're running, it can open the text file. This method *could* work in LibreOffice too, but unfortunately the macro command used in AutoOpen.xls doesn't exist in LO BASIC. The command is Workbooks.OpenText I'm not sure if the problem is that OpenText doesn't exist, or if there is no Workbooks object to begin with. The documentation on LO BASIC is so sparse that I can't find anything about opening spreadsheets. The LO help file promises documentation at OpenOffice.org; but clicking that link takes me instead to https://wiki.documentfoundation.org/Documentation/BASIC_Guide, which is a placeholder page containing only a link to another website--which doesn't respond. (By the way, the help file does document an Open command, but that command is used to open simple data files to be processed and closed by the script. It doesn't cause LO to open a document in the UI.) After finding nothing documented, I enabled macro recording, began recording a macro, and used File > Open to open the text file/spreadsheet myself. Obviously, I was hoping to then examine the macro to learn what objects and functions LO uses to open files. But opening a new file causes macro recording to end without a comment, warning, or error…and without saving anything of the macro in progress. Can anyone give me clues to creating a macro which can open a spreadsheet? -- Charles -- Unsubscribe instructions: E-mail to discuss+h...@documentfoundation.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.documentfoundation.org/www/discuss/ All messages sent to this list will be publicly archived and cannot be deleted
