Hi,
I am trying to use this macro Calc - Read/Write CSV without any conversion - #15 by sokol92 in order to open CSV files with all columns as text by default, by assigning this macro to the “Open document” event.
The macro works fine when I do the “Run macro” flow, but it doesn’t work when attaching it to the event as it shows an error:
“BASIC runtime error.
Incorrect property value.”
What do I need to do to make this work when CSV documents are opened?
Thanks
The macro you specified is intended specifically for opening files, and not for listening to events.
Place the macro in any module of any application library (My Macros…), assign a convenient hotkey (or menu item) to it and use it to open csv files in which all fields should be treated as text.
Understood, is it possible to make it compatible with this event? We have 100+ people working on calc with CSV files where stripping leading zeros etc. can be a huge risk and opening a file through a hotkey won’t be sustainable.
Thanks again
Which code line this is happened with?
@Zizi64 it doesn’t specify anything beyond that, I just loaded this macro as a whole to the “Open document” event but as @sokol92 mentioned, it was not built for that and that is the solution I am looking for - CSV files opened with all columns as text automatically
Do you mean this function of the linked thread?
Function OpenCsvAsText(Optional ByVal fileName As String, Optional ByVal tokens As String) As Object
It is a Function with passed parameters, and a Return object: the opened document.
Do you want to use it as a Subroutine, without input parameters, and without Return object?
@Zizi64, this function can be assigned to a hotkey. If both parameters of the macro are omitted, the macro will call a dialog to open the csv file.
I’d suggest not to modify this routine, but to write a second macro, wich calls sokols.
.
As @nirilan wishes to interfere, when a .csv-file is “opened” by double-click in the OS one needs to find out, if a macro can interfere before loading the csv, or if the approach should be to detect a csv was loaded and then remove data and load again with the macro by @sokol92 .
.
As I never interfered with the early stage of loading files, I would follow the second approach…
Actually I would prefer to solve this outside LO by creating another handler for csv-files, but that’s OT here.
Thanks for the help @Wanderer @sokol92 @Zizi64 , unfortunately I myself have zero knowledge of building/editing macros and was just wondering if the already built macro can be edited somehow to fit the need (which I see has been brought up multiple times over the years).
Let me know if opening a CSV file with all columns automatically as text is achievable in Libreoffice, I will check other CSV editors for our need in the meantime if it’s not as a plan B.
I can add the following to @Wanderer 's message.
There are two options:
The user opens a csv file from the LibreOffice application through the corresponding menu item.
As far as I know, there is no LO event that occurs before the file opens. Therefore, the only reasonable way is to open csv files through another menu item or hotkeys, which, unfortunately, all users should be made aware of.
The user opens the csv file using the operating system tools (for example, double-clicking the file icon in Windows Explorer). At the same time, the operating system settings indicate that opening a file with the .csv extension is done by calling the LibreOffice application.
In this case, you can select parameters for the command line of the application call and set them in the operating system settings so that, for example, csv files are opened with a semicolon delimiter and text field types. An additional command line parameter can be like this (no more than 40 fields are assumed per line of the csv file):
--infilter="Text - txt - csv (StarCalc):59,34,UTF8,1/1/2/2/3/2/4/2/5/2/6/2/7/2/8/2/9/2/10/2/11/2/12/2/13/2/14/2/15/2/16/2/17/2/18/2/19/2/20/2/21/2/22/2/23/2/24/2/25/2/26/2/27/2/28/2/29/2/30/2/31/2/32/2/33/2/34/2/35/2/36/2/37/2/38/2/39/2/40/2"
Thanks for the response, this thread can be closed