گزینه ها
aydass

اتصال و کار با اExce با استفاده از ای دی های متفاوت

حل شده  Visual Basic 6

توسط aydass در 1 هفته پیش
29531 هفته پیش
سلام....دوستان من یه برنامه نوشتم که از طریق ویژوال به اکسل وصل شه یعنی برنامه ک اجراشدی یوزروپس بگیره و ب شیت مخصوص اون ای دی وصل شه در ضمن بقیه شیت ها هایدبشه....برنامه نوشته شده ولی فقط ب شیت اول وصل میشه...اگه میشه راهنماییم کنین...درضمن برنامه هایدشدن بقیه شیت هارم ننوشتم😞
آخرین ویرایش: 03-12-2017 ساعت 09:59، توسط Reza.irdev
سلام. خوش اومدین. یه نمونه کد خدمتتون میذارم برای اتصال به یه 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
0
aydass در
ازتون ممنونم...رفتم انجمن اکسل ایران هیچکسی جواب نداد...ازتون ممنونم...واقعاااا دستتون دردنکنه
0
Reza.irdev در
خواهش میکنم. سوالی داشتید در خدمت هستیم.