Topic: Write an Excel's cell with ADO

[Patrick_35]

Hi

I try to write an Excel cell with ADO, but it does not work
I am inspired by this topic (thank's ElpanovEvgeniy)

Code: [Select]

(setq fil (getfiled "Sélectionner le fichier Excel"  ""  "xls;xlsx" 0)) ;choose Exel's file

(setq adoconnect (vlax-get-or-create-object "ADODB.Connection")
      adorecord  (vlax-get-or-create-object "ADODB.Recordset")
      adocommand (vlax-get-or-create-object "ADODB.Command")
)

; Ouvrir le fichier Excel
; Open Excel's file
(vlax-invoke-method adoconnect 'Open
(strcat "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
fil
";Extended Properties=;Excel 8.0;HDR=No"
)
""
""
nil
)

; Liste des feuilles Excel
; Dump sheets
(mapcar '(lambda(x) (vl-string-right-trim "$" x))
(vl-remove-if-not '(lambda(x) (wcmatch x "*$"))
  (caddr (mapcar '(lambda(x) (mapcar 'vlax-variant-value x))
(vlax-safearray->list
   (vlax-variant-value
     (vlax-invoke-method
       (vlax-invoke-method adoconnect 'OpenSchema 20)
       "GetRows"
       65535
     )
   )
)
)
  )
)
)

; Ouvrir la Feuille Excel
; Open sheet TD01
(vlax-invoke-method adorecord 'Open
      (strcat "SELECT * FROM [" "TD01" "]")
      adoconnect
      1
      3
      nil
)

; Lire la cellule A1 (fonctionne comme A1:B5 par exemple)
; Read cell A1 in sheet TD01
(vlax-invoke-method adorecord 'Open "SELECT * FROM [TD01$A1:A1]" adoconnect 1 3 nil)
(setq res (vlax-invoke adoconnect 'Execute "SELECT * FROM [TD01$A1:A1]"))
(setq tot 0)
(while (< tot (vlax-get (vlax-get res 'fields) 'Count))
  (princ (strcat "\n" (vlax-get (vlax-get-property (vlax-get res 'fields) 'item tot) 'value)))
  (setq tot (1+ tot))
)

; Ecrire la cellule A1 (fonctionne comme A1:B5 par exemple)
; Write cell A1 in sheet TD01
(vlax-invoke-method adorecord 'Open "SELECT * FROM [TD01$A1:A1]" adoconnect 1 3 nil)
(setq res (vlax-invoke adoconnect 'Execute "SELECT * FROM [TD01$A1:A1]"))
(setq cel (vlax-get-property (vlax-get res 'fields) 'item 0))
(vlax-put-property cel 'Value "Test") ; Not Work

Regards

@+

[CAB]

Also check these threads. There is a problem using Windows 7, I think.
ADO Links

[Patrick_35]

Thank's Cab

I'm try the search and i don't find.
I work with windows XP

@+

[Patrick_35]

Yes, I find

Code: [Select]

; Connect to Excel's file with ADO
(defun Connexion_Xls(fichier / adoconnect)
  (setq adoconnect (vlax-get-or-create-object "ADODB.Connection"))
  (vlax-invoke-method adoconnect 'Open
(strcat "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
fil
";Extended Properties=;Excel 8.0;HDR=No"
)
""
""
nil
  )
  adoconnect
)

; Dump sheets
(defun Liste_Onglets(Connexion)
  (mapcar '(lambda(x) (vl-string-right-trim "$" x))
  (vl-remove-if-not '(lambda(x) (wcmatch x "*$"))
    (caddr (mapcar '(lambda(x) (mapcar 'vlax-variant-value x))
   (vlax-safearray->list
     (vlax-variant-value
       (vlax-invoke-method
(vlax-invoke-method Connexion 'OpenSchema 20)
"GetRows"
65535
       )
     )
   )
   )
    )
  )
  )
)

; Read cell
(defun Lire_cellule(Connexion Feuille Cellule / adocommand adorecord txt)
  (setq adorecord  (vlax-get-or-create-object "ADODB.Recordset")
adocommand (vlax-get-or-create-object "ADODB.Command")
  )
  (vlax-put adocommand 'ActiveConnection Connexion)
  (vlax-put adocommand 'CommandText (strcat "SELECT * FROM [" Feuille "$" Cellule ":" Cellule "]"))
  (vlax-invoke-method adorecord 'Open adocommand nil 1 3 nil)
  (setq txt (vlax-get (vlax-get-property (vlax-get adorecord 'fields) 'item 0) 'Value))
  (vlax-invoke adorecord 'Close)
  (vlax-release-object adorecord)
  (vlax-release-object adocommand)
  txt
)

; Write cell
(defun Ecrire_Cellule(Connexion Feuille Cellule Valeur / adocommand adorecord)
  (setq adorecord  (vlax-get-or-create-object "ADODB.Recordset")
adocommand (vlax-get-or-create-object "ADODB.Command")
  )
  (vlax-put adocommand 'ActiveConnection Connexion)
  (vlax-put adocommand 'CommandText (strcat "SELECT * FROM [" Feuille "$" Cellule ":" Cellule "]"))
  (vlax-invoke-method adorecord 'Open adocommand nil 1 3 nil)
  (vlax-put-property (vlax-get-property (vlax-get adorecord 'fields) 'item 0) 'Value Valeur)
  (vlax-invoke adorecord 'Update)
  (vlax-invoke adorecord 'Close)
  (vlax-release-object adorecord)
  (vlax-release-object adocommand)
  (princ)
)

; Close file and ADO
(defun Fermer_Xls(Connexion)
  (vlax-invoke Connexion 'Close)
  (vlax-release-object Connexion)
)

; Example
(setq fil (getfiled "Sélectionner le fichier Excel"  ""  "xls;xlsx" 0)) ; search file
(setq mon_xls (connexion_xls fil)) ; connect to file
(setq lst (liste_onglets mon_xls)) ; Dump sheets
(lire_cellule mon_xls (car lst) "A1") ; read cell
(ecrire_cellule mon_xls (car lst) "A1" "GREAT ^^") ; write cell
(fermer_xls mon_xls); close file and ADO

[Lee Mac]

Patrick, quick question: is the ADO method quicker than writing using the Excel.Application?

[Patrick_35]

Patrick, quick question: is the ADO method quicker than writing using the Excel.Application?

Hi
Yes, using CDO method is faster than using Excel.Application fast and more and it also helps overcome Excel

It remains to find for Excel 2007 as methods change.

@+

[gile]

Hi,

Have to consider that using this method, the Excel sheet is read as a database table. So, the first line may or may not be considered as a 'header' (argument HDR=Yes or HDR=No) and the the data in each column have to be the same type (interger, real, string, date, ...).

For Excel 2007, you have to use the "Microsoft.ACE.OLEDB.12.0" driver instead of "Micosoft.Jet.OLEDB.4.0" (not tested) and the .xlsx file extension.

Code: [Select]

(vlax-invoke-method adoconnect 'Open
(strcat "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
fil
";Extended Properties=;Excel 8.0;HDR=No"
)
""
""
nil
  )

[Patrick_35]

Hi,

Have to consider that using this method, the Excel sheet is read as a database table. So, the first line may or may not be considered as a 'header' (argument HDR=Yes or HDR=No) and the the data in each column have to be the same type (interger, real, string, date, ...).

For Excel 2007, you have to use the "Microsoft.ACE.OLEDB.12.0" driver instead of "Micosoft.Jet.OLEDB.4.0" (not tested) and the .xlsx file extension.

Code: [Select]

(vlax-invoke-method adoconnect 'Open
(strcat "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
fil
";Extended Properties=;Excel 8.0;HDR=No"
)
""
""
nil
  )

Thanks gile
I find it

Code: [Select]

; Connexion sur le fichier Excel
; Retourne la connexion si tout s'est bien passé, sinon nil
;
; 1er argument --> Nom du fichier
;
; Pour ceux qui ont la version antérieur à Office 2007
; il faut que AccessDatabaseEngine.exe (25.3Mo) soit installé pour lire les fichiers xlsx
; http://www.microsoft.com/downloads/fr-fr/confirmation.aspx?familyId=7554f536-8c28-4598-9b72-ef94e038c891&displayLang=fr

(defun Connexion_Xls(fichier / adoconnect data)
  (setq adoconnect (vlax-create-object "ADODB.Connection"))
  (if (eq (strcase (vl-filename-extension fichier)) ".XLSX")
    (setq data (strcat "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
fichier
";Extended Properties='Excel 12.0;HDR=No'"
       )
    )
    (setq data (strcat "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
fichier
";Extended Properties=;Excel 8.0;HDR=No"
       )
    )
  )
  (if (vl-catch-all-error-p (vl-catch-all-apply 'vlax-invoke-method (list adoconnect
  'Open
  data
  ""
  ""
  nil
    )
    )
      )
    nil
    adoconnect
  )
)Now, i've an problem.
With an xlsx's file, i write only in cell "A1"

and the the data in each column have to be the same type (interger, real, string, date, ...).

and what you recommended ?

Salut gile
J'ai trouvé la solution pour lire les fichiers xlxs, mais maintenant mon problème et que j'écris que dans la cellule A1.
Par contre, je ne comprends pas ton histoire qu'une colonne doit être d'un même type (réel, entier, etc...)
Si je lis la cellule A1 par exemple et que c'est un string, j'ai un string.
Si je lis la cellule A3 par exemple est que c'est un chiffre, j'ai un réel;
J'évite pour l'instant de lire des plages type A3:C8, car cela ne fonctionne pas correctement, il ne lit que la 1er ligne

ps : Tu as une routine pour trouver toutes les feuilles.

@+

[gile]

I was meaning that if you select more than one entry (row) each value in a column is considered as a key which have to be the same type in a database table.
If they're not, ADO wil choose one type (I don't know about which crtiteria) and return null for all datas which type is different.

Je voulais dire que si tu sélectionne plus d'une entrée (ligne) chaque valeur dans une colonne est considérée  comme une clé qui doit être du même type dans une table de base de données.
Si elles ne le sont pas, ADO choisit un type (je ne sais pas suivant quel critère) et retourne null pour toutes les valeurs quin ne sont pas de ce type. Voir ici

[Patrick_35]

Thank you gile

Now I have another problem.
With a xlsx file, I can not write otherwise than in cell A1

@+

[Lee Mac]

Patrick, quick question: is the ADO method quicker than writing using the Excel.Application?

Hi
Yes, using CDO method is faster than using Excel.Application fast and more and it also helps overcome Excel

It remains to find for Excel 2007 as methods change.

@+

Thanks Patrick, I shall have to look into possibly using this method in the future