Automark/delete with excel

[WORKflow]

Hallo Leute..

Ich habe folgendes Problem.  Zu meiner Arbeit gehört das erfassen/scannen tausender imei nummern von Smartphones via Barcodescanner in das Programm Excel.

mit STRG+ F öffne ich das suchen und ersetzen Fenster in Excel und scanne anschließend die Imei nummern . Das problem  und die folgende Frage bezieht sich auf die kopierte nummer in dem suchen un ersetzen Fenster. Ich muss jedesmal die nummer im Fenster markieren oder löschen um das nächste Handy bzw. die nächste imei nummer zu scannen.

 Ich würde aber gerne diesen Arbeitschritt überspringen, daher die frage.. ist es möglich das sich die imei nummer Automatsich im Fentser markiert sodass ich ohne es manuell markieren zu müssen das nächste Händy einscannen kann.

 

Leider bin ich total unerfahren mit Makro befehlen. Ich hoffe jemand hat eine Lösung

[rudi]

Hallo Workflow,

 

willkommen im Forum -- this is an English forum, so please repost your question in English.

there is an Excel UDF, that will help you to automate Excel tasks, pls. have a look at https://www.autoitscript.com/wiki/Excel_UDF

regards, Rudi.

 

[WORKflow]

Hello folks.. I have the following problem. My work involves scanning thousands of imei numbers from smartphones using a barcode scanner in the Excel program. with CTRL + F I open the search and replace window in Excel and then scan the imei numbers. The problem and the following question is related to the copied number in the find and replace window. I have to mark or delete the number in the window every time to scan the next cell phone or imei number. But I would like to skip this step, here is the question.. is it possible that the imei number is automatically marked in the window so that I can scan the next mobile phone without having to mark it manually.

[Nine]

When you say mark a cell, do you mean highlight in yellow ?

Here what you could do :

1- create a small GUI with an input box where you would scan your imei numbers

2- Use Excel UDF to find the number

3- Highlight the cell

4- Clear the GUI input box, then repeat #1

ps. what happens when you do not find the number ?

[water]

I have never used it myself. Searchin the MS docu suggests the following solution:

• Create a VBA macro in your workbook
• Grab the Ctrl-F key by using OnKey and call a subroutine
• The subroutine creates the find/replace dialog and sets values for the controls in this dialog

OnKey: https://trumpexcel.com/vba-events/#Excel-VBA-OnKey-Event

Dialogs: https://learn.microsoft.com/en-my/office/vba/api/excel.dialogs

I'm not very familiar with VBA and do not have Excel available at the moment. So I'm not sure this "solution" will work at all.
This means that all the testing needs to be done by you (I will do my very best to assist you).

Do you want to go this route?

[water]

@WORKflow
Habe Dir im anderen Thread auf englisch geantwortet.

[WORKflow]

@Nine No, I mean marking the number in the copy paste window so that I can scan in new imei without interruption.. Up until now I have to double-click the imei to mark it so that it replaces the new imei with the old one becomes. If the imei is not marked, the scanner simply adds the new imei behind it. But I do not want that.

[WORKflow]

@water

Hello, since I am unfortunately a total beginner and my English is not the best, it is a little difficult for me to follow your instructions. but as far as I understand it seems to be the right way.

 

[water]

Wenn Du Dich im deutschen Forum unter www.autoit.de anmeldest, dann können wir die Konversation dort auch auf deutsch führen.

[Nine]

I still do not understand what it is that you want to achieve.  Like @water I am not very familiar with VBA, but I am very confident that you can do it all in AutoIt (and even more).  If you are interested in doing it in Autout, could you please explain in more details what are the steps you are currently taken to process those imei numbers ?

[ioa747]

If I understand correctly, this is a good start 

expandcollapse popup

; https://www.autoitscript.com/forum/topic/209766-automarkdelete-with-excel/?do=findComment&comment=1514021
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7
#include <AutoItConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>

Global $oExcel = ObjCreate("Excel.Application") ; Create an Excel Object
$oExcel.Visible = 1 ; Let Excel show itself
$oExcel.WorkBooks.open(@ScriptDir & "\test2.xls") ; open file

Global $hGUI = GUICreate('Find IMEI', 210, 45, -1, -1, -1, BitOR($WS_EX_TOOLWINDOW, $WS_EX_TOPMOST))
GUISetBkColor(0xA0A0A0)
Global $idImei = GUICtrlCreateInput("", 3, 1, 204, 19)
Global $idButtonGet = GUICtrlCreateButton("Get", 3, 23, 100, 20)
Global $idButtonExit = GUICtrlCreateButton("Exit", 107, 23, 100, 20)
GUISetState(@SW_SHOW)

;~ ConsoleWrite($oExcel.ActiveWorkBook.ActiveSheet.Cells(1, 1).Value & @CRLF)

;************************************************
While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE, $idButtonExit
            ExitLoop

        Case $idButtonGet
            GoToImei(FindThisImei(GUICtrlRead($idImei)))
            WinActivate("[CLASS:XLMAIN]")
    EndSwitch

    Sleep(10)
WEnd
;************************************************

Exit

;----------------------------------------------------------------------------------------
Func FindThisImei($sImei)
    Local $index = 1
    Local $XLimei ;= $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 1).Value
    While 1
        $XLimei = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 1).Value
;~      ConsoleWrite($index & ") -- " & $XLimei & @CRLF)
        If $XLimei = "" Then
            ConsoleWrite("-- Nothing" & @CRLF)
            Return 0
        ElseIf $XLimei = $sImei Then
            ConsoleWrite("> (" & $index & ") Imei = " & $XLimei & @CRLF)
            Return $index
        EndIf
        $index += 1
    WEnd
    Return 0
EndFunc   ;==>FindThisImei
;----------------------------------------------------------------------------------------
Func GoToImei($index)
    If $index = 0 Then
        $oExcel.ActiveWorkBook.ActiveSheet.Cells(1, 1).Activate
    Else
        $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 1).Activate
    EndIf
EndFunc   ;==>GoToImei

this is an approximation of what you are asking for (always in my opinion)

but there are other solutions, perhaps even faster, without having to focus in xl  but directly from the gui

if you have any questions, post them here

 

Edited February 25, 2023 by ioa747
corrections

[WORKflow]

@ioa747thx m8, where can i put in this code? never used codes or makros before.. I installed Autoit but i dont knopw where and how to put this codes

 

 

[WORKflow]

@water Ja das werde ich dann mal tun^^ weiß garnicht wie ich mich hier ins englische verirrt habe. 

[WORKflow]

@Nine

I have a lot of cell phones on the table in front of me. there is a barcode on every mobile phone... My job is to scan these mobile phones in Excel and to check whether the devices have a guarantee. Now it's like this, every time I scan an imei with my laser scanner, I have to mark the imei every time in order to be able to scan the next imei. You can see that in the picture in the small window you see in the middle. I want it to automatically mark or delete the imei after each scan sothat I can scan and check all devices without interruptions

 

[water]

Simply send Control-a to the control to select all text. For your project you would do this in  a loop and wait for the window (Ctrl-f) to appear before sending Ctrl-a.
Example:

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>

GUICreate("Suchen und Ersetzen", 320, 120, @DesktopWidth / 2 - 160, @DesktopHeight / 2 - 45)
Local $idInput = GUICtrlCreateInput("Example data", 10, 5, 300, 20)
Local $idMsg = GUICtrlCreateInput("", 10, 30        , 300, 20)
Local $idBtn = GUICtrlCreateButton("Ok", 40, 75, 60, 20)
GUISetState(@SW_SHOW)

Sleep(2000)
ControlSetText("Suchen und Ersetzen", "", $idMsg, "De-Select Text")
; DeSelect the Text
ControlClick("Suchen und Ersetzen", "", $idInput)

Sleep(2000)
ControlSetText("Suchen und Ersetzen", "", $idMsg, "Select Text")
; Select the Text
ControlSend("Suchen und Ersetzen", "", $idInput, "^a")

; Loop until the user exits.
While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            ExitLoop
        Case $idBtn
            ExitLoop
    EndSwitch
WEnd

 

Edited February 25, 2023 by water

[ioa747]

7 hours ago, WORKflow said:

where can i put in this code? never used codes or makros before.. I installed Autoit but i dont knopw where and how to put this codes

I suggest you also install the SciTE4AutoIt3

Installation instructions for Autoit3 and SciTE4AutoIt3.

then you open the .au3 file with SciTE and when you press F5, script is runing

https://youtu.be/BLU60CD-Poo

 

[WORKflow]

@water https://autoit.de/thread/87981-auto-markieren-excel-starter/  hallo Water, hier ist der Link vom deutschen Forum. Währe schön wenn wir uns da weiter unterhalten können. mfg