|
|
|||||||||
|
|||||||||
|
|||||||||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
AT&T devCentral & BlackBerry(r) Webcast Series: BlackBerry and GPS -Build Location Awareness into your BlackBerry Applications, July 10th-1:00PM EST. Register Today!
|
|
#1
|
|||
|
|||
|
Hallo everybody,
I have searched our forums to find wether any thread that can help me but unfortunately I did't find it. I found somethings but I don't need that much of complexity because I just have to open an excel file in a specific location and I have to search a for value in that sheet and I have to select that cell . I have to do this when the user clicks a button on an access form. I wrote a code and its opening the file and also the specific sheet but not searching beacause I don't know how to get the value of that cell and also I have abserved that it is working only alternative times not everytime. I did't understand why it is working so... please help me... this is the code that I wroteSet xlApp = New Excel.Application varPath = "C:\Dokumente und Einstellungen\Kiran Karnati\Desktop\EXCEL\Book2.xls" With xlApp .Visible = True ' and now I have to open a perticular Workbook whose path is in varPath variable and Perticular Sheet Set xlWB = .Workbooks.Open(varPath, , False) With xlWB.Sheets("Sheet1") For i = 1 To 100 s1 = "C" & i s2 = Range(s1).Value If Val(s2) = 100 Then Range(s1).Select End If Next i End With End With Thank you in advance. Kiran. |
|
#2
|
|||
|
|||
|
Quote:
Thank you for every one who viewed this and thought to help me. I found the solution for my problem and I want to post it here so that no one else will suffer for this problem. that is... Private Sub Command47_Click() Dim intKDVal As Integer Dim xl As Excel.Application Dim SA As Excel.Worksheet Dim rows As Long Dim maxval, temp, selrow, chkval As Integer 'As my file is too larg and takes 1 minute to open am changing the mouse pointer as a 'hour glass till it is opened DoCmd.Hourglass True Set xl = CreateObject("Excel.Application") maxval = 0 selrow = 1 'Taking the value to search in the excel sheet from the access form Text49.Value = Forms!TOP_200_JOBS_Form.sample_sub!Expr1.Value chkval = Forms!TOP_200_JOBS_Form!Text49.Value xl.Workbooks.Open "C:\Dokumente und Einstellungen\Kiran Karnati\Desktop\EXCEL\Touareg.HA+TA.2004-09-10.xls" Set SA = xl.ActiveWorkbook.Sheets("Sheet0") With SA For rows = 3 To 65536 If .Cells(rows, 2).Value = chkval Then temp = .Cells(rows, 17).Value If temp > maxval Then maxval = temp selrow = rows End If End If Next rows 'Now select the row which has the maximum value in the 17 column corresponding to 'the first column .Cells(selrow, 2).Select End With 'Now the excel sheet is shown to the user xl.Visible = True 'Mouse pointer turned to normal DoCmd.Hourglass False Set SA = Nothing 'If you want to close the excel sheet you can close 'As I want to see the resulting selection I have commented them 'xl.Workbooks("HideCols.xls").Close savechanges:=True 'xl.Quit Set xl = Nothing End Sub |
![]() |
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > search and select a cell in excel from access |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|