Search code examples
vbscriptexport-to-excelhta

Writing values to excel document from html interface

I have an application which is supposed to take several strings from a html interface and write the values to an excel document.

at the moment all that happens is the first 2 cells contain continuosly updating numbers and the third contains "[object]"

sub Export  
    Dim URL         ' URL being saved to Excel
    Dim FileName        ' Name for file being saved
    Dim FullPath        ' Path of file
    Dim oExcel          ' Excel
    Dim oBook           ' Workbooks
    Dim oSheet          ' Worksheets
    Dim iv              ' Interval value  
    Dim Target          ' Target  FilePath
        
    set FSO = CreateObject("Scripting.FileSystemObject")
    FullPath = FSO.GetAbsolutePathName(folderName)                  
    Target = FullPath & "\list.xls"
    Set oExcel = CreateObject("Excel.Application")  
    oExcel.Application.Visible = True
    Set oBook = oExcel.Workbooks.Open(Cstr(target))
        
    iv = 0
        
    While iv <= IC  
         URL =Cstr (URL & iv) 
         FileName = Cstr(FileName & iv)
            
        'Writes values to excel
        oExcel.Cells(1,3).value=IC   <-- ( this is the problem )
        oExcel.Cells(1,1).value=URL
        oExcel.Cells(1,2).value=FileName
        iv = iv + 1
    Wend        
        
    oBook.Save
    oExcel.Quit
        
End sub 

<Body>
    <input type="Button" value="Add to Queue" name="Export_Run" onclick="Export">
    <input type="Button" value="Extract"    name="Extractor_Run" onclick="Extract">         
    
    URL         <input type="Text" value="" name=URL> <br>
    File Name   <input type="Text" value="" name=FileName> <br>
    No. Pages   <input type="Text" value="" name=IC> <br>       
</Body>

After 7 years of personally learning programming it is now apparent that the problem was that I was writing the html element to the document, rather than the value of the html element.

Solution

  • IC_ is an HTMLInputElement object. To get the text from the input field you should use IC_.Value, and you need to convert it to an integer if you want to compare it to another integer. Also, you should assign the value just once, and work with the converted value throughout the rest of the script.

    ...
'sets Interval cap
ic = CLng(IC_.Value)

set FSO = CreateObject("Scripting.FileSystemObject")
FullPath = FSO.GetAbsolutePathName(folderName)
Target = FullPath & "\list.xls"

Set oExcel = CreateObject("Excel.Application")
oExcel.Application.Visible = True

Set oBook = oExcel.Workbooks.Open(Cstr(target))

iv = 0
ic = IC_

While iv <= ic 
    URL = Cstr (URL_1 & iv) 
    FileName = Cstr(FileName_1 & iv)

    oExcel.Cells(1,3).value = ic
    oExcel.Cells(1,1).value = URL
    oExcel.Cells(1,2).value = FileName
    iv = iv + 1
Wend
...