jazjef
asked on
How do I get an asp.net (vb.net) TextBox value into a cell on a pre-existing spreadsheet in Excel?
In VBscript this button-click code works fine:
Sub OpenExcel_OnClick
Set objExcel = CreateObject("Excel.Applic
Set objWorkbook = objExcel.Workbooks.Open("C
objExcel.Visible = True
objExcel.Cells(2, 2).Value = "Scooby Doo"
End Sub
However, if I want the value of "Scooby Doo" to come from an entry typed into a textbox on the interface, it doesn't work:
Sub OpenExcel_OnClick
Set objExcel = CreateObject("Excel.Applic
Set objWorkbook = objExcel.Workbooks.Open("C
objExcel.Visible = True
objExcel.Cells(2, 2).Value = TextBox1.Text
End Sub
If I use an html 'TextField' control or a 'TextBox' control it doen't matter.... the cell remains empty but the spreadsheet launches fine.
How do I get this textbox value into the spreadsheet cell? There has to be a way because txtfields on asp.net applications seem to be very common on the web for collecting user information. Please help if you can....I'm dying on this one and my head hurts from bangin' it..... many thanks.
Is this client-side VBScript running in an ASP.NET page? If so, then the chances are that your text box hasn't been rendered with an ID of "TextBox1". View the page source and find the text box. If you want, post the full page source and I'll have a look.
ASKER
Thanks Muzzy2003.... here's my full page source:
<%@ Import Namespace="System.IO" %>
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx. vb" Inherits="WebApplication22 .WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScri pt">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
<SCRIPT language="VBScript">
'XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXX
<!-- Instruct non-IE browsers to skip over VBScript modules.
Sub OpenExcel_OnClick
Set objExcel = CreateObject("Excel.Applic ation")
Set objWorkbook = objExcel.Workbooks.Open("C :\default. xls")
objExcel.Visible = True
'objExcel.Workbooks.Add
'objExcel.Cells(2, 2).Value = "Scooby Doo"
objExcel.Cells(2, 3).Value = TextBox1.Text
End Sub
-->
'XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXX
</SCRIPT>
</HEAD>
<body bgColor="#ffffff" MS_POSITIONING="GridLayout ">
<form id="Form1" method="post" runat="server">
<INPUT id="cmdOpenExcel" type="button" value="OpenExcel" name="OpenExcel">
<asp:textbox id="TextBox1" style="Z-INDEX: 102; LEFT: 72px; POSITION: absolute; TOP: 72px" runat="server"
Width="88px">Scooby Doo</asp:textbox></form>
</body>
</HTML>
<%@ Import Namespace="System.IO" %>
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScri
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
<SCRIPT language="VBScript">
'XXXXXXXXXXXXXXXXXXXXXXXXX
<!-- Instruct non-IE browsers to skip over VBScript modules.
Sub OpenExcel_OnClick
Set objExcel = CreateObject("Excel.Applic
Set objWorkbook = objExcel.Workbooks.Open("C
objExcel.Visible = True
'objExcel.Workbooks.Add
'objExcel.Cells(2, 2).Value = "Scooby Doo"
objExcel.Cells(2, 3).Value = TextBox1.Text
End Sub
-->
'XXXXXXXXXXXXXXXXXXXXXXXXX
</SCRIPT>
</HEAD>
<body bgColor="#ffffff" MS_POSITIONING="GridLayout
<form id="Form1" method="post" runat="server">
<INPUT id="cmdOpenExcel" type="button" value="OpenExcel" name="OpenExcel">
<asp:textbox id="TextBox1" style="Z-INDEX: 102; LEFT: 72px; POSITION: absolute; TOP: 72px" runat="server"
Width="88px">Scooby Doo</asp:textbox></form>
</body>
</HTML>
No, I meant the source as it's rendered to the browser. But never mind. Try replacing the relevant line of your script block with:
objExcel.Cells(2, 3).Value = TextBox1.value
If that doesn't work, try this:
objExcel.Cells(2, 3).Value = <%= TextBox1.ClientID %>.value
objExcel.Cells(2, 3).Value = TextBox1.value
If that doesn't work, try this:
objExcel.Cells(2, 3).Value = <%= TextBox1.ClientID %>.value
Basically, you are writing client-side script, where the property you want to access is the DHTML value property. The Text property is a server-side one that you would access in VB.NET (or C#) code behind. Additionally, giving an ASP.NET control a particular ID does not mean that it will be rendered to the browser with that DHTML id - that is what the second version will address.
ASKER
OK.... here's the source as rendered to the browser upon debugging.... the '.value' and '<%= TextBox1.ClientID %>.value' suggestions don't work either:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScri pt">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
<SCRIPT language="VBScript">
'XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXX
<!-- Instruct non-IE browsers to skip over VBScript modules.
Sub OpenExcel_OnClick
Set objExcel = CreateObject("Excel.Applic ation")
Set objWorkbook = objExcel.Workbooks.Open("C :\default. xls")
objExcel.Visible = True
'objExcel.Workbooks.Add
objExcel.Cells(2, 2).Value = "Scooby Doo"
objExcel.Cells(2, 3).Value = TextBox1.Text
End Sub
-->
'XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXX
</SCRIPT>
</HEAD>
<body bgColor="#ffffff" MS_POSITIONING="GridLayout ">
<form name="Form1" method="post" action="WebForm1.aspx" id="Form1">
<input type="hidden" name="__VIEWSTATE" value="dDwxMzkwODI1NzQ5Ozs +Z7pN57S0w q/0mCPwcWq 04fLRxRA=" />
<INPUT id="cmdOpenExcel" type="button" value="OpenExcel" name="OpenExcel">
<input name="TextBox1" type="text" value="Scooby Doo" id="TextBox1" style="width:88px;Z-INDEX: 101; LEFT: 72px; POSITION: absolute; TOP: 72px" /><INPUT style="Z-INDEX: 102; LEFT: 72px; WIDTH: 88px; POSITION: absolute; TOP: 120px; HEIGHT: 24px"
type="text" size="9" value="Scooby Doo" id="TextField1"></form>
</body>
</HTML>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScri
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
<SCRIPT language="VBScript">
'XXXXXXXXXXXXXXXXXXXXXXXXX
<!-- Instruct non-IE browsers to skip over VBScript modules.
Sub OpenExcel_OnClick
Set objExcel = CreateObject("Excel.Applic
Set objWorkbook = objExcel.Workbooks.Open("C
objExcel.Visible = True
'objExcel.Workbooks.Add
objExcel.Cells(2, 2).Value = "Scooby Doo"
objExcel.Cells(2, 3).Value = TextBox1.Text
End Sub
-->
'XXXXXXXXXXXXXXXXXXXXXXXXX
</SCRIPT>
</HEAD>
<body bgColor="#ffffff" MS_POSITIONING="GridLayout
<form name="Form1" method="post" action="WebForm1.aspx" id="Form1">
<input type="hidden" name="__VIEWSTATE" value="dDwxMzkwODI1NzQ5Ozs
<INPUT id="cmdOpenExcel" type="button" value="OpenExcel" name="OpenExcel">
<input name="TextBox1" type="text" value="Scooby Doo" id="TextBox1" style="width:88px;Z-INDEX:
type="text" size="9" value="Scooby Doo" id="TextField1"></form>
</body>
</HTML>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or failing that:
objExcel.Cells(2, 3).Value = document.getElementById('< %= TextBox1.ClientID %>').value
which should be pretty certain to work if (possibly) a little unnecessary.
objExcel.Cells(2, 3).Value = document.getElementById('<
which should be pretty certain to work if (possibly) a little unnecessary.
ASKER
muzzy2003:
Thanks guy!!! You nailed it dude! Works like a dream.....
PS: I did have to replace ('TextBox1') with ("TextBox1") in double quotes so it wouldn't 'comment out' .... but that did it just fine!
For others looking for this answer, here's the code that works using my original question and muzzy2003's solution:
Sub OpenExcel_OnClick
Set objExcel = CreateObject("Excel.Applic ation")
Set objWorkbook = objExcel.Workbooks.Open("C :\default. xls")
objExcel.Visible = True
objExcel.Cells(2, 2).Value = "Scooby Doo"
objExcel.Cells(2, 3).Value = document.getElementById("T extBox1"). value
End Sub
Thanks guy!!! You nailed it dude! Works like a dream.....
PS: I did have to replace ('TextBox1') with ("TextBox1") in double quotes so it wouldn't 'comment out' .... but that did it just fine!
For others looking for this answer, here's the code that works using my original question and muzzy2003's solution:
Sub OpenExcel_OnClick
Set objExcel = CreateObject("Excel.Applic
Set objWorkbook = objExcel.Workbooks.Open("C
objExcel.Visible = True
objExcel.Cells(2, 2).Value = "Scooby Doo"
objExcel.Cells(2, 3).Value = document.getElementById("T
End Sub