A script thats list user name and number

March 12, 2011 at 09:22:52
Specs: Windows xp
OK, guys i am very new is this script thing..


I want to create a batch script using notepad.

1. when i click on the script is will ask me to type the user name i want, and when i type the user name i want a number to come up which will be the user--number.. for example i type brown and his number is 123 i want to be able to see that.

2. i have a bunch of user name and numbers in a excel can a batch script pull data from the excel document to make this process easier?


See More: A script thats list user name and number

Report •


#1
March 12, 2011 at 09:45:40
Unfortunately a batch script can not directly access an Excel sheet since Excel is a GUI application and batch can only manage text files. Excel must be converted to .csv format then processed; to do the job a VBScript or VBA macro is needed.

Report •

#2
March 12, 2011 at 10:00:14
thanks, do you know how i can get started on the first question i asked? im very new at creating batch script

Report •

#3
March 12, 2011 at 12:29:45
About coding a batch script prompting for username when activated

@echo off
echo.
set /P UserID=Enter User Name^>
echo. User Name is %UserID%
pause
:: End_Of_Batch

To get the full list of batch commands at prompt type help | more and to display a quick on-line help type /? after the command code, e.g. set /?.

If you post the structure of your Excel sheet maybe your issue may be solved.


Report •

Related Solutions

#4
March 12, 2011 at 15:15:28
USERNAME USER ID

MARK ANT 122
JESS ASDW 525
MARK ANTION 689
PETER JACKSON 967
LILLY ADAR 845
JOHN ARDAR 874
MATT BROWN 237
DEBBIE SILVER 211
JAMES ANDERSON 424
TONY DUNCAN 430
JESSICA MAROLAS 762
SUZANNA HARM 959


SO THATS HOW MY EXCEL LOOKS..

NOW ALL I WANT TO DO IS OPEN UP A BATCH SCRIPT AND JUST TYPE IN THE PERSON LAST NAME AND HAVE THEIR USER ID NUMBER POP UP


Report •

#5
March 12, 2011 at 22:50:44
As IVO mentioned, the best way to do this within a batch file is to save your excel sheet as a CSV file.

Once you do that, place the csv in the same directory as your bat file with the following code:

@echo off
:top
cls
Set /p name=Please enter any portion of the user's name to find their number:
cls
Echo Searching for %name%
Echo.
findstr /I %name% filenamehere.csv
Echo.
Pause
goto top
---

This will list all entries containing the portion of the name entered.


Report •

#6
March 13, 2011 at 04:37:33
Here the script you requested ready to run. Just insert in the set ExcelFile= the full pathname/Filename of your Excel list sheet. Be aware this is an advanced batch/VBScript that requires a deep knowledge of scripting to be mastered.

@echo off & setlocal

set ExcelFile=HERE YOUR EXCEL FILE PATHNAME/FILENAME

set ExcelFile=%ExcelFile:"=%
call :XLS2CSV "%ExcelFile%"
echo.
pause
exit

:XLS2CSV [VBScript to export an Excel .xls/.xlsx sheet]
echo.
if not exist "%~f1" (echo.  File "%~f1" not found & goto :EOF)
set /P UserID=Enter User Name^>
echo.
echo.Browsing Users' List, Please wait...
echo.

  echo.Dim objXL                                         >  %~n0.vbs
  echo.Set objXL = CreateObject("Excel.Application")     >> %~n0.vbs
  echo.Const xlType = 42 'Tab delimited Unicode Text     >> %~n0.vbs
  echo.With objXL.Application                            >> %~n0.vbs
  echo.  .DisplayAlerts = False                          >> %~n0.vbs
  echo.  .Visible = False                                >> %~n0.vbs
  echo.  .Workbooks.Open "%~f1"                          >> %~n0.vbs
  echo.  .Workbooks("%~nx1").SaveAs "%~dpn1.csv", xlType >> %~n0.vbs
  echo.  .Workbooks("%~n1.csv").Close,SaveChanges = True >> %~n0.vbs
  echo.End With                                          >> %~n0.vbs
  echo.objXL.Quit                                        >> %~n0.vbs
  echo.Set objXL = Nothing                               >> %~n0.vbs

%~n0.vbs
del %~n0.vbs
if not exist "%~dpn1.csv" (echo.  Conversion failed & goto :EOF)
type "%~dpn1.csv" | find /I "%UserID%" || echo.  User Name "%UserID%" not round
del "%~dpn1.csv"

goto :EOF [End Of XLS2CSV]


Report •

#7
March 13, 2011 at 04:39:29
What are you doing with the number? It seems an inquiry in a database would give better results.

Report •

#8
March 13, 2011 at 10:50:22
HERE IS WHAT I ENTER.. I SAVE THE EXCEL AS A CSV FILE .. SAVE IT IN THE SAME LOCATION AS THE BAT BUT WHEN I TYPE THE PERSON LASTNAME IT SAYS FINDSTR CAN NOT OPEN>

@echo off
:top
cls
Set /p name=Please enter any portion of the user's name to find their number:
cls
Echo Searching for %name%
Echo.
findstr /I %name% USER AND ID.csv
Echo.
Pause
goto top


Report •

#9
March 13, 2011 at 10:57:56
Try the script I posted in #6. I tested it and the code worked fine.

Report •

#10
March 13, 2011 at 11:04:15
HEY IVO THE SCRIPT WORKS GREAT BUT THE OLD BAD THING IS AFTER I RUN THE SCRIPT AND I PUT THE USER NAME AND IT FINDS THE NUMBER... IT DELETES THE EXCEL/.CSV FILE?

Report •

#11
March 13, 2011 at 13:24:46
Yes, the script is designed to automatically access a true Excel file suffixed .xls or xlsx then to convert to a temporary .csv file to be processed by batch and eventually deleted.

You do not have to deal with converted Excel file, you can simply refer to your native Excel worksheet that is what you requested im your original post.

The script is a blend of NT batch and VBScript in one code.


Report •

#12
March 13, 2011 at 14:10:21
oh now i see what your talking about. it works great! thanks.


Now is is possible for me to get result to come up in different colors

for example if i do a search for brown can i get the result for brown user id number to come up in lets say..red?

also why does it close out after it finish a search? for example i do a search for brown after it finds the result for brown it tells me to press any key to continue and when i do i close out... basically i want to do more than one search in the same batch


Report •

#13
March 13, 2011 at 15:22:06
Well, about colors in NT console window is not possible to get single strings in different color since you can control the background and foreground for the whole text displayed only.

About multiple searches that is possible. Let me slightly modify the code tomorrow since now in Italy where I live is late night and I have to wake up at five in the morning on monday.


Report •

#14
March 14, 2011 at 07:54:48
@echo off & setlocal

set ExcelFile=HERE YOUR EXCEL .XLS/.XLSX SHEET

set ExcelFile=%ExcelFile:"=%
echo.
call :XLS2CSV "%ExcelFile%"
echo.
if ErrorLevel 1 (echo.  Press any key to quit... & pause > nul & exit 1)

:LOOP [Browsing UserNames' List]

  cls & echo.
  set UserID=& set /P UserID=Enter User Name^>
  echo.
  type "%ExcelFile%" | find /I "%UserID%" || echo.  User Name "%UserID%" not found
  echo.
  set /P UserID=Type X to quit, [ENTER] to continue^>
  if /I not "%UserID%"=="X" goto :LOOP

del "%ExcelFile%"
exit 0 [Return to OS]

:XLS2CSV [VBScript to export an Excel .xls/.xlsx sheet]
if not exist "%~f1" (echo.  File "%~f1" not found & exit /B 1)
echo.Accessing "%~f1", Please wait...

  echo.Dim objXL                                         >  %~n0.vbs
  echo.Set objXL = CreateObject("Excel.Application")     >> %~n0.vbs
  echo.Const xlType = 42 'Tab delimited Unicode Text     >> %~n0.vbs
  echo.With objXL.Application                            >> %~n0.vbs
  echo.  .DisplayAlerts = False                          >> %~n0.vbs
  echo.  .Visible = False                                >> %~n0.vbs
  echo.  .Workbooks.Open "%~f1"                          >> %~n0.vbs
  echo.  .Workbooks("%~nx1").SaveAs "%~dpn1.tmp", xlType >> %~n0.vbs
  echo.  .Workbooks("%~n1.tmp").Close,SaveChanges = True >> %~n0.vbs
  echo.End With                                          >> %~n0.vbs
  echo.objXL.Quit                                        >> %~n0.vbs
  echo.Set objXL = Nothing                               >> %~n0.vbs

%~n0.vbs & del %~n0.vbs

if not exist "%~dpn1.tmp" (echo.  Conversion failed & exit /B 1)
set ExcelFile=%~dpn1.tmp
exit /B 0 [End Of XLS2CSV]


Report •


Ask Question