Copy dan Paste Range
Copy Paste dalam satu sheet yang sama
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim xlSourceRange, xlDestRange As Excel.Range
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
xlWorkBook = xlApp.Workbooks.Open("C:\vb dan excel\percobaan.xlsx")
xlApp.Visible = True
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
'~~> sumber yg mau di copy
xlSourceRange = xlWorkSheet.Range("A1:B10")
'~~> tujuan atau mau dipaste
xlDestRange = xlWorkSheet.Range("D1")
'~~> Copy paste range
xlSourceRange.Copy(xlDestRange)
End Sub
End Class
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet, xlWsheet2 As Excel.Worksheet
Dim xlSourceRange, xlDestRange As Excel.Range
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
xlWorkBook = xlApp.Workbooks.Open("C:\vb dan excel\percobaan.xlsx")
xlApp.Visible = True
'~~> worksheet sumber
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
'~~> worksheet tujuan
xlWsheet2 = xlWorkBook.Sheets("Sheet2")
'~~> Set sumber range
xlSourceRange = xlWorkSheet.Range("A1:B10")
'~~> Set range tujuan
xlDestRange = xlWsheet2.Range("A1")
'~~> Copy paste range
xlSourceRange.Copy(xlDestRange)
End Sub
End Class
Copy Paste beda workbook
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Dim xlApp As New Excel.Application
Dim xlWorkBook, xlWorkBook2 As Excel.Workbook
Dim xlWorkSheet, xlWsheet2 As Excel.Worksheet
Dim xlSourceRange, xlDestRange As Excel.Range
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
'~~> Buka file sumber
xlWorkBook = xlApp.Workbooks.Open("C:\vb dan excel\percobaan.xlsx")
'~~> Buka file tujuan
xlWorkBook = xlApp.Workbooks.Open("C:\vb dan excel\percobaan1.xlsx")
xlApp.Visible = True
'~~> Set sumber worksheet
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
'~~> Set tujuan worksheet
xlWsheet2 = xlWorkBook2.Sheets("Sheet1")
'~~> Set sumber range
xlSourceRange = xlWorkSheet.Range("A1:B10")
'~~> Set range tujuan
xlDestRange = xlWsheet2.Range("A1")
'~~> Copy paste range
xlSourceRange.Copy(xlDestRange)
End Sub
End Class
Merging/Unmerging Cells
Imports Excel =
Microsoft.Office.Interop.Excel
Public Class Form1
'~~> Define your Excel Objects
Dim xlApp As New
Excel.Application
Dim xlWorkBook As
Excel.Workbook
Dim xlWorkSheet As
Excel.Worksheet
Dim xlSourceRange,
xlDestRange As Excel.Range
Private Sub
Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles Button4.Click
xlWorkBook =
xlApp.Workbooks.Open("C:\vb dan excel\percobaan.xlsx")
xlApp.Visible =
True
xlWorkSheet =
xlWorkBook.Sheets("Sheet1")
'~~> Set range
yg mau di merge
xlSourceRange =
xlWorkSheet.Range("A1", "E1") \
'~~> Merge range
xlSourceRange.Merge()
'~~> Jika mau di set
center alignment secara horizontal dan vertical g
xlSourceRange.HorizontalAlignment
= Excel.Constants.xlCenter
xlSourceRange.VerticalAlignment
= Excel.Constants.xlCenter
End Sub
End Class
Mencetak / Print
Imports Excel =
Microsoft.Office.Interop.Excel
Public Class Form1
Dim xlApp
As New Excel.Application
Dim
xlWorkBook As Excel.Workbook
Dim
xlWorkSheet As Excel.Worksheet
Private
Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles Button4.Click
xlWorkBook = xlApp.Workbooks.Open("C:\vb dan excel\percobaan.xlsx")
xlApp.Visible = True
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
'~~> Printing
xlWorkSheet.PrintOut(From:=1, To:=1, Copies:=1, Collate:=True)
End Sub
End Class
Save atau save as dan
Keluar dari file excel
lagi.. syntaxnya aja ya
heheheh
Save dan close
'~~> Save
xlWorkBook.Save()
'~~> Close
xlWorkBook.Close()
Saveas dan close
'~~> Save As
xlWorkBook.SaveAs(Filename:="C:\vb
dan excel\percobaanNew.xlsx")
'~~> Close
xlWorkBook.Close()
Kirim file Excel
menggunakan Outlook
jangan lupa add
reference Microsoft Outlook nya
Imports Excel =
Microsoft.Office.Interop.Excel
Imports Outlook =
Microsoft.Office.Interop.Outlook
Public Class Form1
Dim
objOutlook As New Outlook.Application
Dim
objEmail As Outlook.MailItem
Private
Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles Button4.Click
Dim body As String
Dim ExcelFile As String = "C:\vb dan excel/percobaan.xlsx"
objOutlook = CType(CreateObject("Outlook.Application"),
Outlook.Application)
objEmail = objOutlook.CreateItem(Outlook.OlItemType.olMailItem)
body = "Hello," & vbCrLf & vbCrLf
body += "Terlampir file excelnya" & vbCrLf & vbCrLf
body += "Terinakasih," & vbCrLf & "<Siapa
aku>"
With objEmail
.Subject = "Kirim file excel via VB dan outlook"
.To = "tujuan@domain.com"
.Body = body
.Attachments.Add(ExcelFile)
'~~> jika ingin dimunculkan di monitor
.Display(True)
.Send()
End With
End Sub
End Class
Close Excel and Clean Up
jangan lupa untuk selalu
membersihkan / clean up dan melepas object excelnya denga syntax berikut
'~~> Clean Up
releaseObject(xlApp)
releaseObject(xlWorkBook)
'~~> Release objects
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
releaseObject(xlApp)
releaseObject(xlWorkBook)
'~~> Release objects
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
"No Offence No
Hearth Feelings"
Semoga Bermanfaat
No comments:
Post a Comment