Jump to content

Automark/delete with excel


WORKflow
 Share

Recommended Posts

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. excel.thumb.jpg.1048ad8e3716a18df6e9cdb5a269c042.jpg

 

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

Link to comment
Share on other sites

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.

excel.jpg

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

@WORKflow
Habe Dir im anderen Thread auf englisch geantwortet.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • Jos locked, unlocked and locked this topic
@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.
Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

If I understand correctly, this is a good start  :)

; 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 by ioa747
corrections

I know that I know nothing

Link to comment
Share on other sites

@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

 

excel.thumb.jpg.28133a7563fd50ec4c0fdc32386bc21a.jpg

Link to comment
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

:welcome:

I know that I know nothing

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...