Conversion Google Sheet à Excel - Traitement de "" dans calculs

Bonjour,

J'ai posé la question ci-dessous dans stackoverflow https://stackoverflow.com/questions/58529868/google-sheet-to-excel-conversion-treating-as-zero mais les réponses reçues ne sont pas très satisfaisantes. J'espère avoir plus de chance sur votre forum.

Je dois convertir des centaines de feuilles Google Sheets à Excel. Dans le but de ne pas encombrer les feuilles originales (Google Sheet), plusieurs cellules contiennent un "" (deux double guillemets sans espace) résultant d'une formule comme =IF(condition;calcul;"").

Google Sheet traite "" comme zero. Par exemple, A1="", B1=3, A1+B1=3.

Quand la feuille Google Sheet est exportée à Excel, Excel donne une erreur "type de données" for A1+B1. L'erreur disparaît si A1 est modifié pour vide ou 0.

Existe-t-il une façon simple de faire en sorte qu'Excel se comporte comme Google Sheet dans ce cas?

Les suggestions sur stackoverflow sont essentiellement de modifier les formules en remplaçant les "" par 0. Ce serait pour moi un travail très long et fastidieux à cause du très grand nombre de feuilles et de formules à modifier. Avez-vous une meilleure idée?

Merci.

André

Sl André,

à tester

Public Sub Rempl()
Dim feuil As Worksheet
For Each feuil In ThisWorkbook.Worksheets
    feuil.Cells.Replace What:="""", Replacement:="0"
Next feuil
End Sub

Merci m3ellem1 pour la suggestion.

Je vais avoir besoin d'aide pour tester le code parce que je suis très familier avec les scripts dans Google Sheet, mais très peu avec VB dans Excel.

Voici donc ce que j'ai fait:

1. J'ai converti un fichier Google Sheet à Excel 2013. C'est un fichier avec 12 feuilles. Après la conversion, plusieurs cellules ont des erreurs "type de données" dues à ce que j'ai décrit dans le post original.

2. J'ai activé l'onglet Développeur et ouvert l'éditeur VB.

3. J'ai inséré un module et j'ai copié le code de Rempl() dans Module 1.

4. J'ai sauvé le fichier dans Test.xlsm.

5. J'ai exécuté la macro Rempl().

Malheureusement, aucune formule n'a été modifiée et les erreurs sont toujours présentes.

Merci.

André

Slt André,

Public Sub Rempl()

Dim feuil As Worksheet

For Each feuil In ThisWorkbook.Worksheets

feuil.Cells.Replace What:="""", Replacement:="0"

Next feuil

End Sub

Bonjour.

Je pense que :

  • Ta Macro n'a pas été exécuté correctement;
  • Tu peux aussi optimiser la suppression des vides ("") en utilisant la fonction if

remplace la ligne suivante

feuil.Cells.Replace What:="""", Replacement:="0"

par ceci

If feuil.Cells = Empty Or feuil.Cells="" Or feuil.Cells = vbNullString Or IsEmpty(feuil.Cells) Or feuil.Cells="""" Then feuil.Cells = 0

La feuille pouvant aussi comporter des données zonées, il faudra penser à définir la plage de traitement si les données sont zonées dans une plage bien définie.

du genre

Set maPlage = feuil.range("A2:L40")

J'ai fait un test de la macro de m3ellem1 avec un fichier minimal (deux cellules) et ça fonctionne.

Avec un fichier plus complexes (12 feuilles), ça fonctionne sur les formules des 11 feuilles de détails, mais seulement sur une partie des formules de la feuille sommaire (qui est la plus complexe). J'ai peut-être d'autres formules qui se convertissent mal de Sheet à Excel et qui causent ces nouveaux problèmes. Je regarderai de plus près quand j'aurai le temps.

@m3ellem1:

C'est un fichier que j'aimerais mieux ne pas rendre public. Si je n'arrive pas à résoudre le problème, est-ce que je pourrai te l'envoyer personnellement?

@ Nathalie Charette:

J'obtiens une erreur 7 de mémoire insuffisante quand je fais le changement suggéré. Aucune cellule n'est modifiée.

Merci.

André

Pour ma fonction, je demande à la macro d'aller cellule après cellule dans toute une feuille.

Sachant qu'une feuille a plusieurs cellules, remplir 0 partout où la macro trouve le vide demande une très grande capacité. D'où je t'ai suggéré de zoner la plage qui contient les données avant de boucler la dessus. Sinon, impossible

@m3ellem1:

C'est un fichier que j'aimerais mieux ne pas rendre public. Si je n'arrive pas à résoudre le problème, est-ce que je pourrai te l'envoyer personnellement?

Tu peux faire ne serait-ce qu'une copie partielle de ton doc pour qu'on puisse t'aider en palpant du doigt la réalité du problème

Bonjour tout le monde;

@m3ellem1:

C'est un fichier que j'aimerais mieux ne pas rendre public. Si je n'arrive pas à résoudre le problème, est-ce que je pourrai te l'envoyer personnellement?

oui bien sur

Bonjour,

Il aurait aussi été possible d'effacer les "" dans google sheets : le résultat n'aurait rien modifié dans sheets et l'importation n'aurait pas causé d'erreurs dans excel.

J'ai fait un essai avec

function myFunction() {

var feuilles = SpreadsheetApp.getActiveSpreadsheet().getSheets()
feuilles.forEach(function(feuille){
 var cellules = feuille.getRange(1,1,feuille.getLastRow(),feuille.getLastColumn())
  var formules = cellules.getFormulas()
  const regex = new RegExp(';["]{2}', 'g')
  var li = 0
  formules.forEach(function(ligne){
    li++
    var co = 0
    ligne.forEach(function(formule){
      co++
      try{
        if(regex.test(formule)){
          formule = formule.replace(regex,';')
          feuille.getRange(li,co).setFormula(formule)
        }
      }
      catch(e){}
    })
  })
})

}

à dupliquer sur chaque fichier. Le principe est que, dès que l'on rencontre ;"" alors on efface le "" pour laisser à blanc

j'ai mis aussi cette réponse (après l'avoir complètement testée sur un simple jeu d'essai) sur stackoverflow

Rechercher des sujets similaires à "conversion google sheet traitement calculs"