Excel Search Help

[danmclane]

Hello All. Newby here.

Have a Excel sheet I open with a macro.
I need to incorporate a barcode scanner to search for data in the open sheet, ( in a specific column) then move to the "Received Qty" cell to input the amount of material received.

Have everything working well except I can't get the macro to stop and wait for the scanner input in the Excel FIND popup (CTRL F). The scanner is programmed to trim all leading zeros and end with a "return".

I would be OK creating a dialog to ask the user "please scan the barcode or enter the part number" insted of using the excel FIND But I am new and can't figure out the dialog and passing the info.

Here is my existing code.

Please be very specific if you have any suggestions. Newby, remember....

//Set IGNORESPACES to 1 to force script interpreter to ignore spaces.
//If using IGNORESPACES quote strings in {" ... "}
//Let>IGNORESPACES=1
XLOpen>C:\Documents and Settings\dmclane\Desktop\Dan sorted.xls,1,xlBook
WaitWindowOpen>Microsoft Excel - Dan sorted.xls
// Open FIND box and move to correct position
Press LCTRL
Wait>0.47
Send>f
Release LCTRL
WaitWindowOpen>Find and Replace
MoveWindow>Find and Replace,80,175
ResizeWindow>Find and Replace,449,189

// Wait for Barcode Scanner to get data

// Based on Scan the first cell with the data is found (there may be no data match also) in which case an error should pop up to try again.


// Move to current cell box (name), delete the column reference, add "W" to move to the W Column with the same row number, then jump to QTY Recd cell
MouseMove>12,70
Wait>.05
LDown
WaitWindowOpen>Microsoft Excel - Dan sorted.xls
MoveWindow>Microsoft Excel - Dan sorted.xls,-4,-4
ResizeWindow>Microsoft Excel - Dan sorted.xls,1288,780
Wait>0.02
LUp
Wait>0.33
Press Home
Wait>1.3
Press Del
Wait>0.72
Send>w
Wait>1.11
Press Enter

// Enter correct quantity manually into the appropriate cell

// Save the sheet

// Start over.

[Marcus Tettmar]

Instead of using the find dialog and having to simulate user input take a look at the FindCell function here:
http://www.mjtnet.com/blog/2010/12/15/m ... -vbscript/

[danmclane]

Thanks Marcus.

Ok, did that. But as I read this I need to know the column and cell position for the data. Unless there is a variable I can use, which I don't understand.

I know the data (from the bar code scanner input) and the column ( fixed column for Vendor part #). But the line is variable? and the part number can be in the sheet multiple times. I have sorted the sheet on the part number and also sub totaled the counts so that every part is seperated.

I played with GetCell for about 3 hours yeaterday with no luck. Likely it's due to my minimal skill level.

I would be happy not to use Excel Find, its sloppy. but my macro/VB skills are pretty weak.

Not to mention the fact that I need this working 3 days ago for a project.

I have searched the forum for examples but also I can find one that fits.

daniel

[Marcus Tettmar]

No that FindCell function takes only a sheet name and the data you want to find and then returns the cell coordinates.

Once you have the cell coordinates you can use them as offsets to move to the cell you want to output to and use SetCell to output the data.

[danmclane]

OK, I get it. But is there any example of this 3 step process in use so I can follow it in my code?

dan

[Marcus Tettmar]

Right. Paste the following into a new macro and save it into your General group. I'm assuming you have all the standard sample macros installed. It works against the supplied example.xls file which is in the same folder. So as long as you save it into the same group as all the existing samples you should be good to go.

It opens example.xls locates a cell containing the value 59.59 which happens to be in row 5, column 3 and then outputs a new string to the cell 2 columns over. You'll see that cell change in row 5.

Code: Select all

VBSTART
  Dim xlApp
  Dim xlBook
  Sub GetXL
    Set xlApp = GetObject(,"Excel.Application")
    Set xlBook = xlApp.ActiveWorkbook
  End Sub

  Function FindCell(Sheet,Data)
    Dim theCell
    Dim xlValues
    xlValues = -4163

    Dim xlSheet
    Set xlSheet = xlBook.Worksheets(Sheet)
    xlSheet.Range("A1").Select
    Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)
    FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)
  End Function
VBEND

//Open an XLS file natively
XLOpen>%SCRIPT_DIR%\example.xls,1,xlH

//Call GetXL to give VBScript a reference to the XL instance
VBRun>GetXL

//find the value 59.59 in the sheet
VBEval>FindCell("Sheet1","59.59"),res
Separate>res,:,coords

//so row number is in coords_1 and column in coords_2
//So let's output a value to a cell two columns along:
XLSetCell>xlH,Sheet1,coords_1,{%coords_2%+2},SOMETHING NEW,scres