[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I get an asp.net (vb.net) TextBox value into a cell on a pre-existing spreadsheet in Excel?

Posted on 2006-11-05
8
Medium Priority
?
551 Views
Last Modified: 2008-02-26

In VBscript this button-click code works fine:

  Sub OpenExcel_OnClick

      Set objExcel = CreateObject("Excel.Application")
      Set objWorkbook = objExcel.Workbooks.Open("C:\default.xls")

      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.Application")
      Set objWorkbook = objExcel.Workbooks.Open("C:\default.xls")

      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.








0
Comment
Question by:jazjef
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 16

Expert Comment

by:muzzy2003
ID: 17877099
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.
0
 
LVL 4

Author Comment

by:jazjef
ID: 17877212
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_defaultClientScript">
            <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
            <SCRIPT language="VBScript">
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
<!-- Instruct non-IE browsers to skip over VBScript modules.

  Sub OpenExcel_OnClick

      Set objExcel = CreateObject("Excel.Application")
      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

-->
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
            </SCRIPT>
      </HEAD>
      <body bgColor="#ffffff" MS_POSITIONING="GridLayout">
            <form id="Form1" method="post" runat="server">
                  &nbsp;<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>
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 17877371
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 16

Expert Comment

by:muzzy2003
ID: 17877399
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.
0
 
LVL 4

Author Comment

by:jazjef
ID: 17877404
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_defaultClientScript">
            <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
            <SCRIPT language="VBScript">
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
<!-- Instruct non-IE browsers to skip over VBScript modules.

  Sub OpenExcel_OnClick

      Set objExcel = CreateObject("Excel.Application")
      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

-->
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
            </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+Z7pN57S0wq/0mCPwcWq04fLRxRA=" />

                  &nbsp;<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>
0
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 2000 total points
ID: 17877412
Try this:

     objExcel.Cells(2, 3).Value = document.getElementById('TextBox1').value
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 17877431
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.
0
 
LVL 4

Author Comment

by:jazjef
ID: 17877493
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.Application")
     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("TextBox1").value
         
  End Sub

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question