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 Wed, Mar 13, 2013 at 4:36 PM, Charles Jenkins cejw...@gmail.com 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
Hi Charles, *, On Tue, Mar 12, 2013 at 1:07 PM, Charles Jenkins cejw...@gmail.com 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
I'm making some progress. Here's my code so far: == Public Sub OpenExcelFile(excelPath As String) Attempt1: on error goto Fail1 ' snip 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
Hi Charles, *, On Mon, Mar 11, 2013 at 3:55 PM, Charles Jenkins cejw...@gmail.com 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 = yourfilteroptionsstring 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
On 7 March 2013 16:43, Charles Jenkins cejw...@gmail.com 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
Re: [tdf-discuss] Macro Difficulties
2013/3/7 Tony Pursell a...@princeswalk.fsnet.co.uk: On 7 March 2013 16:43, Charles Jenkins cejw...@gmail.com 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
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
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
