Please help me its urgent, how to select a cell in excel from access.

Hallo friends,

This is Kiran I have been trying to open an excel file and search in it for a specific number and select that cell from access.

Previously I got a problem that it is always opening a new copy of the file but I have solved this problem by using GetObject( ) function which is opening every time only the same file but if I open a file with this function.

The selection of a cell is working If there is only this file is opened , if another Excel file is also opened then the line which has selecting a cell in the excel file is giving an error.

But our users will definitely open other excel files when they are working with my project so I must find a way which can work even other excel files are opened.

As I spent so many days on this, now am in a peek stage please help me. Any help will be great for me.



Here is my code.

Set myXl = GetObject("C:\Dokumente und Einstellungen\Kiran Karnati\Desktop\EXCEL\Book2.XLS")





XlBookCnt = myXl.Parent.Windows.Count



If XlBookCnt > 1 Then

For i = 1 To XlBookCnt

If myXl.Parent.Workbooks(i).FullName = "C:\Dokumente und Einstellungen\Kiran Karnati\Desktop\EXCEL\Book2.XLS" Then

myXl.Application.Visible = True

myXl.Parent.Windows(i).Visible = True

Label48.Caption = myXl.Worksheets("Sheet1").Cells(1, 2).Value

myXl.Worksheets("Sheet1").Cells(1, 10).Select

End If

Next i

Else



myXl.Application.Visible = True

myXl.Parent.Windows(1).Visible = True

Label48.Caption = myXl.Worksheets("Sheet1").Cells(1, 2).Value ‘ ß this line is has error

myXl.Worksheets("Sheet1").Cells(1, 10).Select

End If

I tink that may be as in VB

-----------------------------------------------------------------------
OPEN EXCEL
-----------------------------------------------------------------------
Function IsRunning(EXE_FILE As String)
Dim wmi As Object, sQuery As String, Processes As Collection
Set wmi = GetObject("winmgmts:")
sQuery = "select * from win32_process where name='" & EXE_FILE & "'"
IsRunning = (wmi.execquery(sQuery).Count > 0)
End Function

Function StartApp(AppName As String) As Object
Dim olApp As Object
If IsRunning(AppName & ".exe") Then
Set olApp = GetObject(, AppName & ".Application")
Else
Set olApp = CreateObject(AppName & ".Application")
End If
Set StartApp = olApp
Set olApp = Nothing
End Function
-----------------------------------------------------------------------
LOAD FILE <don't ask why OPUSAPP, no idea>
-----------------------------------------------------------------------
Sub ExcelFileOpen(ByVal FileName As String)
Dim hwnd
Dim StartDoc
hwnd = apiFindWindow("OPUSAPP", "0")
StartDoc = ShellExecute(hwnd, "open", FileName, "", "C:\", SW_SHOWNORMAL)
End Sub

-----------------------------------------------------------------------
WRITE TO EXCEL
-----------------------------------------------------------------------

Sub ExcelWrite(Wks As excel.Worksheet, Texto as string, Row as long, Col as long, Optional Size = 10, Optional BOLD = False, Optional ForeColor = -1, Optional BackColor = -1, Optional BORDER = False, Optional H_Alinement = -1, Optional V_Alinement = -1)
If BOLD Then Wks.Cells(Row, Col).Font.BOLD = True
Wks.Cells(Row, Col).Font.Size = Size
If ForeColor <> -1 Then Wks.Cells(Row, Col).Font.Color = ForeColor
If BackColor <> -1 Then Wks.Range(Wks.Cells(Row, Col), Wks.Cells(Row, Col)).Interior.ColorIndex = BackColor
Wks.Cells(Row, Col) = Texto

If H_Alinement <> -1 Then Wks.Range(Wks.Cells(Row, Col), Wks.Cells(Row, Col)).HorizontalAlignment = H_Alinement 'xlHAlignCenter
If V_Alinement <> -1 Then Wks.Range(Wks.Cells(Row, Col), Wks.Cells(Row, Col)).VerticalAlignment = V_Alinement
Wks.Range(Wks.Cells(Row, Col), Wks.Cells(Row, Col)).NumberFormat = "###,###,###.#####"
If BORDER Then Wks.Range(Wks.Cells(Row, Col), Wks.Cells(Row, Col)).Borders.Color = 0
End Sub

Well, as you can see you can use select on a worksheet but just reference the cells with the range or cells statments, I hope this help..., if not explain more.

Hei Hei Really surprised of your reply, because I have joined this forum in this month only and I have posted some posts but never got such a perfect and good answer, but I have been believing that I will get such help but now am feeling that my belief is correct.

Any way Thanks a million for your reply and I am now going to try your solution but before I try I want reply you.

I think you know problem ya?, I don't need to change the values of the sheet , I just have to search in the sheet and stop at a position where I found my number and I have to place the cursor in that cell.

I think you gave me greate knowledge with your reply and I am going try with that , but if you think the selection of a cell is not possible with this method please tell me.

Thanks a million from an Indian Friend who is now in Germany, If possible can you please tell me from where are you?...

Kiran Karnati.

hallo Darius ,

I have tried your solution but it is getting an error at the apiFindwindow() function that "The method or function is not defined ".

The same error at ShellExecute() also .

Again I have strucked please help me. May be am using your functions in a wrong way.

here is how I have used your functions. ( " I really I appriciate your help.")

Private Sub command47_Click()
Dim MYXL As Object
Dim Wks As Excel.Worksheet
Set MYXL = StartApp("Excel")
ExcelFileOpen ("Book1.XLS")
Wks = MYXL.Worksheets("sheet1")
Call ExcelWrite(Wks, "10", 9, 9)
End Sub

Excuse me and help me in using your help.

Thanks in advance.

Hi,

Sorry to barge in, but why do people always insist on using exotic API calls while there is a much simpler solution to the problem at hand. Explore the Microsoft Excel object model a bit. It is worth it. Anyway, your problem has a common cause: GetObject returns the first instance it can find of a given class name. So, if Excel is running, then you get a handle on that copy of excel. And since you don't know if there are any open workbooks, you can't figure out which is the one you need (not with one call).

Now, if you look at the Excel.Application object, you see you have a Workbooks collection that has a method called Open. That is how you can open an existing workbook and get a handle on that workbook to access it's worksheets. See where I am going?

The following code spawns a new excel object, opens up a file (you have to change it to your path) and selects the first sheet that is in the workbook. If you have a name for your worksheet you can use that instead. Next it selects a cell. You have to set a reference to the Microsoft Excel.X library (I figure you already have that). I use this code-setup in all my programs and it almost never fails (when it does it's usually memory problems because users are sloppy and leave everything open).



Do remember that when you are done with the objects set them to nothing and quit Excel. Otherwise you keep Excel processes open that slow down the computer.

One piece of advice in these kinds of problems, whenever you find yourself doing stuff that is really hard and involves the use of API's and exotic code: stop and think for a bit. Simple inspection often gives a simple solution.

Grtz.©

M.

hello hello Mythomep,
I really felt great for your solution , I too did something like how you have suggested but If I run this again it is opening another copy of the same file how to stop it.
Sometimes it is behaving very strange, it works good that it does't open the copies of the files If the excel file is not yet closed once, but If I close the file and open it again from that point it goes on opening the copy of the same file again and again.

and more over my problem is not to open a file which is already opened because my excel files are very large with a lot of data and it is taking a lot time to open and search.

I just want to tell a bit more clearly about my problem.

that is I have to open an excel file from access, search for a velue in the first sheet of that file and I must select the cell where I find my value.

I must not open the file if it is already opened.

everything I said up to now is working with the code that I going to show is now but it is working only if this file is opened , if any other file is opend after opening this file then it not selecting that cell. creating an error in the line of selection.

[code]

Private Sub command47_Click()
Dim oXL As Object
Dim wb As Object
On Error Resume Next
' try to get Excel, if not possible, create a new instance
' of the Automation server
Set oXL = GetObject(, "Excel.Application")
If CBool(Err.Number) Then
Set oXL = CreateObject("Excel.Application")
Err.Clear
End If
oXL.Visible = True ' if not true, selection will not work
'
' try to set a wb variable, if not possible, open the workbook
Set wb = oXL.Workbooks("Book2.xls")
If CBool(Err.Number) Then
Err.Clear
Set wb = oXL.Workbooks.Open("C:\Book2.xls")
If CBool(Err.Number) Then
MsgBox "Workbook not found": Exit Sub
Err.Clear
End If
End If
On Error GoTo 0

'
'this will write the contents to the Caption property of Label48 control
Label48.Caption = wb.Sheets("Sheet1").Cells(6, 2).Value
'
'this will select the cell if you really must
wb.Sheets("Sheet1").Cells(6, 2).Select

End Sub

[\code]


Any help is great for me.

Kiran Karnati.

Hi,

Make the workbook and worksheet variables private to your form (or global to your project, if you must). Then, when the form loads, open up your Excel file and keep it open. In your command button code you just do your thing (search for the value) and nothing more. This creates a few problems you have to adress. First one is the visibility of Excel. If you make excel visible, then a user can close Excel and mess up your application. If I have to grab data from an Excel file, I never ever display it to the user but let it run invisible and do my stuff. I saw in your code that you select a cell. Why is that? Selecting and using the .ActiveSelection methods are very very slow in Excel Automation. If you don't need to select anything, then don't and keep MS-Excel hidden from the user.

The second one is the loading time of your form. If your Excel file is really large and loading it creates a noticable time-lag in your application you might want to look for another solution and get rid of Excel all together. This depends on the nature of the data, is it static or dynamic? What do you use the data for? Looking values up or outputting something?
Can you explain the problem you are trying to solve?

Grtz.©

M.

Haiiii , ya sure I will explain...

I am Kiran Karnati an Indian student almost at the end of my MS Computers in Germany and working here in germany for a company which works for "VolksWagen". I am developing an Internal software programme for my Company, which I am doing in Access .

My company's main work is to Analyse and produce the repair timings for Volks Wagen.

My project will show the users that on which part to analyse first and which aprt of the car later to produce the repair times of that car.

now when they select a number in the list of the part numbers on an Access form and click a button then the programme must search for that value in the excel file and place the cursor on that cell where it finds that part number , so that the programme can help the users not to search in that large excel file for that number.

to see this information is very important for the user to analyse and produce the repair times for the car that they are working on.

So I must select a the cell and show it to the user , I mean I have to make Excel sheet visible that is the job of the programme to open the file ( if it is not opened yet)
and search for a value and place the cursor on that cell and show it to the user so that he don't need to search in those thousands of lines.

opening and searching is ok but it is not selecting the cell I did't understand why??

can you please help me?...

Thank a million in advance.
Kiran Karnati.

Hi,

Using select does not automatically show the selected cell if you do it programatically. You need to use the GoTo method on the Application object.



But if I understand your problem correctly, all you need to do is search a part number in an Excel sheet and display the row that was found. If that is the case, why don't you import the Excel sheet in an Access Table and display the record? Easier, faster and you can maintain it much better. For example, what if someone decides to alter the Excel sheet and insert a column somewhere?


Grtz.©

M.

Your are the really fit programmer Mythomep , it is working perfectly.

but I am using with specific names so that it always points the same sheet , independent of the order when it is opened.

so this is the statement that I am using.( but presented by Mythomep )

oXL.Goto oXL.Workbooks("Book2.XLS").Worksheets("Sheet1").Range("B6"), True

this is working with out any errors for me.

have fun and keep smiling.
Kiran.

Sorry kiran_karnati, missed funtion definitions


Declare Function apiFindWindow Lib "user32" Alias "FindWindowA" (ByVal lpclassname As Any, ByVal lpCaption As Any) As Long
Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation _
As String, ByVal lpFile As String, ByVal lpParameters _
As String, ByVal lpDirectory As String, ByVal nShowCmd _
As Long) As Long

Global Const SW_SHOWNORMAL = 1

I really have to thank you all because today is a very good for me that I have solved my problem. any way Now I am very happy and I wish you all a nice time .

here is my final code.


Private Sub LblDCHA_Click()
Dim oXL As Object
Dim wb As Object
Dim oWorksheet As Excel.Worksheet
Dim i, xlBookCnt As Integer
Dim filename, fullpath, strRange As String
Dim XLVal, AccessVal, XLcutVal As String
Dim mycomp, row As Integer
On Error Resume Next
DoCmd.Hourglass True

' try to get Excel, if not possible, create a new instance
' of the Automation server
Set oXL = GetObject(, "Excel.Application")
If CBool(Err.Number) Then
Set oXL = CreateObject("Excel.Application")
Err.Clear
End If

filename = "WMS Passat B6 Limo.xls"
fullpath = "C:\Anna_Documents\Jetta\Excel_Docs\WMS Passat B6 Limo.xls"
' try to set a wb variable, if not possible, open the workbook
Set wb = oXL.Workbooks(filename)
If CBool(Err.Number) Then
Err.Clear
Set wb = oXL.Workbooks.Open(fullpath)
If CBool(Err.Number) Then
MsgBox "Workbook not found": Exit Sub
Err.Clear
End If
End If
On Error GoTo 0

Set oWorksheet = wb.Worksheets("HA")

'Searching for a value
AccessVal = Forms!TOP_200_JOBS_Form.sample_sub!Expr1.Value
For i = 1 To 500
strRange = "A" & i
XLVal = oWorksheet.Range(strRange).Value
XLcutVal = Mid(XLVal, 1, 4)
' mycomp = StrComp(XLcutVal, AccessVal, 1)
' If mycomp = 1 Then
If XLcutVal = AccessVal Then
row = i
Exit For
End If
Next i



If XLcutVal <> AccessVal Then
'
MsgBox "Sorry KD not found in Excel sheet 'HA'"
oXL.Visible = True ' if not true, selection will not work
Else
oXL.Visible = True ' if not true, selection will not work
' i = i - 1
strRange = "A" & row
'this will write the contents to the Caption property of Label48 control
' Label48.Caption = oWorksheet.Range(strRange).Value

Label48.Caption = i

'this will select the cell if you really must
oXL.Goto oXL.Workbooks(filename).Worksheets("HA").Range(strRange), True
End If
Set oXL = Nothing
Set wb = Nothing
Set oWorksheet = Nothing


DoCmd.Hourglass False
End Sub


C U again.
bye biiiii
Kiran Karnati.