HTA to submit Data in Excel

X

Xlacs

Board Regular
Joined
Mar 31, 2021
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
'Im 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... up to the next blank cell If user continue to submit data.

But my code 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>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Try this.
HTML: 
<html>
<head><title>XLS Data</title>
<HTA:APPLICATION
APPLICATIONNAME="XLS Data"
SCROLL="yes"
SINGLEINSTANCE="yes"
>
</head>

<script type="text/vbscript">
Sub WriteXL()
Const xlCellTypeLastCell = 11
strFileName = "C:\Users\ChrisLacs\Desktop\Book1.xlsx"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(strFileName)
With objWorkbook.Worksheets(1)
    iLast = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
    If .Cells(iLast, 1).Value <> "" Or .Cells(iLast, 2).Value <> "" Then iLast = iLast + 1
    .Cells(iLast, 1).Value = document.getElementById("item1").value
    .Cells(iLast, 2).Value = document.getElementById("item2").value
    .Cells.EntireColumn.AutoFit
End With
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">Submit</button></p>

</form>
</body>
</html>
 
Upvote 0
Thank You. Unfortunately, data are still being stored to A1 and B1.
 
Upvote 0
Hi Xlacs - are you saying that the data is only ever going to be stored in Column A? So A1 would be Data Item1,and A2 would be Data Item2; then in the next submission, the two pieces of data would go to Cells A3 and A4...and so on...?

If that's the case, John's code above will still work, but with the following tweak:

VBA Code: 
    .Cells(iLast, 1).Value = document.getElementById("item1").value
    .Cells(iLast + 1, 1).Value = document.getElementById("item2").value

I reproduce the full code below for ease of reference:

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

<script type="text/vbscript">
Sub WriteXL()
Const xlCellTypeLastCell = 11
strFileName = "C:\Users\ChrisLacs\Desktop\Book1.xlsx"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(strFileName)
With objWorkbook.Worksheets(1)
    iLast = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
    If .Cells(iLast, 1).Value <> "" Or .Cells(iLast, 2).Value <> "" Then iLast = iLast + 1
    .Cells(iLast, 1).Value = document.getElementById("item1").value
    .Cells(iLast + 1, 1).Value = document.getElementById("item2").value
    .Cells.EntireColumn.AutoFit
End With
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">Submit</button></p>

</form>
</body>
</html>
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Solved
HTA created table to Excel
2 3
Replies
22
Views
3K
Dan_W
D
P
  • Question Question
VBA Script to send form information to a new line in excel sheet on same drive
Replies
0
Views
830
posfog
P
F
  • Question Question
How to edit excel data via webpage
Replies
0
Views
718
flarnlgso
F

Forum statistics

Threads
1,223,573
Messages
6,173,133
Members
452,501
Latest member
musallam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top