سلام. خوش اومدین. یه نمونه کد خدمتتون میذارم برای اتصال به یه Sheet خاص:
'do declare these variables you need to add a reference
'to the microsoft excel 'xx' object library.
'you need two text boxes and two command buttons
'on the form, an excel file in c:\book1.xls
Dim xl As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlwbook As Excel.Workbook
Private Sub Command1_Click()
'the benifit of placing numbers in (row, col) is that you
'can loop through different directions if required. I could
'have used column names like "A1" 'etc.
Text1.Text = xlsheet.Cells(2, 1) ' row 2 col 1
Text2.Text = xlsheet.Cells(2, 2) ' row 2 col 2
'don't forget to do this or you'll not be able to open
'book1.xls again, untill you restart you pc.
xl.ActiveWorkbook.Close False, "c:\book1.xls"
xl.Quit
End Sub
Private Sub Command2_Click()
xlsheet.Cells(2, 1) = Text1.Text
xlsheet.Cells(2, 2) = Text2.Text
xlwbook.Save
'don't forget to do this or you'll not be able to open
'book1.xls again, untill you restart you pc.
xl.ActiveWorkbook.Close False, "c:\book1.xls"
xl.Quit
End Sub
Private Sub Form_Load()
Set xlwbook = xl.Workbooks.Open("c:\book1.xls")
Set xlsheet = xlwbook.Sheets.Item(1)
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set xlwbook = Nothing
Set xl = Nothing
End Sub
برای فعال کردن یک Sheet:
Sheets("Name of sheet").Select
Sheets(index).Select
اینم یه مثال کامل برای کار با اکسل:
Private Sub Command1_Click()
Dim excelApp As Excel.Application
Dim excelWB As Excel.Workbook
Dim excelWS As Excel.Worksheet
Dim rowCount As Integer
Dim colCount As Integer
Dim r As Integer
Dim c As Integer
Dim foundRow As Integer
Dim foundCol As Integer
Dim foundRange As Range
Dim myRange As Range
Dim sheet As Worksheet
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
Set excelWB = excelApp.Workbooks.Open("C:\testing\testWB.xls")
Set excelWS = excelWB.Worksheets(1)
rowCount = excelWS.UsedRange.Rows.Count
colCount = excelWS.UsedRange.Columns.Count
' One way to search for things
For r = 1 To rowCount
For c = 1 To colCount
If excelWS.Cells(r, c).Value = "Using ranges with cell strings" Then
' Store the row number, and column number in a variable
foundRow = r
foundCol = c
excelWS.Cells(r, c).Value = "I found you!"
End If
Next c
Next r
' Another way
Set myRange = excelWS.Range("A2", "A10")
Set foundRange = myRange.Find("Using a loop to fill in cells", , xlFormulas, xlWhole, xlByColumns)
If Not (foundRange Is Nothing) Then
'We found it
End If
'Delete the unused worksheets
For Each sheet In excelWB.Sheets
If Not sheet.Name = "Sheet1" Then
sheet.Delete
End If
Next sheet
' Add a worksheet, then rename it
Set sheet = excelWB.Worksheets.Add
sheet.Name = "NewlyCreated"
sheet.Cells(1, 1).Value = "I'm new!"
End Sub
Private Sub Form_Load()
Dim excelApp As Excel.Application
Dim excelWB As Excel.Workbook
Dim excelWS As Excel.Worksheet
Dim rowCounter As Integer
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = False
Set excelWB = excelApp.Workbooks.Add
Set excelWS = excelWB.Worksheets(1)
'Examples of the Cells() member
excelWS.Cells(1, 1).Value = "Testing testing..."
For rowCounter = 2 To 10
excelWS.Cells(rowCounter, 1).Value = "Using a loop to fill in cells"
Next rowCounter
'Examples of the Range() member
excelWS.Range("A15", "F25").Value = "Using ranges with cell strings"
excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 4)).Value = "Using ranges with Cells() objects"
'Examples of font formatting
excelWS.Range("A15", "F25").Select
With Selection.Font
.Size = 8
.Italic = True
.Bold = True
.Underline = True
.Underline = True
.Name = "Arial Black"
End With
'Examples of number formating
excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 4)).Value = 500
excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 2)).Select
With Selection
.NumberFormat = "$#,##0.00"
End With
excelWS.Range(excelWS.Cells(1, 3), excelWS.Cells(10, 3)).Select
With Selection
.NumberFormat = "#,##0.000"
End With
excelWS.Range(excelWS.Cells(1, 4), excelWS.Cells(10, 4)).Select
With Selection
.Style = "Percent"
End With
'Save and close
excelWB.SaveAs "C:\testing\testWB", FileFormat:=xlExcel8
excelWB.Close
Excel.Application.Quit
Set excelApp = Nothing
End Sub
Private Sub Form_Terminate()
Dim f As Form
For Each f In Forms
If f.hWnd <> Me.hWnd Then
Unload f
Set f = Nothing
End If
Next f
Unload Me
End Sub