Incrémenter régulièrement un onglet Excel à partir d'une table ACCESS

Re,

Tu me confirmes que je place le code dans une macro de ma base Access ?

* source des données Access => Table `Resultats` de la base `Test.accdb`

* cible Excel => onglet `Resultats` du fichier `VENTES.xlsm`

erreur2 capture

Comme je ne peux pas joindre de base Access dans mes messages, j'ai copié le code dans un excel. Désolée si ça te parait évident, je ne suis vraiment pas à l'aise sur ce projet..!

1test-2.xlsm (16.93 Ko)

M'enfin!

"ThisWorkbook" ne peut pas convenir dans du code Access!!! This - Workbook! Ce - Classeur!

Remplace la ligne par un truc genre :

Connect_xls "C:\repertoire\sous-repertoire\Base_truc_chouette.accdb"

ou bien par :

Connect_xls Access.CurrentProject.Path & "\Base_truc_chouette.accdb"

Encore une fois avec les codes proposés, il faut adapter à la situation! => noms des fichiers, noms des tables, noms des onglets, répertoires, ...

Bonjour Sidwel, Pierre,

nouvelle essai,

Sub Export_After_LastRow()
Dim Fichier As String, NomFeuille As String, texte_SQL As String, rng As String, n As String
Dim cn As Object
Dim Rst As Object

Fichier = '"C:\Users\...\VENTES.xlsx" 'à adapter
NomFeuille =  '"Resultats" 'à adapter

Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Rst.Open texte_SQL, cn, adOpenKeyset, adLockOptimistic
n = "A" & Rst.RecordCount + 2
.Close
End With

Export_VENTES Fichier, NomFeuille, n

End Sub
Function Export_VENTES(Fich As String, NomFeuil As String, cellule As String)
On Error GoTo Export_VENTES_Err

'acSpreadsheetTypeExcel9    8   Microsoft Excel 97 -2007 format
'acSpreadsheetTypeExcel12   9   Microsoft Excel 2010 format

DoCmd.TransferSpreadsheet acExport, 8, "RESULTATS_J", Fich, True, NomFeuil & "!" & cellule

Export_VENTES_Exit:
Exit Function

Export_VENTES_Err:
MsgBox Error$
Resume Export_VENTES_Exit

End Function

@I20100

Merci de persévérer

Mais j'ai une mauvaise nouvelle.. même message d'erreur. C'est un vrai casse tête !

capture

@Pierre

Oui il faut adapter j'ai bien compris et c'est ce que j'essaie de faire et il me semble avoir bien adapté les noms des fichiers/onglets. Je n'avais pas touché à Access il y a 1 mois donc ça rentre petit à petit. Voilà tout.

re,

fait un essai en enlevant

Option Compare Database

toujours pas, c'est terrible J'ai toujours le nom pas valide "Resultats$A20"

Sub Export_After_LastRow() '2nd test 
Dim Fichier As String, NomFeuille As String, texte_SQL As String, rng As String, n As String
Dim cn As Object
Dim Rst As Object

Fichier = "C:\Users\...\VENTES.xlsm"
NomFeuille = "Resultats"

Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Rst.Open texte_SQL, cn, adOpenKeyset, adLockOptimistic
n = "A" & Rst.RecordCount + 2
.Close
End With

Export_VENTES Fichier, NomFeuille, n

End Sub

Function Export_VENTES(Fich As String, NomFeuil As String, cellule As String) '2nd test I20100
On Error GoTo Export_VENTES_Err

'acSpreadsheetTypeExcel9    8   Microsoft Excel 97 -2007 format
'acSpreadsheetTypeExcel12   9   Microsoft Excel 2010 format

DoCmd.TransferSpreadsheet acExport, 8, "Resultats", Fich, True, NomFeuil & "!" & cellule

Export_VENTES_Exit:
Exit Function

Export_VENTES_Err:
MsgBox Error$
Resume Export_VENTES_Exit

End Function

re,

ton fichier excel est bien au format Excel 2007 ?

Dernière proposition, voir démo.

Pour adapter le code à la situation il faut adapter les noms des répertoires, des fichiers, des onglets, des tables, ...

Et je maintiens que la méthode INSERT est particulièrement adapté au besoin.

Pierre

6demo3.zip (40.36 Ko)

re,

malheureusement ce n'est pas possible à partir de Access

selon la doc de MS: https://docs.microsoft.com/fr-ca/office/vba/api/access.docmd.transferspreadsheet

c'est possible uniquement pour l'importation et non pour l'export.

Range Optional Variant Expression de chaîne correspondant à une plage correcte de cellules ou au nom d'une plage dans la feuille de calcul. Cet argument s'applique uniquement à l'importation. Laissez cet argument vide pour importer la feuille de calcul entière. Lorsque vous exportez vers une feuille de calcul, vous devez laisser cet argument vide. Si vous entrez une plage, l'exportation échoue.

malheureusement ce n'est pas possible à partir de Access

selon la doc de MS: https://docs.microsoft.com/fr-ca/office/vba/api/access.docmd.transferspreadsheet

A l'évidence il y a confusion entre :

* DAO (Data Access Objects) dont 'DoCmd.TransferSpreadsheet' fait partie

* et ADO (ActiveX Data Object) qui permet d'utiliser du Sql (et donc INSERT, UPDATE, SELECT, ...)

Pierre

@Pierre

même si cela te parait évident, j'ai besoin d'aide pour adapter la macro stp. Notamment sur les chemins pour la Sub Ecriture_dans_Excel

Est ce que je ne peux pas écrire directement le chemin en dur?

du genre:

Connect_xls "C:\Users\..\VENTES.xlsm" 

Sinon j'ai adapté avec mes noms de fichiers, à savoir table Resultats de ma base test.accdb et mon onglet Resultats de mon fichier excel VENTES.xlsm

Option Explicit

Public Cnx As Object, Rst As Object

Sub Démo()
Dim Req As String, Head As String, S As String
Dim T As Variant, i As Long
Dim Chemin As String, Fichier_Xl As String, Base_Access As String
Dim Onglet As String, Table As String

    ' 3ème partie : lecture d'un accdb
    Base_Access = "Test.accdb"
    Table = "Resultats"
    Connect_xls Access.CurrentProject.Path & "\Test.accdb" ' connexion à Test.accdb
    Req = "SELECT * FROM [" & Table & "]"       ' démo d'un SELECT dans Access
    T = Select_Xls_Accdb(Req)
    Close_xls 1                                 ' ferme la connexion

    ' 4ème partie : ecriture dans Base.xlsx
    Ecriture_dans_Excel T
End Sub

Sub Ecriture_dans_Excel(T As Variant)
Dim Head As String, S As String, i As Long
Dim Chemin As String, Fichier_Xl As String

    Chemin = ThisWorkbook.Path
    Fichier_Xl = "VENTES.xlsm"
    Connect_xls ThisWorkbook.Path & "\" & Fichier_Xl

'    Connect_xls "C:\Users\..\VENTES.xlsm"          ' connexion au fichier excel de destination VENTES.xlsm

    Head = "`Date`, `reference`, `volume`, `nom client`, `designation`, `secteur`,"
    For i = 0 To UBound(T, 1)
        S = CLng(T(i, 0)) & "," & T(i, 1) & "," & TxtXl(T(i, 2)) & _
            "," & TxtXl(T(i, 3)) & "," & NumXl(T(i, 4))
        Insert_Xls "Resultats", Head, S    ' démo d'INSERT : injection de la ligne dans le fichier VENTES.xlsm
    Next i
    Close_xls                                                  ' ferme la connexion
End Sub

merci

@I20100

ma version Excel c'est 2010..

re,

Comme je le disais dans mon message précédent,

L’argument Range n’est pas possible lors de l’export avec la propriété DoCmd mais seulement lors de l’import,

Fait le test suivant, tu verras que si n = 0 l’export se fait bien pourtant l’adresse A0:E0 n’est pas possible.

Tandis que si = 1 cela provoque une erreur de dépassement.

Sub Export_After_LastRow()
Dim Fichier As String, NomFeuille As String, n As Long

Fichier = "C:\Users\isabelle\Documents\TestAccess\Base1.xlsx" 'à adapter
NomFeuille = "Feuil1" 'à adapter
n = 0
Export_VENTES Fichier, NomFeuille, n
End Sub

Function Export_VENTES(Fich As String, NomFeuil As String, ligne As Long) '2nd test I20100
On Error GoTo Export_VENTES_Err
'acSpreadsheetTypeExcel12   9   Microsoft Excel 2010 format

MaTable = "Donnees_demo" 'à adapter
dest = NomFeuil & "!" & "A" & ligne & ":E" & ligne

DoCmd.TransferSpreadsheet acExport, 9, MaTable, Fich, False, dest

Export_VENTES_Exit:
Exit Function

@Sidwel

Est ce que je ne peux pas écrire directement le chemin en dur?

du genre:

Connect_xls "C:\Users\..\VENTES.xlsm" 

1/ Bien sûr, le chemin (complet!) peut être en dur

2/ pour un Insert le nombre le données doit correspondre au nombre d'entêtes

Head = "`Date`, `reference`, `volume`, `nom client`, `designation`, `secteur`"

/!\ attention pas de virgule à la fin après `secteur`

6 entêtes => 6 valeurs , et avec les précuations suivantes :

  • date traduite en entier long => par ex CLng(T(i, 0))
  • valeur numérique sans guillemet
  • valeur numérique décimale la virgule "," remplacée par un point "." => par ex NumXl(T(i, 4))
  • valeur texte avec guillemets => par ex TxtXl(T(i, 3))

Tu peux le faire! La Force est avec toi!

Pierre

Hello @Pierre

Donc, j'ai 7 entêtes finalement :

Head = "`Date`, `reference`, `volume`, `prev`, `reel`, `secteur`, `commentaire`"

Je n'ai plus d'erreurs dans la Sub Ecriture_dans_Excel

Sub Ecriture_dans_Excel(T As Variant)
Dim Head As String, S As String, i As Long
Dim Chemin As String, Fichier_Xl As String

    Connect_xls "C:\Users\...\VENTES.xlsm" ' connexion au fichier excel de destination VENTES.xlsm

    Head = "`Date`, `reference`, `volume`, `prev`, `reel`, `secteur`, `commentaire`"
    For i = 0 To UBound(T, 1)
        S = CLng(T(i, 0)) & "," & TxtXl(T(i, 1)) & "," & T(i, 2) & "," & T(i, 3) & _
            "," & T(i, 4) & "," & TxtXl(T(i, 5)) & "," & TxtXl(T(i, 6)) & ","
        Insert_Xls "Resultats", Head, S    ' démo d'INSERT : injection de la ligne dans le fichier VENTES.xlsm
    Next i
    Close_xls                                                  ' ferme la connexion
End Sub

J'ai déclaré le chemin du fichier excel en dur. Je pense faire une erreur en utilisant la variable Onglet du coup dans Insert_Xls ?

Peux tu me donner ton avis?

Sub Insert_Xls(Onglet As String, Head As String, Data As String)
Dim Req As String

Onglet = "Resultats"

    Req = "INSERT INTO [" & Onglet & "$] (" & Head & ") VALUES (" & Data & ")"
    Cnx.Execute Req
End Sub

Et du coup l'erreur dans la Sub Connect_xls est peut être liée ?

capture

Bah, déjà "C:\Users\...\VENTES.xlsm" ce n'est pas un chemin complet, il faut compléter (cf les 3 petits points entre les slash).

Par ailleurs, inutile de définir la variable "Onglet" dans "Sub Insert_Xls" puique cette variable est transmise en tant qu'argument :

=> Sub Insert_Xls(Onglet As String, Head As String, Data As String)

=> appel : Insert_Xls "Resultats", Head, S

Enfin, il y a une virgule de trop à la fin de :

"," & T(i, 4) & "," & TxtXl(T(i, 5)) & "," & TxtXl(T(i, 6)) & ","

il faut préférer :

"," & T(i, 4) & "," & TxtXl(T(i, 5)) & "," & TxtXl(T(i, 6))

Bonjour, et désolée pour le retard

Bien sûr j'ai écris le chemin dans son intégralité.

J'ai réessayé avec tes remarques, et ça fonctionne!!!!!!!!!!!!!!!!!!!!!!! merci merci

Avant de clôturer le topic, j'ai une dernière interrogation:

quand je lance ma macro, ça fonctionne nickel --> OK

si entre temps, j'efface une ligne de mon fichier excel de destination (exemple ligne 10), alors quand je relance ma macro, j'ai l'impression qu'il considère toujours que cette ligne 10 n'est pas vide malgré le fait que j'ai effacé les données

--> et donc la macro me copie-colle les valeurs la ligne d'après (11).. pourtant comme j'ai effacé les données, il devrait considérer qu'il peut réecrire des données dessus en ligne 10 ?

Je ne sais pas si je suis très claire dans mes explications..

Bonjour Sidwel,

La description est très claire et l'effet est très classique.

En effet, Excel est un filou!

Exactement comme tu l'as pensé instinctivement, il ne considère pas toujours les cellules déjà utilisées mais effacées comme étant vides.

Pour vider complètement une ligne il est mieux de la supprimer (si c'est possible).

Sinon, une autre possibilité à la lecture d'un fichier, est de faire un "SELECT `Champs`,... blabla ... WHERE NOT ISNULL(`Champs`)" ce qui élimine les fameuses cellules vides de la colonne "Champs"

Content que tu aies trouvé une solution à ta question!

Pierre

Rechercher des sujets similaires à "incrementer regulierement onglet partir table access"