Textbox
Textbox
Textbox
Textbox
Textbox
Textbox Textbox Textbox Textbox Textbox Textbox Textbox Textbox
Textbox Textbox
Textbox
Go Back  Xtreme Visual Basic Talk > > > Textbox


Reply
 
Thread Tools Display Modes
  #1  
Old 01-16-2004, 05:05 AM
skydiver skydiver is offline
Newcomer
 
Join Date: Jan 2004
Posts: 8
Default Textbox


Hi all

I have textbox on a form what is the best way to link it to a excell worksheet I have

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Xlsheet(1).Cells = "textbox1"
End Sub ?

Many thanks in advance
Reply With Quote
  #2  
Old 01-16-2004, 05:52 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Quote:
Originally Posted by skydiver
Hi all

I have textbox on a form what is the best way to link it to a excell worksheet I have

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Xlsheet(1).Cells = "textbox1"
End Sub ?

Many thanks in advance



Code:
Private Sub TextBox1_Change() Range("a1").Select ActiveCell.Value = TextBox1.Text End Sub
Reply With Quote
  #3  
Old 01-16-2004, 10:34 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Well, you are operating in VB.Net, so you will need something like the following:
Code:
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles TextBox1.TextChanged Dim oRng As Excel.Range Dim oWS As Worksheet oWS = CType(xlSheets(1), Excel.Worksheet) oRng = oWS.Range("A1") oRng.Value = Me.TextBox1.Text NAR(oRng) NAR(oWS) End Sub Private Sub NAR(ByVal o As Object) ' This sub cleanly releases the COM Automation Object. ' Source: http://support.microsoft.com/?kbid=317109 Try System.Runtime.InteropServices.Marshal.ReleaseComObject(o) Catch Finally o = Nothing End Try End Sub
Alternatively, you could make use of .Cells as well, where Cells(1,1) would be the same as saying Range("A1"). Something like this:
Code:
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles TextBox1.TextChanged Dim oRng As Excel.Range Dim oWS As Worksheet oWS = CType(xlSheets(1), Excel.Worksheet) oRng = oWS.Cells(1,1) ' <-- Using .Cells instead of .Range oRng.Value = Me.TextBox1.Text NAR(oRng) NAR(oWS) End Sub
Note that in the above:

(1) you MUST write ".Value" explicitly. Default values that do not require a parameter are no longer recognized in .Net as they were in VB6 or in VBA.

(2) When Automating Excel from .Net, compound object references to the Excel Object Model are to be avoided like the plague.

(3) All COM Object (Range, Worksheet, Workbook, etc.) muct be released explicitly by calling InteropServices.Marshal.ReleaseComObject(o), which we invoke cleanly with the NAR() routine.

For more info on this, see the VB.Net Office Integration FAQ.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 01-16-2004 at 11:10 AM.
Reply With Quote
  #4  
Old 01-18-2004, 03:39 AM
skydiver skydiver is offline
Newcomer
 
Join Date: Jan 2004
Posts: 8
Default

Thanks mike for your reply I will try that, this stuff seems much harder than vba, is there a way or a tool to convert vba to vb.net it would help a lot? I have a macro below could you show me the code difference to change to vb.net

Many many thanks again



Sub test1()
Dim x As Range
Set x = Sheets("Data Centre").Range("B1:B1000")
With Sheets("Data Centre")
.AutoFilterMode = False
x.AutoFilter Field:=1, Criteria1:=">=1"
x.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("data collection1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
.AutoFilterMode = False
Application.CutCopyMode = False
End With
End Sub
Reply With Quote
  #5  
Old 01-18-2004, 01:27 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Skydiver,

As a practical matter, Automating Excel from VB.Net is, well, impractical. It sounds like you are tryning to do a lot using VB.Net here. Is doing the same project from VB 6.0 or from VBA an option for you? If so I think it should be a stong consideration.

(I would never do this in VB.Net if I had VB 6.0 Available. And even then I'd much rather use VBA, which is always available.)

Not trying to dissuade you, well, I guess I am, actually, I just think that VB.Net is doable, but has issues that make things 5x harder than they should be when you deal with Automation of MS Office programs such as Excel.

-- Mike

[If you wish to keep going in .Net, I'll walk you through it... you must start by "undoing" any and all compound-object references that exist in any one line. That is, pretty much any line with more than one "." in it is invalid and must be rewriten into two or more lines. You have quite a few like that... Take your first try at it and we'll see where it goes, but, again, VB6 or VBA would be a vastly easier development environment here.]
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 01-18-2004 at 07:49 PM.
Reply With Quote
  #6  
Old 01-19-2004, 04:52 AM
skydiver skydiver is offline
Newcomer
 
Join Date: Jan 2004
Posts: 8
Default

Hi Mike

Thanks for your reply, I Have a full working copy of my project in vba the only reason I wanted to do this is to have a stand alone Application but I take your point, I will have to think it over again.

I am also looking at building without the use of excell because there is many maths fuctions in vb.net.

Thanks again for your input
Reply With Quote
  #7  
Old 01-19-2004, 07:51 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Converting from VBA to VB6 would be very straight-forward. Mostly copy-paste. If you have a large project this will still be work and a fair amount of learning of VB6 (if you don't know it yet), but nothing compared to transporting it to .Net. I would say that it basically can't be done.

I am currently converting all my VBA projects to DLL libraries, using VB6 for this. I would never even think of trying this in .Net. .Net is GREAT, I love it, but they really messed up the Automation aspects.

The flip-side is that the math functions in VBA and VB6 are really weak while, as you noted, they are extensive in .Net. In the end, thought, it is probably easier to replicate the math functions you need than it is re-do everything else.

Some day VBA will probably move to the .Net environment, and then we won't have to compromise anymore .

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Textbox Display Issue - Text Wrapping swedishgonzo Excel 2 12-05-2003 10:26 AM
TextBox Event jgbarber65 General 6 08-09-2003 05:27 AM
TextBox Question unclebill General 2 04-26-2003 11:19 AM
Error in updating database thro textbox (editable msflexgrid) SURESH MENON Database and Reporting 0 03-26-2003 11:57 PM
Scroll Multiline textbox fizbim API 5 11-18-2001 05:35 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Textbox
Textbox
Textbox Textbox
Textbox
Textbox
Textbox Textbox Textbox Textbox Textbox Textbox Textbox
Textbox
Textbox
 
Textbox
Textbox
 
-->