Import d'access vers excell, bloquage à 65000 entrées [résolu]

[kraenys]

Bonjour la communauté.

Je suis sous excel et access 2007
J'ai un problème concernant l'import d'une table access vers un fichier excel.
Ma table fait approximativement 126000 entrées et je dois l'importer dans un fichier excel.
Je passe donc par "données externes" => "exporter -> excel" et afin de garder une présentation correcte, je coche la case "Exporter les données avec la mise en forme et la mise en page".

Problème, j'ai le message d'erreur suivant: "Vous avez sélectionné plus d'enregistrements qu'il est possible de copier en une fois sur le presse-papier", cette limite étant de 65534.
L'import se fait quand même, mais je n'ai que les 65534 premières entrées.
Par contre, lorsque je fais ce même export sans cocher la case "exporter les données avec la mise en forme et la mise en page", je récupère toutes les données dans une seule feuille sans aucun problème.

Je cherche un moyen d'importer la table complète sur un seul fichier excel. Les conditions sont de respecter la mise en page et la mise en forme de départ et que la table d'arrivée sous excel contenant les 126000 entrées soit en une seule feuille.

Merci d'avance de vos réponses.

 

[Paritec]

Re : Import d'access vers excell, bloquage à 65000 entrées

Bonjour Fred bonjour chris Kraenys
pas le temps mais juste pour saluer les ci-dessus
a+
papou

 

[chris]

Re : Import d'access vers excell, bloquage à 65000 entrées

Bonjour

Pour les liens, un exemple de code

    For Each cellule In Maplage 'Maplage à définir au préalable
     If cellule.Value <> "" Then ActiveSheet.Hyperlinks.Add cellule, cellule.Text
    Next cellule

Reste à adapter pour retirer les # de cellule.Text

 

[Efgé]

Re : Import d'access vers excell, bloquage à 65000 entrées

Bonjour à tous et toutes
Si la solution retenue est l'importation depuis Excel, sous 2003 , il y a des propriétés d'importation qui semblent intéressantes :

Les colonnes automatiquement ajustées, les formats de cellules préservés.
Cordialement

 

[kraenys]

Re : Import d'access vers excell, bloquage à 65000 entrées

Bonjour Chris, Bonjour Efgé. J'ai finalement opté pour un import sans garder la mise et page et en forme depuis access vers excel, ce qui me permet de facilement l'automatiser.

Ensuite, j'utilise une série de macros pour arriver à mes fins.
Je commence par:
Sub Macro_mise_en_page()

Cells.Select
Selection.ColumnWidth = 32
Cells.Replace What:="Lien PMRQ #", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="##Lien vers PMRQ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Qui me permet de remettre les colones à largeur suffisante, puis je modifie la colone des liens hypertext pour ne laisser que le lien.

Ensuite, j'active les 65530 premiers liens hypertext: (j'ai découvert à mes dépends qu'excel 2007 a un souci, il ne peut pas gérer plus de 65530 liens hypertext dans une même feuille de calcul... c'est d'ailleurs pour cette raison que lorsque j'importais depuis access en gardant la mise en page et la mise en forme, je n'importais que les 65530 premières lignes... Il a donc fallu diviser la feuille de calcul de 126230lignes en deux.)

Sub Lien_hypertext()
Dim val As Range
For Each val In Range("d2:d65530")
If val <> "" Then
ActiveSheet.Hyperlinks.Add Anchor:=Range(val.Address), Address:=val.Value
End If
Next
End Sub

Ensuite, vu que je ne peux pas avoir plus d'autres liens hypertext, je créé mon autre feuille de calcul:

Sub Macro_nouvelle_page()

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Feuil1").Select
Sheets("Feuil1").Name = "U_DEPL_requête_BLA_suite"
End Sub

Puis je fais un couper coller du reste de la table qui n'ont pas encore de liens hypertext actif (de 65531 à 126230):

Sub Macro_menu()
Range("A1:AB1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("U_DEPL_requête_BLA_suite").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("U_DEPL_requête_BLA").Select
Range("A65531:AB126230").Select
Application.CutCopyMode = False
Selection.Cut
Sheets("U_DEPL_requête_BLA_suite").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

C'est du pur bidouillage, mais ça me permet d'arriver à mes fins, c'est à dire une table sous excel identique à celle que j'avais sous access, avec tout les liens hypertext actifs. Seul bémol qui viens du logiciel en lui même, j'ai été obligé de le faire en deux feuilles de calcul..


Maintenant, j'ai une dernière chose à faire, c'est de créer une macro qui me permet d'automatiser toutes les autres macros. Un peu comme sous access où on peut faire un chemin entre les macros. Si vous avez une idée de comment procéder sous excel?

Encore merci de votre aide fournie, on touche au but^^

 

[chris]

Re : Import d'access vers excell, bloquage à 65000 entrées

Bonjour

Dans Access tu as 2 langages : macro (qui correspond à ce qui existait dans Excel 4) et VBA (ce que tu as dans Excel depuis la version 5).

En VBA tu peux appeler une autre procédure en mettant tout simplement call suivi du nom de la procédure.
Tu crées une procédure avec tes 3 CALL (ou plus) et c'est cette procédure que tu invoques.

Pour le nombre maxi de liens, où as-tu trouvé l'info ? Je ne la trouve pas, mais je constate effectivement .

 

[kraenys]

Re : Import d'access vers excell, bloquage à 65000 entrées

Merci beaucoup pour l'instruction CALL, c'est précisément ce que je cherchais. Je pourrai ainsi appeler depuis un fichier .bat mon fichier excel en lançant cette procédure qui lancera par la suite toutes les autres.

En cherchant hypertext 65530 sur google, j'ai obtenu l'info sur ce forum, qui s'est confirmé en cherchant un peu plus: limitation du nombre de liens hypertexte dans un fichier? : Forum Excel
Je suppose (sans confirmation) qu'ils ont gardé la gestion de 2003 pour le nombre de liens hypertext, d'où cette limite des 65530 et non pas du million et des poussières comme pour le reste.

Encore merci pour cette instruction, c'est ce qui vas permettre de finaliser ce document.

Bien à vous.

Kraenys

 

[Claude5]

Re : Import d'access vers excell, bloquage à 65000 entrées

Bonjour Paritec

Il me semblais avoir noté lors de la création de mon topic que j'étais sous Excel 2007, au temps pour moi si ça ne s'est pas passé.

Je suis donc bel et bien sous Excel 2007 et non Excel 2003
En espérant que celà permette de trouver une solution à mon problème.

Kraenys

Bonjour, je suis sous Excel 2010 qui a augmenté le nombre de lignes et j'ai le même problème. Je suis obligé de scinder en deux ou en trois ma requête. Il me semble qu'Excel 2010 a augmenté le nombre de lignes à 1048576, les versions anhtérieures étant moins généreuses. Claude

 

[chris]

Re : Import d'access vers excell, bloquage à 65000 entrées

Bonjour

C'est un peu plus compliqué mais on trouve pas mal d'exemples sur le web (sans doute même ici des exemple de Bruno et de micheXLD) : pourquoi, depuis un code VBA dans ACCESS, ne pas piloter l'Excel créé pour lui appliquer dans la foulée les mises en forme et hyperliens ? (Si le temps d'exécution ne gène pas ton process)

Passez par un .bat suppose, il me semble un code, dans le Workbook.open d'Excel.

 

[Staple1600]

Re : Import d'access vers excell, bloquage à 65000 entrées

Bonjour à tous

Pour le nombre maxi de liens, où as-tu trouvé l'info ? Je ne la trouve pas, mais je constate effectivement .

Je l'ai trouvé pour toi, chris ... ou plutôt G..g.e l'a dénichée.

Excel specifications and limits - Excel - Office.com

Voir la 18ème ligne du tableau nommé Worksheet and workbook specifications and limits

 

[chris]

Re : Import d'access vers excell, bloquage à 65000 entrées

Bonjour

Merci Staple. Je crois être allée sur cette page ou alors une proche copie francisée où ma recherche sur le mot "hyper" ou "lien" ne donnait rien.

Bizarre, vous avez dit bizarre...

Alors merci de ce bon lien qui permet aussi de voir que cette limite n'a pas évolué dans 2010 ni 2013...

 

[kraenys]

Re : Import d'access vers excell, bloquage à 65000 entrées

Merci à vous tous pour l'aide que vous m'avez apporté, merci à Staple pour son lien très complet sur les limites d'Excel (qui servira encore d'ici peu je pense )

Je pense que nous pouvons clore ce sujet qui est pour moi résolu, Encore merci à tous.

Kraenys