0

I'm currently creating a tool that will automate the submission of Data to Excel. My problem is, I wanted to submit multiple data to Excel.

For example:

I have 2 textbox (Item 1 and Item 2) and once I click the submit button. Data should be save in the Excel sheet (A1, A2) and so on A3... If user continue to submit data.

Below is my code that store the data to A1 and B1 continuously.

<html>
<head><title>XLS Data</title>
<HTA:APPLICATION 
     APPLICATIONNAME="XLS Data"
     SCROLL="yes"
     SINGLEINSTANCE="yes"
>
</head>

<script type="text/vbscript">
 Sub WriteXL()
  strFileName = "C:\Users\ChrisLacs\Desktop\Book1.xlsx"
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = True
  Set objWorkbook = objExcel.Workbooks.Open(strFileName)
  Set objWorksheet = objWorkbook.Worksheets(1)
  Const xlCellTypeLastCell = 11
  objWorksheet.UsedRange.SpecialCells(xlCellTypeLastCell).Activate
  iLast = objExcel.ActiveCell.Row + 1
  
  objExcel.Cells(iLast, 1).Value = document.getElementById("item1").value
  objExcel.Cells(iLast, 2).Value = document.getElementById("item2").value
 


  objExcel.Cells.EntireColumn.AutoFit
  objWorkbook.Save
  objExcel.Quit
 End Sub
  
</script>
<body>
<form>
 <p>Item 1: <input type="text" id="item1" max="20" /></p>
 
  </p>
 <p>Item 2: <input type="text" id="item2" max="50" /></p>

 <p><button onclick="WriteXL">SubmitL</button></p>

</form>
</body>
</html>
Xlacssss
  • 61
  • 6
  • @ali-esmailpor the [suggested edit](https://stackoverflow.com/review/suggested-edits/28652270) is pointless for HTA HTML, client-side VBScript will not run in modern browsers. – user692942 Mar 31 '21 at 08:18
  • 1
    Does this answer your question? [Open an Excel file in exclusive mode using VBScript](https://stackoverflow.com/questions/36019574/open-an-excel-file-in-exclusive-mode-using-vbscript) – user692942 Mar 31 '21 at 08:21
  • Hi, the error message thing has been solved. Now the problem is whenever I submit data using HTA, the data is stored in A1 and B1. It should be in A1 and A2. – Xlacssss Mar 31 '21 at 08:26

1 Answers1

1

In Excel "cells" uses row, then column (Cells(Row_num, Col_num)).

Try

objExcel.Cells(1, iLast).Value = document.getElementById("item1").value
objExcel.Cells(2, iLast).Value = document.getElementById("item2").value
Doofus
  • 26
  • 2