Can't find item 1

[JimmyFo]

Hi, I have a VBScript function to export to excel - it finds an item on the page and then grabs the outerHTML and uses the COM components of Excel to write it out.

However, it writes out nothing and a msgbox check to see what the HTML is is empty.

The page is a content page in a master page (i.e. the grid and the export function and button are all in a content page, not a single HTML page). would this affect it? Here is what I believe is the offending line:

<script type="text/vbscript" language="vbscript">
        sHTML = document.all("grid").outerHTML 
msgbox(sHTML)

The whole function, pretty common, it is someone else's, is here:

<script type="text/vbscript" language="vbscript">
    Function Export()
        ON ERROR RESUME NEXT 
        DIM sHTML, oExcel, fso, filePath 

        sHTML = document.all("grid").outerHTML 
msgbox(sHTML)
        SET fso = CreateObject("Scripting.FileSystemObject")

        filePath = fso.GetSpecialFolder(2) & "\MyExportedExcel.xls" 
        fso.CreateTextFile(filePath).Write(sHTML) 

        DIM i 
        SET i = 0 

        DO WHILE err.number > 0 
            err.Clear() 
            filePath = fso.GetSpecialFolder(2) & "\MyExportedExcel" & i & ".xls" 
            fso.CreateTextFile(filePath).Write(sHTML) 

            i = i + 1 
        LOOP 

        SET oExcel = CreateObject("Excel.Application") 
        IF err.number>0 OR oExcel =NULL THEN 
            msgbox("You need to have Excel Installed and Active-X Components Enabled on your System.") 
            EXIT FUNCTION 
        END IF 

        oExcel.Workbooks.open(filePath) 
        oExcel.Workbooks(1).WorkSheets(1).Name = "My Excel Data" 
        oExcel.Visible = true 
        Set fso = Nothing 

    End Function 
</script>
<asp:Button UseSubmitBehavior="false" Text="click" runat="server" OnClientClick="Export()" />

Thanks!
James

 

[tsuji]

>The whole function, pretty common, it is someone else's, is here
If it is that common, it would not contain that many mistakes.

[1] There are at least two main reasons that shtml be empty.
[1.1] document.all("grid") is nothing (ie, cannot find it).
[1.2] document.all("grid") returns a htmlelementcollection.

[2] This is a runtime error if not you use on error resume.
>set i=0

[3] You use a very cryptic loop to create a new xls file.
[tt]
<script type="text/vbscript" language="vbscript">
Function Export()
ON ERROR RESUME NEXT
DIM sHTML, oExcel, fso, filePath
[blue]dim oelem
set oelem=document.all("grid")
if oelem is nothing then 'you cannot find it
exit function 'early exit, you can wrap the whole functional part in the else if part if you like
end if
dim slen
slen=oelem.length 'make sure it is not dispHTMLElementCollection
if err.number<>0 then
exit function 'there are more than one element of "grid" return, you have to reconsider how to deal with it
end if[/blue]
sHTML = document.all("grid").outerHTML
msgbox(sHTML)
SET fso = CreateObject("Scripting.FileSystemObject")

filePath = fso.GetSpecialFolder(2) & "\MyExportedExcel.xls"
[red]'[/red]fso.CreateTextFile(filePath).Write(sHTML)

DIM i
[red]'[/red]SET i = 0
[blue]i=0[/blue]

DO WHILE not fso.fileexists(filePath)
[red]'[/red]err.Clear() 'no need
filePath = fso.GetSpecialFolder(2) & "\MyExportedExcel" & i & ".xls"
[green]'why do you write something like this to an xls file??? disregarding exist or not???[/green]
[red]'[/red]fso.CreateTextFile(filePath).Write(sHTML)

i = i + 1
LOOP


SET oExcel = CreateObject("Excel.Application")
[red]'[/red]IF err.number>0 OR oExcel =NULL THEN
IF err.number<>0 THEN
msgbox("You need to have Excel Installed and Active-X Components Enabled on your System.")
EXIT FUNCTION
END IF

oExcel.Workbooks.open(filePath)
oExcel.Workbooks(1).WorkSheets(1).Name = "My Excel Data"
[blue]oExcel.Workbooks(1).WorkSheets.cells(1,1)=sHTML[/blue]
oExcel.Visible = true
Set fso = Nothing

End Function
</script>
<asp:Button UseSubmitBehavior="false" Text="click" runat="server" OnClientClick="Export()" />
[/tt]

 

[tsuji]

Amendment
In the revision, need to add back creating the xls file before excel opening it using the original logic.
[tt]
'etc etc
[blue]fso.CreateTextFile filePath[/blue]
wscript.sleep 1000
oExcel.Workbooks.open filePath
'etc etc continuing
[/tt]

 

[JimmyFo]

Alright, thanks for the help! I put it together and ran it with a few other msgboxes for error checking and I see that it cannot find the item "grid". "grid" is an <asp:gridview> on my page with ID="grid" and all bound columns - nothing fancy. My concern now would be, since this site uses master pages, and the grid/script/etc is in a content page, would that cause an issue? I'm going to test this on a standard aspx page and see how it works. Ideas?

Thanks!
James

 

[tsuji]

Correction
This is my mistake.
[tt]
slen=oelem.length 'make sure it is not dispHTMLElementCollection
if err.number[red]=[/red]0 then
exit function 'early exit, you can wrap the whole functional part in the else if part if you like
end if
[red]err.clear[/red]
[/tt]

 

[JimmyFo]

Bah, even in a standalone page, it can't find "grid" - I'm stuck here.

 

[tsuji]

You have to inspect the page rendered by the browser using view-source and the problem will surface itself.