Définir un nom de zone depuis Access

[poussin2003]

Bonjour,
Je suis en train de créer un outil de suivi qui utilise deux logiciels :
- Access pour le stockage et l'exploitation des données (requêtes)
- Excel pour la restitution graphique des résultats

J'ai créé un fichier qui contient les graphs, et dans lequel Access doit venir coller les données (les graphs se mettant à jour)
Ma contrainte principale étant la suivante : je ne suis pas sûr que les tableaux exportés d'access feront toujours la même taille. Je dois donc passer par des graphs qui se basent sur des "zones" et non sur un adressage fixe.

L'idée est de redéfinir mes zones à chaque export pour que les graphs soient à jour.
Voici une macro qui marche sous Excel
zone = "=" & onglet & "!R" & lig & "C" & col & ":R" & I - 1 & "C" & col + J - 1
ActiveWorkbook.Names.Item(Données).Delete
ActiveWorkbook.Names.Add Name:=Données, RefersToR1C1:=zone

Avec onglet, lig, col, I, J et Données des variables que je récupère du début de la macro.

J'ai donc essayé d'adapter ça à Access Mais si je mets ça dans Access, mais il me renvoie une erreur.
zone = "=" & xlApp.ActiveSheet.Name & "!R" & lig & "C" & col & ":R" & I - 1 & "C" & col + J - 1
xlApp.ActiveWorkbook.Names.Item(Données).Delete
xlApp.ActiveWorkbook.Names.Add Name:=Données, RefersToR1C1:=zone

Quelqu'un saurait-il ce que je ne fais pas bien ?

Merci d'avance.

 

[BOISGONTIER]

Re : Définir un nom de zone depuis Access

Bonjour,

Exemple de création de graphe Excel à partir d'Access.
Remarque: Il est + simple de créer des graphes directement dans Access.

Private Sub B_ole_Click()
Dim bd As Database
Dim rs As Recordset
Dim i
Set xl = CreateObject("excel.Application")
xl.Workbooks.Add
xl.Visible = True
xl.ActiveWindow.DisplayGridlines = False

sql = "Select * FROM RG_ca_vendeurs"
Set bd = CurrentDb
Set rs = bd.OpenRecordset(sql)
xl.ActiveSheet.Cells(1, 1) = "Nom"
xl.ActiveSheet.Cells(1, 2) = "Prix"
i = 2
Do While Not rs.EOF
  xl.ActiveSheet.Cells(i, 1) = rs("NomVendeur")
  xl.ActiveSheet.Cells(i, 2) = rs("Ca")
  rs.MoveNext
  i = i + 1
Loop

xl.ActiveSheet.Range("B2").Select
xl.Selection.CurrentRegion.Select
xl.Selection.Interior.ColorIndex = 36
xl.ActiveSheet.Range("A2").ColumnWidth = 30
xl.Charts.Add

xl.ActiveChart.ChartType = 54
With xl.ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Grahique Généré par OLE"
End With
xl.ActiveChart.Axes(xlValue).TickLabels.Font.Size = 8
xl.ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 8
xl.DisplayAlerts = False
xl.ActiveWorkbook.SaveAs Application.CurrentProject.Path & "\graphe.xls"
xl.Quit
Set xl = Nothing
MsgBox "J'ai crée un fichier Graphe.xls"
End Sub

JB

 

[poussin2003]

Re : Définir un nom de zone depuis Access

Bonjour,
Tout d'abord merci pour cette première réponse si rapide.

En fait, je suis parti sur une idée d'avoir une maquette Excel pour deux raisons :
1) je maîtrise bien mieux les graphs sous Excel que dans Access (je suis un grand débutant sous Access).
Or la création d'Etats ne me semblent pas très intuitive, surtout que je vais avoir 7 ou 8 graphiques sur mon tableau de bord.

2) les personnes qui devront accéder à ce tableau ne disposent pas d'Access, or il me faudra leur envoyer ce fichier par mail (Excel étant quant à lui installé sur tous nos postes)

Etant donné le nombre de graphiques, je pense qu'il est plus performant de ne pas les recréer à chaque fois.
De plus, comme il s'agit de la mise en place de ce tableau de bord, je pressens que je vais devoir y apporter pas mal de modifs dans les premiers mois. Par conséquent, je pense que j'y passerai moins de temps s'il me suffit de modifier les graphs dans Excel que du code dans Access.

Voici mon code entier de la macro dans Access
Le problème se situe à la fin de 'Function ExportDatas' quand je veux redéfinir ma zone dans Excel : il me renvoie une erreur d'éxécution '1004'

Désolé pour le jeu de piste mais j'ai segmenté ma macro initiale pour éviter de devoir écrire une fonction d'export pour chaque tableau (j'en ai plus qu'une seule comme ça).

Merci d'avance à l'âme charitable qui me sortira de cette impasse.

Function ExportSynthèse()

    Dim xlApp As Excel.Application
    Dim xlSheet As Excel.Worksheet
    Dim xlBook As Excel.Workbook
    Dim t0 As Long, t1 As Long

    t0 = Timer
    folder = "M:\1.Outils de Suivi\test\"
    ficname = InputBox("Sous quel nom sauvegarder le fichier de synthèse?", , "Synthèse")
    alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
    Call OuvertureMaquette(xlApp, xlBook, folder)
    
    'Call ExportDatas(alphabet, xlApp, xlBook, "INFO_FREQUENCY")
    
    'Call ExportDatas(alphabet, xlApp, xlBook, "INFO_CONTACT")
    
    Call ExportDatas(alphabet, xlApp, xlBook, "RQ_ORIGINE_PB")
    
    Call Sauvegarde(xlApp, xlBook, xlSheet, folder, ficname)

    t1 = Timer
    MsgBox "Traitement terminé en " & Format(t1 - t0, "0") & " secondes." & Chr(13) & Chr(13) & "Le fichier est disponible sous " & folder


End Function

Function OuvertureMaquette(xlApp, xlBook, folder)


    'Initialisations
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    'Ouverture de la maquette
    Set xlBook = xlApp.Workbooks.Open(folder & "Maquette.xls")
    

End Function

Function ExportDatas(alphabet, xlApp, xlBook, Données)
    
    Dim I As Long, J As Long
    Dim rec As Recordset
    Dim lig As Long, col As Long
    
    'Définition de la table utilisé
    Set rec = CurrentDb.OpenRecordset(Données, dbOpenSnapshot)

    'Sélection de la zone correspondante dans Excel
    xlApp.Application.Goto Reference:=Données
    xlApp.ActiveCell.Select
     lig = xlApp.ActiveCell.Row
     col = xlApp.ActiveCell.Column
    
    'Mention de ce qui a été exporté
    xlApp.Cells(lig - 1, col) = "Export de " & Données
    
    'Insertion des entetes
    '  .Fields(Index).Name renvoie le nom du champ
    For J = 0 To rec.Fields.Count - 1
        xlApp.Cells(lig, col + J) = rec.Fields(J).Name
    Next J
    
    'Copie des données à partir de la ligne 3
    I = lig + 1
    Do While Not rec.EOF
        For J = 0 To rec.Fields.Count - 1
            ' .Fields(Index).Type renvoie le type du champ
            If rec.Fields(J).Type = dbText Then
                xlApp.Cells(I, col + J).NumberFormat = "@"
                xlApp.Cells(I, col + J) = rec.Fields(J)
                'xlApp.Cells(I, J + 1) = "'" & rec.Fields(J)
            Else
                xlApp.Cells(I, col + J) = rec.Fields(J)
            End If
        Next J
        I = I + 1
        rec.MoveNext
    Loop

    
    'redéfinition de la zone sous Excel
    If col > 26 Then
        firstcol = Mid(alphabet, Int(col / 26), 1) & Mid(alphabet, col - ((Int(col / 26)) * 26), 1)
        Else
        firstcol = Mid(alphabet, col, 1)
    End If
    If (col + J - 1) > 26 Then
        lastcol = Mid(alphabet, Int((col + J - 1) / 26), 1) & Mid(alphabet, (col + J - 1) - ((Int((col + J - 1) / 26)) * 26), 1)
        Else
        lastcol = Mid(alphabet, col + J - 1, 1)
    End If
    zone = xlApp.ActiveSheet.Name & "!$" & firstcol & "$" & lig & ":$" & lastcol & "$" & I - 1
    'zone = "=" & xlApp.ActiveSheet.Name & "!R" & lig & "C" & col & ":R" & I - 1 & "C" & col + J - 1
    xlApp.ActiveWorkbook.Names.Item(Données).Delete
    xlApp.ActiveWorkbook.Names.Add Name:=Données, RefersToR1C1:="=" & zone
    
    
    rec.Close
    Set rec = Nothing

End Function

Function Sauvegarde(xlApp, xlBook, xlSheet, folder, ficname)
    
    ' code de fermeture et libération des objets
    t1 = Timer
    xlBook.SaveAs folder & ficname & ".xls"
    'comment fermer un classeur en enregistrant automatiquement à la sortie
    'xlBook.Close (True) 'on dit qu'on enregistre les modifs du fichiers
    xlApp.Quit
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

    't1 = Timer
    'MsgBox I & " enregistrements", Format(t1 - t0, "0") & " secondes"

End Function