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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
VBA Script to send form information to a new line in excel sheet on same drive
Replies
0
Views
708
posfog
P
Z
  • Question
How do i log into this web site and pull data?
Replies
0
Views
1K
Zino_596
Z
T
  • Question
Excel 2010 - Use a Button on each row to send e-mail with string of data from row - help wanted!
Replies
1
Views
7K
CalcSux78
CalcSux78
F
  • Question
How to edit excel data via webpage
Replies
0
Views
650
flarnlgso
F

Forum statistics

Threads
1,214,918
Messages
6,122,241
Members
449,075
Latest member
staticfluids

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