Dear Andrès,

I drafted a macro ses-setq to allow access a SES spreadsheet programatically, and I updated your code to use it. This allowed to remove all the workarounds. Please note that ses-setq is just a draft, far from complete. I intend to add some switches to allow access the cells eaither by cell name or by row col, and to modify either value or formula.


[https://res.cdn.office.net/assets/mail/file-icon/png/generic_16x16.png]andreÌs.el<https://1drv.ms/u/s!AkDIBBjRAOVwg3AeKk2hwCznI9rK>
[https://res.cdn.office.net/assets/mail/file-icon/png/generic_16x16.png]ses.el<https://1drv.ms/u/s!AkDIBBjRAOVwg3FksGYkC-uFDOQQ>

PS: I need to improve the macro, notable setting a cell value should also affect its formula if any.
________________________________
De : andrés ramírez <rrandresf@hotmail.com>
Envoyé : samedi 4 novembre 2023 14:55
À : Vincent Belaïche <vincent.b.1@hotmail.fr>
Objet : using ses programatically (was: a ses question)

Hi Vincent.

Thanks again for fixing the ses-range macro.
After applying that change, which btw fixed one of the workarounds in
the elisp code we have been improving on this thread.

The other workaround is the use of the yank function.

When I apply this patch to the code:
--8<---------------cut here---------------start------------->8---
diff -u /tmp/smeter.el /tmp/smeter_wished.el
--- /tmp/smeter.el      2023-11-04 13:24:42.030386021 +0000
+++ /tmp/smeter_wished.el       2023-11-04 13:26:25.128947532 +0000
@@ -15,7 +15,7 @@
     (setq beg (point)) (forward-line 4) (setq prevmeasu (split-string (buffer-substring-no-properties beg (point)) "\n"))
     (setq beg (point)) (setq waterbill (buffer-substring-no-properties beg (line-end-position)))
     (forward-line 1)
-    (setq beg (point)) (forward-line 4) (setq measu (buffer-substring-no-properties beg (point)))
+    (setq beg (point)) (forward-line 4) (setq measu (split-string (buffer-substring-no-properties beg (point)) "\n"))
     (setq beg (point)) (setq myyyyymm (buffer-substring-no-properties beg (line-end-position)))
     (if (not (file-readable-p workbook-filename))
         (message (format "file not found: '%s'"  workbook-filename))
@@ -25,11 +25,11 @@
       (ses-set-cell 3 1 'value (string-to-number (nth 1 prevmeasu)))
       (ses-set-cell 4 1 'value (string-to-number (nth 2 prevmeasu)))
       (ses-set-cell 5 1 'value (string-to-number (nth 3 prevmeasu))) ;;; B6
-      (ses-jump-safe "C3") ;;; paste 4 cells
-      (kill-new measu)
-      (yank)
+      (ses-set-cell 2 2 'value (string-to-number (nth 0 measu))) ;;; C3
+      (ses-set-cell 3 2 'value (string-to-number (nth 1 measu)))
+      (ses-set-cell 4 2 'value (string-to-number (nth 2 measu)))
+      (ses-set-cell 5 2 'value (string-to-number (nth 3 measu))) ;;; C6
       (let ((range (ses-range E3 E6))) (ses-recalculate-cell))
-
       (ses-write-cells)
       ;;; '*1' formatting to vector also the range is the computation-result
       (let ((range  (ses-range N3 N6 *1)))

Diff finished.  Sat Nov  4 13:51:04 2023
--8<---------------cut here---------------end--------------->8---

The computation result is not the expected one (compared to the one that
uses yank). So it seems something
else is needed.

Any ideas why that patch does not do the right thing?

Best Regards
Andrés Ramírez