Sign in to follow this  
Followers 0
boniakowski

Excel need inputbox fill cells and @crlf

4 posts in this topic

Hello my friends, i need to fill a sheet with the follow description,

A1= Name

B1= Serial

my question is: i will open an inputbox (Fill the Name) and with an a barcode scanner i fill the data in A2 and next another input box appear asking Serial to be filled in B2 and again all the work begin from the start input box and find the next blank cell to be filled with the name and the same job for the Serial.

Can anybody help me. thanks.

i have done reading: 

_ExcelWriteSheetFromArray _ExcelWriteCell

 but i dont see nothing for me.

thanks again.

Christian Boniakowski.

Share this post


Link to post
Share on other sites



Simply do it in a loop and count the row numbers:

$iRow = 1
While 1
    $sName = InputBox(...)
    $sSerial = .... ; Add your barcode scanner code here
    If @error = 1 Then ExitLoop ; User pressed Cancel
    _ExcelWriteCell($oExcel, $sName, $iRow, 1)
    _ExcelWriteCell($oExcel, $sSerial, $iRow, 2)
    $iRow = $iRow + 1
WEnd

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

 

Simply do it in a loop and count the row numbers:

$iRow = 1
While 1
    $sName = InputBox(...)
    $sSerial = .... ; Add your barcode scanner code here
    If @error = 1 Then ExitLoop ; User pressed Cancel
    _ExcelWriteCell($oExcel, $sName, $iRow, 1)
    _ExcelWriteCell($oExcel, $sSerial, $iRow, 2)
    $iRow = $iRow + 1
WEnd

 

Excelent help MVPs i was stuck in a moment, your help rescue my mind.

this is the result of the script 100% working for me.

#include <Excel.au3>


$Location= ("c:\")
$Excel=InputBox("Ingres el Nombre de la Planilla","Introduzca el nombre del archivo a ser creado aqui...","","")
If @error = 1 Then
   Exit
   EndIf
$sExt=(".xls")
$sFile = ($Location&$Excel&$sExt)

If FileExists ($sFile) Then
   $sMsg1=MsgBox(4, "Precaucion!!!","El archivo "&$sFile& " YA EXISTE"&@CRLF&@CRLF&"¿Desea Reemplazarlo?")
      If $sMsg1=7 Then
         Exit
      EndIf
   EndIf


Local $oExcel = _ExcelBookNew() ;Create new book, make it visible
$iRow1 = 1
Local $aArray[3] = ["PUESTO", "NOMBRE", "LEGAJO"]
$iRow1 = $aArray
_ExcelWriteArray($oExcel, 1, 1, $aArray) ; Write the Array Horizontally


$iRow = 2
While 1
    $sName = InputBox("","Puesto","","")
    If $sMsg1=7 Then
      ExitLoop
      EndIf
    $sSerial = InputBox("","Nombre","","") ; Add your barcode scanner code here
    $sLegajo = InputBox("","Legajo","","") ; Add your barcode scanner code here
    If @error = 1 Then ExitLoop ; User pressed Cancel
    _ExcelWriteCell($oExcel, $sName, $iRow, 1)
    _ExcelWriteCell($oExcel, $sSerial, $iRow, 2)
    _ExcelWriteCell($oExcel, $sLegajo, $iRow, 3)
    $iRow = $iRow + 1
 WEnd

 _ExcelBookSaveAs($oExcel, $sFile, "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookClose($oExcel) ; And finally we close out

Thank you very very much.

Christian

Share this post


Link to post
Share on other sites

I've beautyfied it a bit and removed the surplus brackets and $iRow1 references (they are never used ):

#include <Excel.au3>
 
Global $Location = "c:\"
Global $Excel = InputBox("Ingres el Nombre de la Planilla", "Introduzca el nombre del archivo a ser creado aqui...", "", "")
If @error = 1 Then Exit
Global $sExt = ".xls"
Global $sFile = $Location & $Excel & $sExt
 
If FileExists($sFile) Then
   If MsgBox(4, "Precaucion!!!", "El archivo " & $sFile & " YA EXISTE" & @CRLF & @CRLF & "¿Desea Reemplazarlo?") = 7 Then Exit
EndIf
 
Global $oExcel = _ExcelBookNew() ;Create new book, make it visible
Global $aArray[3] = ["PUESTO", "NOMBRE", "LEGAJO"]
_ExcelWriteArray($oExcel, 1, 1, $aArray) ; Write the Array Horizontally
 
$iRow = 2
While 1
    $sName = InputBox("", "Puesto", "", "")
    If $sMsg1=7 Then Exit
    $sSerial = InputBox("", "Nombre", "", "") ; Add your barcode scanner code here
    $sLegajo = InputBox("", "Legajo", "", "") ; Add your barcode scanner code here
    If @error = 1 Then ExitLoop ; User pressed Cancel
    _ExcelWriteCell($oExcel, $sName, $iRow, 1)
    _ExcelWriteCell($oExcel, $sSerial, $iRow, 2)
    _ExcelWriteCell($oExcel, $sLegajo, $iRow, 3)
    $iRow = $iRow + 1
 WEnd
 
_ExcelBookSaveAs($oExcel, $sFile, "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookClose($oExcel) ; And finally we close out

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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
Sign in to follow this  
Followers 0