My OS is Ubuntu 22.04 and I am using LibreOffice 7.4. My problem is concerned with importing .csv files into an existing spreadsheet. I have a spreadsheet macro which imports data and updates the cells stored on the main sheet to current values. I have been using this spreadsheet macro (without change) for several years. Several days ago, the macro (or spreadsheet ?) stopped working correctly. I have exhausted my (limited) debug efforts to locate the problem. I hope that I can explain the problem well enough to get help here. A simplified version of my spreadsheet – suppose that I am a loan shark, and have loaned sums to several people. My spreadsheet is a compilation of my “clients” in the A column and the amount remaining owed on their loan in column B. Suppose that I have an “bagman” partner on the street who receives payments from some of the customers on a daily basis and records these receipts in his/her own spreadsheet with the name of the payer in the A column and the amount received in the B column which he posts to me as a Cash.csv file. My macro is intended to import the Cash.csv file into a “Data” sheet of my spreadsheet, then use the VLOOKUP() function in column C of the main sheet to update the balances of each loan by looking up each “client’s” name on the “Data” sheet and transferring the received payment to the appropriate row in the main ledger. The macro then subtracts the payment (Col C) from the balance (Col B) for each client. The VLOOKUP formulation, starting in Cell C1 on the main sheet is [ =VLOOKUP(A1,Data.$A$1:$B$1000,1,0) ]. The problem is that the (macro ?) is not supposed to modify the VLOOKUP function but, in loading the csv “data” file, the VL function is changed so that the search on the main sheet starts at the last loaded cell in the “Data” sheet instead of cell $A$1, viz., if the original csv file contained 9 names, then the VL function is changed to [ =VLOOKUP(A1,Data.$A$10:$B$1000,1,0) ]. And the main sheet then displays #N/A in all relevant cells. I realized this is an extremely long post, but I have searched the net for some clue/hint for what the problem might be without an iota of success. I am hoping that a knowledgeable reader might provide some insight. Thanks…
Show us your code and the line or lines where the error is given. The code along with an example CSV file.
I am without a clue as to what line of code is causing this unexpected behavior. Further, I don’t really feel that I can share the sensitive & personal info contained in the spreadsheet. I am sorry that I cannot explain the problem - my basic confusion is why/how does loading a .csv file with a macro cause a cell containing the VLOOKUP() to be modified, especially since the relevant cell references within the VL() are “absolute”. At this point, I would like to withdraw this question from further discussion since it seems to be too complex.
You should not, but you can copy the spreadsheet to a new file and remove all personal data. (Select the area and hit backspace-key, then you are usually asked what to delete. Leave formatting and formulas, but delete anything else. / Maybe leave one or teo lines of data, manually replacing names with John Doe, Martina Musterman, or whatever is common in your Country).
.
Same for the csv-file. Usually we need only 3 to 5 lines to analyze formatting, but you need to edit this with a text-editor, or you may change formatting.
What you can do yourself: Check, if your csv is imported. (Can you see the new data). If not the problem is to access the data.
.
If data is imported, the most likely point is datatype, for example sombody changing 1234 to 1234$ or similiar, wich has to be reflected import-options (most likely given in your macro). But it is also possible the formatting of the csv is completly changed…
I really appreciate your attention to my problem - Thanks.
More info - I import five csv files (which I have checked to have the same format for years) - my csv files are imported and end up where expected, seemingly exactly as they have been in the past before problem developed. I can inspect the “Data” sheet and verify that the loaded data looks as expected.I have installed a “MsgBox” looksee which is called after each csv file is loaded. Each time it is triggered, I print the formula content of the cell containing the VL function - it confirms that each time a csv file is loaded that the VL function cell is updated to the last loaded data cell address+1, (e.g., the first empty cell on the “Data” sheet) so that after all csv files are loaded, the VL cell is left pointing to the first empty cell on the Data sheet.
My next attempt - a sort of work-around - is to, after all csv files load, just set the address in the VL cell back to the beginning to the Data array. This will not help locate the cause of the change in performance but it hopefully will allow me to continue to use the program.
Again, Thanks for your help and suggestions.
Wanderer
July 15
What you can do yourself: Check, if your csv is imported. (Can you see the new data). If not the problem is to access the data.
.
If data is imported, the most likely point is datatype, for example sombody changing 1234 to 1234$ or similiar, wich has to be reflected import-options (most likely given in your macro). But it is also possible the formatting of the csv is completly changed…
Visit Topic or reply to this email to respond.
To unsubscribe from these emails, click here.
Thank for the continued effort on your part. I will try to generate a spreadsheet similar to mine which will display the same behavior and post later today.
Thanks again
Wanderer
July 15
homebody:
Further, I don’t really feel that I can share the sensitive & personal info contained in the spreadsheet
You should not, but you can copy the spreadsheet to a new file and remove all personal data. (Select the area and hit backspace-key, then you are usually asked what to delete. Leave formatting and formulas, but delete anything else. / Maybe leave one or teo lines of data, manually replacing names with John Doe, Martina Musterman, or whatever is common in your Country).
.
Same for the csv-file. Usually we need only 3 to 5 lines to analyze formatting, but you need to edit this with a text-editor, or you may change formatting.
Visit Topic or reply to this email to respond.
To unsubscribe from these emails, click here.
Please don’t reply by mail or at least remove the original part that should had been quoted instead. As is, it is confusing, as you can see if you visit this topic on the web site.
If loading a csv file modifies any formulas in your mainSheet instead of updating the Data sheet or appending to it, it ran (at least once) for/with the wrong target. In what way are the target sheet and the target range in that sheet (where freshly imported data shall go to) designated in the macro? Does that macro use the CurrentSelection in any way?
Of course many inadvertent actions may spoil formuals without your immediate notice. If you suspect such a cause, you should repair the formula apparatus in the main sheet from an old backup of the document, probably.
Your fellow shark.
Fox example, if lines are inserted before my data, I’d expect everything to move down, and Calc could updatate ALL references, so formulas will point to the same data as before…
.
But to check this, one would need to see at least your macro, because “loading a .csv file with a macro” csn be done in different ways…
.
Decision is yours, as you are the only person who has any access here. So I’d say the problem may not be very complex, but you did not provide much to analyse…
I thank everyone who has participated in the analysis. I would withdraw the topic but do not know how,
This is my first try at posting. Not at all sure of the protocol.
My problem is exactly the same as ‘homebody’ – my spreadsheet has been in service for many years but about 6 months ago, in transferring/loading .csv data to the working spreadsheet the absolute cell reference of the search region is changed to the last data cell + 1.
In attempting to ‘fix’ the problem, I rewrote the search formula from: [ =VLOOKUP(B18,Data.$D$5:$J$1048570,6,0) ] to [ =INDEX(Data.$D$5:$J$1048570,MATCH(B18,Data.$D$5:$D$1048570,0),6) ]. But in both cases, the reference to $D$5 is changed to $D$(last entry + 1) (i.e., $D$106754:$D$1048570).
For about six months, I have diligently searched for any clue to explain and/or fix this problem w/o success. So if anyone can suggest an viable approach, I say Thanks in Advance.
Protocol says: Open a new topic. If necessary include a link to this thread, if you think here is something valuable.
.
As @homebody never revealed to much of its file, I don’t think you can judge, if he had the same problem (unless you think “something stopped working” is enough for “same problem”.
Thanks for the quick response. I will now try to ‘open a new topic’.
FYI, my problem is accurately described in the analogy of the loan shark. I don’t think thatI can improve on it.