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

حل شده 

Visual Basic 6

توسط aydass در 1 سال پیش
2 277 3 1 سال پیش
aydassreza-irdev
1

سلام....دوستان من یه برنامه نوشتم که از طریق ویژوال به اکسل وصل شه یعنی برنامه ک اجراشدی یوزروپس بگیره و ب شیت مخصوص اون ای دی وصل شه در ضمن بقیه شیت ها هایدبشه....برنامه نوشته شده ولی فقط ب شیت اول وصل میشه...اگه میشه راهنماییم کنین...درضمن برنامه هایدشدن بقیه شیت هارم ننوشتم😞

آخرین ویرایش: 03-12-2017 ساعت 09:59، توسط Reza

پاسخ ها

1

سلام. خوش اومدین. یه نمونه کد خدمتتون میذارم برای اتصال به یه 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

ازتون ممنونم...رفتم انجمن اکسل ایران هیچکسی جواب نداد...ازتون ممنونم...واقعاااا دستتون دردنکنه

0

خواهش میکنم. سوالی داشتید در خدمت هستیم.

خوش آمدید

برای طرح سوال، ایجاد بحث و فعالیت در سایت نیاز است ابتدا وارد حساب کاربری خود شوید. در صورتی که هنوز عضو سایت نیستید میتوانید در عرض تنها چند ثانیه ثبت نام کنید.

لطفا ابتدا لاگین کنید