utiliser la feuille active dans Excel

Version imprimable

12/06/2019, 13h38
sorbetframboise

utiliser la feuille active dans Excel

Bonjour,

Est il possible de ne pas spécifier la feuille d'un document Excel pour que VB.net modifie les cases sur la feuille active dans Excel

j'ai essayé de remplacer sheet(1) par ActiveSheet mais mon code n'a pas fonctionné

Bonjour,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Imports System.Runtime.InteropServices
Module Module1
    Sub Main()
        Dim XlApp As Object = Nothing, xls As Object = Nothing, ActiveSheet As Object = Nothing
        XlApp = GetObject("Excel.Application")
        XlApp.visible = True
        xls = XlApp.Workbooks.Open(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\Fichier.xlsm")
        ActiveSheet = xls.ActiveSheet
        xls.Close(False)
        XlApp.Quit()
        Dispose(ActiveSheet)
        Dispose(xls)
        Dispose(XlApp)
    End Sub
    Public Sub Dispose(ByRef Obj As Object)
        Marshal.FinalReleaseComObject(Obj)
        GC.SuppressFinalize(Obj)
        GC.ReRegisterForFinalize(Obj)
        GC.Collect()
        Obj = Nothing
    End Sub
    Friend Function GetObject(ByVal App As String) As Object
        Try
            Dim Apli As Object = Marshal.GetActiveObject(App)
            Return Apli
        Catch ex As Exception
            Return CreateObject(App)
        Finally
        End Try
    End Function
    Friend Function CreateObject(ByVal app As String) As Object
        Try
            Dim AppType As Object = Type.GetTypeFromProgID(app)
            Dim ApplInst As Object = Activator.CreateInstance(AppType)
            Return ApplInst
        Catch ex As Exception
            Return Nothing
            Return False
        Finally
        End Try
    End Function
End Module

1 pièce(s) jointe(s)

J'ai modifié mon code initial et voila mon code (qui m'annonce des erreurs)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        With GetObject("Excel.Application")
            '.visible = True'
            With .Workbooks.Open(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\test macro.exe")
                ActiveSheet = xls.ActiveSheet
                ActiveSheet.Cells(5, 2).Value = TextBox1.Text
                TextBox2.Text = .Sheets(1).Cells(1, 1).Value
                .application.Run("test")
                .application.DisplayAlerts = False
                .Save()
                .application.DisplayAlerts = True
                .Close()
 
            End With
            .Quit()
        End With
    End Sub

l'erreur annoncer est celle ci :
Pièce jointe 482873

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim XlApp As Object = Nothing, Workbook As Object = Nothing, ActiveSheet As Object = Nothing
        XlApp = GetObject("Excel.Application")
        XlApp.visible = True
        Workbook = XlApp.Workbooks.Open(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\Dossier final - Copie.xlsm")
        ActiveSheet = Workbook.ActiveSheet
        ActiveSheet.Cells(5, 2).Value = TextBox1.Text
        TextBox2.Text = ActiveSheet.Cells(1, 1).Value
        ActiveSheet.application.Run("test")
        XlApp.DisplayAlerts = False
        Workbook.Save()
        Workbook.Close(False)
        XlApp.DisplayAlerts = True
        XlApp.Quit()
        Dispose(ActiveSheet)
        Dispose(Workbook)
        Dispose(XlApp)
    End Sub
    Private Sub Dispose(ByRef Obj As Object)
        Marshal.FinalReleaseComObject(Obj)
        GC.SuppressFinalize(Obj)
        GC.ReRegisterForFinalize(Obj)
        GC.Collect()
        Obj = Nothing
    End Sub
 
    Friend Function GetObject(ByVal App As String) As Object
        Try
            ' log.Debug("Begin...")
            Return Marshal.GetActiveObject(App)
        Catch ex As Exception
            Return CreateObject(App)
            'Log.Error("", exRD)
        Finally
            ' log.Debug("End...")
        End Try
    End Function
    Friend Function CreateObject(ByVal app As String) As Object
        Try
            ' log.Debug("Begin...")
            Dim AppType As Object = Type.GetTypeFromProgID(app)
            Dim ApplInst As Object = Activator.CreateInstance(AppType)
            Return ApplInst
        Catch ex As Exception
            Return Nothing
            'Log.Error("", exRD)
        Finally
            ' log.Debug("End...")
        End Try
    End Function
 
 
End Class

12/06/2019, 18h41
Phil Rob

Tu peux sélectionner une feuille donnée comme ci-dessous et puis tu peux accéder à ses données :

XLAPP.Sheets("Feuil1").Select()
12/06/2019, 21h23
sorbetframboise

J’essayerais ça demain merci
13/06/2019, 13h50
sorbetframboise

Bonjour,

Excuse moi dysorthographie mais à quoi servent les "dispose" ? Car le programme marche seulement quand je les enlève.

bonjour,
si tu regarde dans le gestionnaire de tache tu verras qu'Excel ne disparait pas quand tu fais quitter!

dispose permet de libérer les objet!

renomme Dispose en Dspose
Code:

1 2 3 4 5 6 7 Private Sub Dspose(ByRef Obj As Object) Marshal.FinalReleaseComObject(Obj) GC.SuppressFinalize(Obj) GC.ReRegisterForFinalize(Obj) GC.Collect() Obj = Nothing End Sub