[VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

R@chid

XLDnaute Barbatruc
Bonsoir tout le monde,
Ce R@chid ne va pas cesser de poser les question ??
:p:eek: Non je ne vais pas cesser..

Comme vous allez voir sur le fichier joint, ( mes amis sur le forum m'ont beaucoup aidé à le faire ), je peux créer un nouveau devis sur un nouvel onglet, imprimer les devis après filtre, récupérer les montants TTC et HT des nouveaux devis, mais le problème avec les devis sur le même fichier c'est que après un certain temps il y aura un problème de ralentissement de fichier avec 200 ou 300 devis, alors je me demande si on peut faire la même chose mais cette fois avec des devis sur un fichier chacun donc à la place de créer un nouvel onglet, créer un nouveau classeur avec un seul onglet (Onglet et Classeur portant le même nom) tout en gardant la possibilité d'imprimer ces classeurs après filtres et en récupérant les montant TTC/HT après l'enregistrement de ces derniers..

Je pense que j'ai appris à rédiger les questions ;)

@ + +
 

Pièces jointes

  • Rachid_XLD2.xlsm
    55.1 KB · Affichages: 119
Dernière édition:

Victor21

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,

Je ne pense pas que ce soit du temps perdu :cool:
Essaie :

VB:
...
Wiith ActiveWorkbook.Sheets(1)
.Unprotect
    .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                 UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
    .Name = Left(NomSht, 31)
       .Range("J1") = NomSht
    .Range("J2") = ShtTdB.Range("B" & LigSel)
    .Range("B1") = ShtTdB.Range("C" & LigSel)
    .Range("B2") = ShtTdB.Range("D" & LigSel)
       .Range("H44") = ShtTdB.Range("E" & LigSel)
    .Parent.SaveAs fich
  End With
...
 
Dernière édition:

R@chid

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,
Un bug,
Victor21.png

Oui c'est vrai, c'est pas un temps perdu.

@ + +
 

job75

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Bonjour R@chid, Patrick, le forum,

Si c'est le classeur qui est protégé :

Code:
'---
ThisWorkbook.Unprotect "Rachid" 'mot de passe
With Sheets("DEVIS_V")
   .Visible = True
   .Copy
   .Visible = False
End With
ThisWorkbook.Protect "Rachid"
'---
En passant, j'ai créé un fichier à partir du fichier (5) avec 1000 lignes en colonne A.

La macro Workbook_Activate s'exécute en 4,5 secondes sur Win7 - Excel 2003.

A+
 

job75

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,

S'il y a un très grand nombre de lignes dans la base, la mise à jour de tous les montants à chaque activation du fichier ne paraît pas une bonne solution.

Il vaut mieux entrer les formules en colonnes I et J lors de la création du devis :

Code:
fich = Dir(fich & ".xls*")
ShtTdB.Cells(LigSel, 9) = "=VLOOKUP(""*TTC*"",'[" & fich & "]" & Left(NomSht, 31) & "'!A1:J200,10,0)"
ShtTdB.Cells(LigSel, 10) = "=VLOOKUP(""*HT*"",'[" & fich & "]" & Left(NomSht, 31) & "'!A1:J200,10,0)"
et les y laisser en permanence.

Avec 1000 lignes la mise à jour des liaisons à l'ouverture du fichier est instantanée.

Si des formules sont effacées par inadvertance ou si des fichiers Devis sont supprimés on pourra toujours lancer cette macro pour mettre à jour les colonnes I et J :

Code:
Sub Formules_Montants()
'Touches Ctrl+M pour lancer la macro
Dim t, chemin As String, derlig As Long, P As Range, i As Long, fich As String
t = Timer 'facultatif bien sûr
chemin = ThisWorkbook.Path & "\" 'à adapter
derlig = Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & derlig) = "" Then derlig = derlig - 1
Set P = Range("A3:A" & derlig)
Application.ScreenUpdating = False
Application.EnableEvents = False
For i = 1 To P.Count
  fich = Dir(chemin & P(i) & ".xls*")
  P(i, 9).Resize(, 2).ClearContents
  If fich <> "" Then
    P(i, 9) = "=VLOOKUP(""*TTC*"",'" & chemin & "[" & fich & "]" & Left(P(i, 1), 31) & "'!A1:J200,10,0)"
    P(i, 10) = "=VLOOKUP(""*HT*"",'" & chemin & "[" & fich & "]" & Left(P(i, 1), 31) & "'!A1:J200,10,0)"
  End If
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "Durée d'exécution " & Format(Timer - t, "0.00 \s")
End Sub
Sur 1000 lignes sa durée d'exécution est de [Edit] 5,5 secondes sur Excel 2003.

Fichier (6).

A+
 

Pièces jointes

  • Rachid_XLD(6).xls
    115 KB · Affichages: 73
Dernière édition:

job75

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,

Quand je disais que la mise à jour des liaisons à l'ouverture du fichier était instantanée c'est parce qu'en fait il n'y avait que 3 fichiers Devis.

J'ai donc créé 1000 fichiers Devis différents.

L'ouverture est alors un peu plus longue, elle est de l'ordre de 2 secondes et est due au poids du fichier.

Avec 1000 lignes et les formules de liaisons toutes différentes en colonnes I et J le fichier pèse 1,27 Mo.

Sans formules en colonnes I et J il ne pèse que 258 ko...

Y-a-t-il une limite au nombre de fichiers en liaisons ? je pense que la limite est seulement la quantité de mémoire disponible.

A toi de voir R@chid ce que tu préfères, le fichier (5) ou le fichier (6).

A+
 

job75

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,

Pour résumer, tu as le choix entre :

- un fichier (5) "léger" mais dont l'ouverture ou l'activation sont assez longues par le recalcul des colonnes I et J

- un fichier (6) "lourd" (à cause des formules) mais dont l'ouverture est plus rapide.

A+
 

R@chid

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Bonjour les amis,
Salut Job75 :)
Merci pour ton retour
Merci d'abord pour la dé-protection, oui je veux protéger le classeur..
En ce qui concerne la limite de nombre de fichiers, j'ai changé de "Nouvel Onglet" vers "Nouveau Fichier" pour ne pas être limiter..
Je pense à changer la numérotation des devis de xxx-aa en xxxxx pour ne pas être limiter et pour pouvoir utiliser un seul fichier pour 10 ans ou plus, je sais qu'avec le xxx-aa on on peut le faire sur le même fichier mais j'aime pas.
Pour l’année 2013, la société n'a fait que 68 devis, mais je prévoie plus pour les années prochaines, une estimation de 200 devis maximum, pour les factures c'est moins car parfois on facture 2 ou 3 devis en une seule facture.

Je préfère le fichier (5) car je ne voie pas de formules en I:J, mais si tu voies que le (6) est plus rapide ok pourquoi pas, même s'il m'affiche "Formule de colonne calculée incohérente" après l’exécution de macro, du coté esthétique j'aime pas, je sais que je peux aller sur les options d'Excel pour désactiver mais je sais que c'est un problème de "$" j'ai essayé d'en ajouter sans succès..

N'est-il pas possible d’exécuter la macro du fichier (5) comme tu l'as faite pour le fichier (6) ??
Une fois exécutée, on fait copier/coller les montants, et comme tu l'as envisagé si des valeurs sont effacées par inadvertance ou si des fichiers Devis sont supprimés on pourra toujours lancer cette macro pour mettre à jour les colonnes I et J..

Merci..


@ + +

Edit :
Job75_3.jpg

Comment on peut être à la fois connecté et déconnecté ???
Ne me dit pas que c'est un code VBA ??;)

Je comprends bien ce qui s'est passé, je rigole..

@ + +
 
Dernière édition:

job75

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,

Pour le fichier (5) on peut bien sûr remplacer la Workbook_Activate par une macro lancée manuellement.

Mais alors on ne saura jamais à tout moment si les valeurs en colonnes I et J sont correctes.

Pour le fichier (6), si un fichier source a été supprimé il y aura un message Excel à l'ouverture.

On peut par ailleurs protéger les formules en verrouillant les cellules I:J et en protégeant la feuille.

A+
 

R@chid

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,
Pour le fichier (6), si un fichier source a été supprimé il y aura un message Excel à l'ouverture.
On peut par ailleurs protéger les formules en verrouillant les cellules I:J et en protégeant la feuille.

Oui, j'ai essayé mais j'ai trouvé une difficulté de faire le tri et filtre ou d’insérer de lignes dans le tableau même l’exécution de la Macro par Ctrl+M, même en cochant ces derniers pendant le verrouillage de la feuille..

@ +
 

job75

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,

Oui, protéger la feuille n'est pas une très bonne idée.

On peut par exemple mettre une MFC (rouge) pour visualiser les cellules non numériques en colonnes I:J avec la formule :

Code:
=(LIGNE()>2)*NBCAR($A1)*NON(ESTNUM(I1))
Et/ou écrire dans la macro Formules_Montants :

Code:
P(i, 9).Resize(, 2) = "Fichier absent"
Fichier (6 bis).

A+
 

Pièces jointes

  • Rachid_XLD(6 bis).xls
    116 KB · Affichages: 66

R@chid

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,
Merci Job75 :)
R@chid = Casse-Tête ;)

- Je ne veux pas voir ces trucs là
001.png

- A l'insertion d'une nouvelle ligne, il met le montant du premier devis en attendant la création et l’exécution de la Macro Formules_Montants
002.png

@ te relire
 

R@chid

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,
Salut Job75,
Sans te casser la tête, je vais opter pour le fichier (5) qui apparemment va satisfaire mes besoins pour 2 ou 3 ans je pense, d'ici 3 ans je vais penser à changer l’idée, j’espère être à la hauteur pour faire comme cette orange..
citron-press-.jpg

Merci de ta bienveillance, c'est impeccable ton aide.

@ + +
 

job75

XLDnaute Barbatruc
Re : [VBA] Nouveau devis sur un nouveau fichier et pas nouvel Onglet

Re,

Pour le triangle vert il s'agit d'une vérification d'erreur (qui n'en n'est pas une).

Et en organisation tableau les formules se recopient sur la nouvelle ligne.

Tout ça est facile à éviter : onglet Fichier => Options => Formules, et décocher les 2 options :

- "Saisie semi-automatique de formule"

- "Formule de colonne calculée incohérente dans des tableaux".

A+
 

Discussions similaires

Statistiques des forums

Discussions
311 733
Messages
2 082 019
Membres
101 872
dernier inscrit
Colin T