Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Développement Développement Visual Basic Ecrire dans excel a partir de VB.Net

Ecrire dans excel a partir de VB.Net

4 réponses
Avatar
Donald Pelletier
Bonjour,

J'essaie d'écrire dans une feuille Excel à partir de VB.net. J'utilise
Visual Studio Architect Entreprise et Excel 2003 et j'obtiens le message
suivant:



Object reference not set to to an instance of an object.

Voici le code VB:

Quelqu'un peut-il me donner une indice de la cause du problème.


Merci de votre aide

Imports Microsoft.Office.Core

Public Class Form1

Inherits System.Windows.Forms.Form

.

.

.

' Open Application

Dim objApp As Excel.Application

Dim objBook As Excel._Workbook

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim objBooks As Excel.Workbooks

Dim objSheets As Excel.Sheets

Dim objSheet As Excel._Worksheet

Dim range As Excel.Range

If chkNieuw.Checked = True Then

'New File

Try

ObjApp = New Excel.Application

objBooks = ObjApp.Workbooks

objBook = objBooks.Add

objSheets = objBook.Worksheets

objSheet = objSheets(1)

'Start Cell

range = objSheet.Range("A1", Reflection.Missing.Value)

range = range.Resize(20, 20)

'Fill in

ObjApp.Application.Cells(1, 1).Value = "Name"

ObjApp.Application.Cells(2, 1).Value = "First Name"

ObjApp.Application.Cells(3, 1).Value = "City"

ObjApp.Application.Cells(1, 2).Value = txtName.Text

ObjApp.Application.Cells(2, 2).Value = txtFirst.Text

ObjApp.Application.Cells(3, 2).Value = txtCity.Text

'Visible

ObjApp.Visible = True

ObjApp.UserControl = True

Catch ex As Exception

MsgBox("Programerror:" & ex.Message)

End Try

Else

Try

'A excisted file -> Fill in

Dim ObjApp As New Excel.Application

Dim ObjWorkSheet As Excel.Worksheet

'Open File

'YOU NEED TO CREATE THIS FILE BEFORE YOU CAN RUN THIS

ObjApp.Workbooks.Add()

ObjApp.Workbooks.Open("C:\Test.xls")

Dim ObjW = ObjApp.ActiveWorkbook

ObjWorkSheet = ObjApp.Worksheets(1)

ObjWorkSheet.Application.Cells(1, 1).Value = "Name"

ObjWorkSheet.Application.Cells(2, 1).Value = "First Name"

ObjWorkSheet.Application.Cells(3, 1).Value = "City"

ObjWorkSheet.Application.Cells(1, 2).Value = txtName.Text

ObjWorkSheet.Application.Cells(2, 2).Value = txtFirst.Text

ObjWorkSheet.Application.Cells(3, 2).Value = txtCity.Text

'Visible

ObjApp.Visible = True

ObjApp.UserControl = True

Catch ex As Exception

MsgBox("Programerror:" & ex.Message)

End Try

End If

'Close

'YOU NEED TO DO THIS

'If you dont do this, i can swear you, if you shutdown youre computer

'you will have a lot of errors about Excel files that are open :)

range = Nothing

objSheet = Nothing

objSheets = Nothing

objBooks = Nothing

End Sub


--
Amateur de VB
Signaler un problème avec ce contenu

4 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
Eric Vernié [MS]
Bonjour Donald,

Sur quelle ligne exactement tu as le problème ?

Cordialement

Eric Vernié
Microsoft France


"Donald Pelletier" wrote in message
news:
Bonjour,

J'essaie d'écrire dans une feuille Excel à partir de VB.net. J'utilise
Visual Studio Architect Entreprise et Excel 2003 et j'obtiens le message
suivant:



Object reference not set to to an instance of an object.

Voici le code VB:

Quelqu'un peut-il me donner une indice de la cause du problème.


Merci de votre aide

Imports Microsoft.Office.Core

Public Class Form1

Inherits System.Windows.Forms.Form

.

.

.

' Open Application

Dim objApp As Excel.Application

Dim objBook As Excel._Workbook

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim objBooks As Excel.Workbooks

Dim objSheets As Excel.Sheets

Dim objSheet As Excel._Worksheet

Dim range As Excel.Range

If chkNieuw.Checked = True Then

'New File

Try

ObjApp = New Excel.Application

objBooks = ObjApp.Workbooks

objBook = objBooks.Add

objSheets = objBook.Worksheets

objSheet = objSheets(1)

'Start Cell

range = objSheet.Range("A1", Reflection.Missing.Value)

range = range.Resize(20, 20)

'Fill in

ObjApp.Application.Cells(1, 1).Value = "Name"

ObjApp.Application.Cells(2, 1).Value = "First Name"

ObjApp.Application.Cells(3, 1).Value = "City"

ObjApp.Application.Cells(1, 2).Value = txtName.Text

ObjApp.Application.Cells(2, 2).Value = txtFirst.Text

ObjApp.Application.Cells(3, 2).Value = txtCity.Text

'Visible

ObjApp.Visible = True

ObjApp.UserControl = True

Catch ex As Exception

MsgBox("Programerror:" & ex.Message)

End Try

Else

Try

'A excisted file -> Fill in

Dim ObjApp As New Excel.Application

Dim ObjWorkSheet As Excel.Worksheet

'Open File

'YOU NEED TO CREATE THIS FILE BEFORE YOU CAN RUN THIS

ObjApp.Workbooks.Add()

ObjApp.Workbooks.Open("C:Test.xls")

Dim ObjW = ObjApp.ActiveWorkbook

ObjWorkSheet = ObjApp.Worksheets(1)

ObjWorkSheet.Application.Cells(1, 1).Value = "Name"

ObjWorkSheet.Application.Cells(2, 1).Value = "First Name"

ObjWorkSheet.Application.Cells(3, 1).Value = "City"

ObjWorkSheet.Application.Cells(1, 2).Value = txtName.Text

ObjWorkSheet.Application.Cells(2, 2).Value = txtFirst.Text

ObjWorkSheet.Application.Cells(3, 2).Value = txtCity.Text

'Visible

ObjApp.Visible = True

ObjApp.UserControl = True

Catch ex As Exception

MsgBox("Programerror:" & ex.Message)

End Try

End If

'Close

'YOU NEED TO DO THIS

'If you dont do this, i can swear you, if you shutdown youre computer

'you will have a lot of errors about Excel files that are open :)

range = Nothing

objSheet = Nothing

objSheets = Nothing

objBooks = Nothing

End Sub


--
Amateur de VB


Avatar
Patrice
L'objet concerné est Nothing. Vérifier son initialisation avant usage ou
qu'une méthode Excel ne retourne pas un objet vide (peut-être par exemple si
une plage nommée n'existe pas ou quelque chose d'approchant).

--
Patrice

"Donald Pelletier" a écrit dans le message de
news:
Bonjour,

J'essaie d'écrire dans une feuille Excel à partir de VB.net. J'utilise
Visual Studio Architect Entreprise et Excel 2003 et j'obtiens le message
suivant:



Object reference not set to to an instance of an object.

Voici le code VB:

Quelqu'un peut-il me donner une indice de la cause du problème.


Merci de votre aide

Imports Microsoft.Office.Core

Public Class Form1

Inherits System.Windows.Forms.Form

.

.

.

' Open Application

Dim objApp As Excel.Application

Dim objBook As Excel._Workbook

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim objBooks As Excel.Workbooks

Dim objSheets As Excel.Sheets

Dim objSheet As Excel._Worksheet

Dim range As Excel.Range

If chkNieuw.Checked = True Then

'New File

Try

ObjApp = New Excel.Application

objBooks = ObjApp.Workbooks

objBook = objBooks.Add

objSheets = objBook.Worksheets

objSheet = objSheets(1)

'Start Cell

range = objSheet.Range("A1", Reflection.Missing.Value)

range = range.Resize(20, 20)

'Fill in

ObjApp.Application.Cells(1, 1).Value = "Name"

ObjApp.Application.Cells(2, 1).Value = "First Name"

ObjApp.Application.Cells(3, 1).Value = "City"

ObjApp.Application.Cells(1, 2).Value = txtName.Text

ObjApp.Application.Cells(2, 2).Value = txtFirst.Text

ObjApp.Application.Cells(3, 2).Value = txtCity.Text

'Visible

ObjApp.Visible = True

ObjApp.UserControl = True

Catch ex As Exception

MsgBox("Programerror:" & ex.Message)

End Try

Else

Try

'A excisted file -> Fill in

Dim ObjApp As New Excel.Application

Dim ObjWorkSheet As Excel.Worksheet

'Open File

'YOU NEED TO CREATE THIS FILE BEFORE YOU CAN RUN THIS

ObjApp.Workbooks.Add()

ObjApp.Workbooks.Open("C:Test.xls")

Dim ObjW = ObjApp.ActiveWorkbook

ObjWorkSheet = ObjApp.Worksheets(1)

ObjWorkSheet.Application.Cells(1, 1).Value = "Name"

ObjWorkSheet.Application.Cells(2, 1).Value = "First Name"

ObjWorkSheet.Application.Cells(3, 1).Value = "City"

ObjWorkSheet.Application.Cells(1, 2).Value = txtName.Text

ObjWorkSheet.Application.Cells(2, 2).Value = txtFirst.Text

ObjWorkSheet.Application.Cells(3, 2).Value = txtCity.Text

'Visible

ObjApp.Visible = True

ObjApp.UserControl = True

Catch ex As Exception

MsgBox("Programerror:" & ex.Message)

End Try

End If

'Close

'YOU NEED TO DO THIS

'If you dont do this, i can swear you, if you shutdown youre computer

'you will have a lot of errors about Excel files that are open :)

range = Nothing

objSheet = Nothing

objSheets = Nothing

objBooks = Nothing

End Sub


--
Amateur de VB


Avatar
Donald Pelletier
Bonjour,

C'est sur la ligne suivante:

objBook = objBooks.Add


par la suite, le programme entre dans la phase "Try" et génère le message
d'erreur"

Merci
--
Amateur de VB


"Eric Vernié [MS]" a écrit :

Bonjour Donald,

Sur quelle ligne exactement tu as le problème ?

Cordialement

Eric Vernié
Microsoft France


"Donald Pelletier" wrote in message
news:
> Bonjour,
>
> J'essaie d'écrire dans une feuille Excel à partir de VB.net. J'utilise
> Visual Studio Architect Entreprise et Excel 2003 et j'obtiens le message
> suivant:
>
>
>
> Object reference not set to to an instance of an object.
>
> Voici le code VB:
>
> Quelqu'un peut-il me donner une indice de la cause du problème.
>
>
> Merci de votre aide
>
> Imports Microsoft.Office.Core
>
> Public Class Form1
>
> Inherits System.Windows.Forms.Form
>
> .
>
> .
>
> .
>
> ' Open Application
>
> Dim objApp As Excel.Application
>
> Dim objBook As Excel._Workbook
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> Dim objBooks As Excel.Workbooks
>
> Dim objSheets As Excel.Sheets
>
> Dim objSheet As Excel._Worksheet
>
> Dim range As Excel.Range
>
> If chkNieuw.Checked = True Then
>
> 'New File
>
> Try
>
> ObjApp = New Excel.Application
>
> objBooks = ObjApp.Workbooks
>
> objBook = objBooks.Add
>
> objSheets = objBook.Worksheets
>
> objSheet = objSheets(1)
>
> 'Start Cell
>
> range = objSheet.Range("A1", Reflection.Missing.Value)
>
> range = range.Resize(20, 20)
>
> 'Fill in
>
> ObjApp.Application.Cells(1, 1).Value = "Name"
>
> ObjApp.Application.Cells(2, 1).Value = "First Name"
>
> ObjApp.Application.Cells(3, 1).Value = "City"
>
> ObjApp.Application.Cells(1, 2).Value = txtName.Text
>
> ObjApp.Application.Cells(2, 2).Value = txtFirst.Text
>
> ObjApp.Application.Cells(3, 2).Value = txtCity.Text
>
> 'Visible
>
> ObjApp.Visible = True
>
> ObjApp.UserControl = True
>
> Catch ex As Exception
>
> MsgBox("Programerror:" & ex.Message)
>
> End Try
>
> Else
>
> Try
>
> 'A excisted file -> Fill in
>
> Dim ObjApp As New Excel.Application
>
> Dim ObjWorkSheet As Excel.Worksheet
>
> 'Open File
>
> 'YOU NEED TO CREATE THIS FILE BEFORE YOU CAN RUN THIS
>
> ObjApp.Workbooks.Add()
>
> ObjApp.Workbooks.Open("C:Test.xls")
>
> Dim ObjW = ObjApp.ActiveWorkbook
>
> ObjWorkSheet = ObjApp.Worksheets(1)
>
> ObjWorkSheet.Application.Cells(1, 1).Value = "Name"
>
> ObjWorkSheet.Application.Cells(2, 1).Value = "First Name"
>
> ObjWorkSheet.Application.Cells(3, 1).Value = "City"
>
> ObjWorkSheet.Application.Cells(1, 2).Value = txtName.Text
>
> ObjWorkSheet.Application.Cells(2, 2).Value = txtFirst.Text
>
> ObjWorkSheet.Application.Cells(3, 2).Value = txtCity.Text
>
> 'Visible
>
> ObjApp.Visible = True
>
> ObjApp.UserControl = True
>
> Catch ex As Exception
>
> MsgBox("Programerror:" & ex.Message)
>
> End Try
>
> End If
>
> 'Close
>
> 'YOU NEED TO DO THIS
>
> 'If you dont do this, i can swear you, if you shutdown youre computer
>
> 'you will have a lot of errors about Excel files that are open :)
>
> range = Nothing
>
> objSheet = Nothing
>
> objSheets = Nothing
>
> objBooks = Nothing
>
> End Sub
>
>
> --
> Amateur de VB





Avatar
Eric Vernié [MS]
Bonjour Donald,
Cela veut dire donc que pour la ligne
objBooks = ObjApp.Workbooks
objBooks est à nothing, comme nous le souligne patrice dans son post.
il te faut donc le tester avec une ligne du type
if not objBooks is nothing then (VB2003)
if objBooks isnot nothing then ... (VB 2005)

Il est vraisemblable que dans ton cas, WorkBooks.Count =0

A+

Eric Vernié

"Donald Pelletier" wrote in message
news:
Bonjour,

C'est sur la ligne suivante:

objBook = objBooks.Add


par la suite, le programme entre dans la phase "Try" et génère le message
d'erreur"

Merci
--
Amateur de VB


"Eric Vernié [MS]" a écrit :

Bonjour Donald,

Sur quelle ligne exactement tu as le problème ?

Cordialement

Eric Vernié
Microsoft France


"Donald Pelletier" wrote in message
news:
> Bonjour,
>
> J'essaie d'écrire dans une feuille Excel à partir de VB.net. J'utilise
> Visual Studio Architect Entreprise et Excel 2003 et j'obtiens le
> message
> suivant:
>
>
>
> Object reference not set to to an instance of an object.
>
> Voici le code VB:
>
> Quelqu'un peut-il me donner une indice de la cause du problème.
>
>
> Merci de votre aide
>
> Imports Microsoft.Office.Core
>
> Public Class Form1
>
> Inherits System.Windows.Forms.Form
>
> .
>
> .
>
> .
>
> ' Open Application
>
> Dim objApp As Excel.Application
>
> Dim objBook As Excel._Workbook
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> Dim objBooks As Excel.Workbooks
>
> Dim objSheets As Excel.Sheets
>
> Dim objSheet As Excel._Worksheet
>
> Dim range As Excel.Range
>
> If chkNieuw.Checked = True Then
>
> 'New File
>
> Try
>
> ObjApp = New Excel.Application
>
> objBooks = ObjApp.Workbooks
>
> objBook = objBooks.Add
>
> objSheets = objBook.Worksheets
>
> objSheet = objSheets(1)
>
> 'Start Cell
>
> range = objSheet.Range("A1", Reflection.Missing.Value)
>
> range = range.Resize(20, 20)
>
> 'Fill in
>
> ObjApp.Application.Cells(1, 1).Value = "Name"
>
> ObjApp.Application.Cells(2, 1).Value = "First Name"
>
> ObjApp.Application.Cells(3, 1).Value = "City"
>
> ObjApp.Application.Cells(1, 2).Value = txtName.Text
>
> ObjApp.Application.Cells(2, 2).Value = txtFirst.Text
>
> ObjApp.Application.Cells(3, 2).Value = txtCity.Text
>
> 'Visible
>
> ObjApp.Visible = True
>
> ObjApp.UserControl = True
>
> Catch ex As Exception
>
> MsgBox("Programerror:" & ex.Message)
>
> End Try
>
> Else
>
> Try
>
> 'A excisted file -> Fill in
>
> Dim ObjApp As New Excel.Application
>
> Dim ObjWorkSheet As Excel.Worksheet
>
> 'Open File
>
> 'YOU NEED TO CREATE THIS FILE BEFORE YOU CAN RUN THIS
>
> ObjApp.Workbooks.Add()
>
> ObjApp.Workbooks.Open("C:Test.xls")
>
> Dim ObjW = ObjApp.ActiveWorkbook
>
> ObjWorkSheet = ObjApp.Worksheets(1)
>
> ObjWorkSheet.Application.Cells(1, 1).Value = "Name"
>
> ObjWorkSheet.Application.Cells(2, 1).Value = "First Name"
>
> ObjWorkSheet.Application.Cells(3, 1).Value = "City"
>
> ObjWorkSheet.Application.Cells(1, 2).Value = txtName.Text
>
> ObjWorkSheet.Application.Cells(2, 2).Value = txtFirst.Text
>
> ObjWorkSheet.Application.Cells(3, 2).Value = txtCity.Text
>
> 'Visible
>
> ObjApp.Visible = True
>
> ObjApp.UserControl = True
>
> Catch ex As Exception
>
> MsgBox("Programerror:" & ex.Message)
>
> End Try
>
> End If
>
> 'Close
>
> 'YOU NEED TO DO THIS
>
> 'If you dont do this, i can swear you, if you shutdown youre computer
>
> 'you will have a lot of errors about Excel files that are open :)
>
> range = Nothing
>
> objSheet = Nothing
>
> objSheets = Nothing
>
> objBooks = Nothing
>
> End Sub
>
>
> --
> Amateur de VB