Iste hepimizin karsilastigi bir sorun.Vb icerisinden Excel dosyalarina nasil erisecegim, nasil

iceriklerini degistirecegim.



Yazacagimiz kodu her projemizde kolay bir sekilde kullanabilmek icin ClassModule yazacagiz.



Iste ClassModule kodu:

'**********************************************************************

' GENERAL

'Proje Tipi : clsMsExcel

'Bagli oldugu Moduller :

'**********************************************************************

'Aciklama : Microsoft Excel dokumani acma,icerik alma,icerik atama,saklama,

'Yazan : Levent YILDIZ

'Sirket :

'Baslangic Tarihi : 02.11.2002

'**********************************************************************

' PUBLIC SUBS

'Property Pupose (Public)

'**********************************************************************



'**********************************************************************

' PRIVATE SUBS

'Local Purpose (Private)

'**********************************************************************

'**********************************************************************

' PUBLIC FUNCTIONS

'Property Pupose (Public)

'**********************************************************************



'**********************************************************************

' PRIVATE FUNCTIONS

'Local Purpose (Private)

'**********************************************************************

'**********************************************************************

' DECLERATIONS

'

'**********************************************************************

Private objMsExcel As Object

Private objMsWorkBook As Object

'**********************************************************************

' EVENTS

'

'**********************************************************************

Event Error()

'**********************************************************************

' PROPERTIES

'

'**********************************************************************

'**********************************************************************





Function CreateExcelObject() As Boolean

'********************************************************************

'Yazan : Levent YILDIZ

'Sirket :

'Tarih : 02.11.2002

'Amac : Excel objesinin yaratilmasi

'Giris :

'Cikis :

'Not :

'********************************************************************

'Degisiklikler

'********************************************************************

Set objMsExcel = CreateObject("Excel.Application")

End Function

Function DestroyExcelObject() As Boolean

'********************************************************************

'Yazan : Levent YILDIZ

'Sirket :

'Tarih : 02.11.2002

'Amac : Excel objesinin yokedilmesi

'Giris :

'Cikis :

'Not :

'********************************************************************

'Degisiklikler

'********************************************************************

Set objMsExcel = Nothing

End Function

Function OpenWorkBook(strFileName As String) As Integer

'********************************************************************

'Yazan : Levent YILDIZ

'Sirket :

'Tarih : 02.11.2002

'Amac : Excel dokumaninin acilmasi

'Giris : strFileName : Acilacak dosya ismi

'Cikis :

'Not :

'********************************************************************

'Degisiklikler

'********************************************************************

On Error GoTo handler

If Dir(strFileName) = "" Then

OpenWorkBook = 2

Exit Function

End If

Set objMsWorkBook = objMsExcel.workbooks.Open(strFileName)

Exit Function

handler:



End Function

Sub NewWorkBook()

'********************************************************************

'Yazan : Levent YILDIZ

'Sirket :

'Tarih : 02.11.2002

'Amac : Yeni bir Excel dokumaninin acilmasi

'Giris :

'Cikis :

'Not :

'********************************************************************

'Degisiklikler

'********************************************************************

If objMsExcel Is Nothing Then Exit Sub

Set objMsWorkBook = objMsExcel.workbooks.Add

End Sub

Sub CloseWorkBook()

'********************************************************************

'Yazan : Levent YILDIZ

'Sirket :

'Tarih : 02.11.2002

'Amac : Acik olan Word dokumaninin kapatilmasi

'Giris :

'Cikis :

'Not :

'********************************************************************

'Degisiklikler

'********************************************************************

If objMsExcel Is Nothing Then Exit Sub

objMsExcel.workbooks.Close

objMsExcel.Quit

Set objMsExcel = Nothing

End Sub

Sub SaveWorkBook()

'********************************************************************

'Yazan : Levent YILDIZ

'Sirket :

'Tarih : 02.11.2002

'Amac : Acik olan Excel dokumaninin saklanmasi

'Giris : strFileName : saklanacak dosya ismi.Eger bos girilirse OpenDocument fonksiyonunda

' acilan dosya ismi alinir

'Cikis :

'Not :

'********************************************************************

'Degisiklikler

'********************************************************************

If objMsWorkBook Is Nothing Then Exit Sub

objMsWorkBook.Save

End Sub

Sub RemoveColumn(ColIndex As Integer)

'********************************************************************

'Yazan : Levent YILDIZ

'Sirket :

'Tarih : 31.01.2003

'Amac : Acik olan Excel dokumaninda istenen kolonun remove edilmesi

'Giris : Colindex : Silinecek kolon indeksi

'Cikis :

'Not :

'********************************************************************

'Degisiklikler

'********************************************************************

If objMsWorkBook Is Nothing Then Exit Sub

objMsWorkBook.sheets(1).Range("A1").Select

objMsWorkBook.sheets(1).Columns(ColIndex).EntireColumn.Delete

End Sub

Sub SaveWorkBookAs(strFileName As String)

'********************************************************************

'Yazan : Levent YILDIZ

'Sirket :

'Tarih : 02.11.2002

'Amac : Acik olan Excel dokumaninin saklanmasi

'Giris : strFileName : saklanacak dosya ismi.Eger bos girilirse OpenDocument fonksiyonunda

' acilan dosya ismi alinir

'Cikis :

'Not :

'********************************************************************

'Degisiklikler

'********************************************************************

If objMsWorkBook Is Nothing Then Exit Sub

objMsWorkBook.SaveAs strFileName

End Sub

Property Let CellValue(RowIndex As Long, ColIndex As Long, New_CellValue As String)

If objMsExcel Is Nothing Then Exit Property

objMsExcel.cells(RowIndex, ColIndex).Value = New_CellValue

End Property

Property Get CellValue(RowIndex As Long, ColIndex As Long) As String

If objMsExcel Is Nothing Then Exit Property

CellValue = objMsExcel.cells(RowIndex, ColIndex).Value

End Property

Property Let Visible(New_Visible As Boolean)

'********************************************************************

'Yazan : Levent YILDIZ

'Sirket :

'Tarih : 02.11.2002

'Amac : Word objesinin saklanmasi | gosterilmesi

'Giris :

'Cikis :

'Not :

'********************************************************************

'Degisiklikler

'********************************************************************

If objMsExcel Is Nothing Then Exit Property

If New_Visible Then

objMsExcel.Show

Else

objMsExcel.Hide

End If

End Property

Private Sub Class_Terminate()

Set objMsExcel = Nothing

Set objMsWorkBook = Nothing

End Sub

Kullanimi ise;

Kod:

ClassXls.CreateExcelObject                      'Yeni bir excel objesi yaratir

ClassXls.NewWorkBook                          'Yeni bir workbook acar.

ClassXls.Visible = False                          'Excek yazilimini gorunmez yapar.

ClassXls.CellValue(1, 1) = "Test"              '1.kolonun 1.sirasina "Test" degerini yazar.

ClassXls.SaveWorkBookAs "c:\Test.xls"    'Dosyayi c:\Test.xls olarak kaydeder.

ClassXls.CloseWorkBook                        'Workbook'u kapatir.

ClassXls.DestroyExcelObject                    'Excel objesini'ni yokeder.