Write to excel named ranges
Write to excel named ranges
Write to excel named ranges
Write to excel named ranges
Write to excel named ranges
Write to excel named ranges Write to excel named ranges Write to excel named ranges Write to excel named ranges Write to excel named ranges Write to excel named ranges Write to excel named ranges Write to excel named ranges
Write to excel named ranges Write to excel named ranges
Write to excel named ranges
Go Back  Xtreme Visual Basic Talk > > > Write to excel named ranges


Reply
 
Thread Tools Display Modes
  #1  
Old 02-24-2005, 03:48 AM
staticbob's Avatar
staticbob staticbob is offline
Freshman
 
Join Date: Apr 2004
Location: Manchestershire UK
Posts: 33
Default Write to excel named ranges


Guys,

I am trying to lift text out of textboxes on a UserControl into named ranges in an excel workbook. The textbox names are exactly the same as the named ranges in excel.

I have this so far but I am getting error "Cannot find member" on "For Each nm As Excel.Name In wkbk.Names"

Any ideas ??
Thanks

Bob
Code:
Dim xl As Excel.Application
        xl = New Excel.Application
        Dim wkbk As Excel.Workbook
        Dim wkst As Excel.Worksheet
        xl.DisplayAlerts = False
        xl.Visible = False

        wkbk = xl.Workbooks.Open("c:\PWBV3 Forms\PWBV3_RFI.xls") 'usetemplatepath & doctype
        wkst = wkbk.Sheets(1)

        For Each ctl As Control In Ucont
                For Each nm As Excel.Name In wkbk.Names
                If StrComp(nm.Name, ctl.Name, CompareMethod.Text) = 0 Then
                    If TypeOf ctl Is TextBox Then
                        nm.RefersToRange.Value = CType(ctl, TextBox).Text
                    ElseIf TypeOf ctl Is ComboBox Then
                        nm.RefersToRange.Value = CType(ctl, ComboBox).Text
                    End If
                End If
            Next
Reply With Quote
  #2  
Old 02-24-2005, 11:13 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

Huh... you found a bug.

I don't know why this is, but it seems that the Enumerator for the Names collection is not exposed to .Net. It's somehow Private. You'll have to use a structure like this instead:
Code:
For i As Integer = 1 To wkbk.Names.Count Dim nm As Excel.Name = wkbk.Names.Item(i) ' Etc. ' Etc. ' Etc. Next i
Also, you might want to protect your nm.RefersToRange assignment, as not all Names actually Evaluate to a Range necessarily. So I guess I'm suggesting that it be expanded out to something like this:
Code:
For i As Integer = 1 To wb.Names.Count Dim nm As Excel.Name = wb.Names.Item(i) Dim rng As Excel.Range Try rng = nm.RefersToRange Catch rng = Nothing End Try If Not rng Is Nothing Then ' Your Code Goes Here ' Your Code Goes Here ' Your Code Goes Here End If Next i
Hope this helps!

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 03-02-2005, 04:30 AM
staticbob's Avatar
staticbob staticbob is offline
Freshman
 
Join Date: Apr 2004
Location: Manchestershire UK
Posts: 33
Default

Mike,

Thankyou - that sorted most of my code out. One more thing tho.

In this code, I am looping through all controls in a usercontrol, if the user control name matches that of a named range in the excel worksheet opened, then copy the .text to the named range text. This works well.

The 2nd loop SHOULD look for a text box shape in excel, named tb_details and should copy the contents of .net textbox tb_details. This bit doesn't work. It doesn't error, but nor does it write any text into the excel text box.

Any ideas ?

I would also like to use Richtext in my front-end and be able to retain any formatting when I write it to Excel, but I think thats a tad more difficult !

Thanks again for the help,
Bob

Code:
 Public Sub CreateDocument(ByVal Doctype As DocTypes, ByVal docnumber As Integer, ByVal Ucont As System.Windows.Forms.UserControl.ControlCollection)

        Dim xl As Excel.Application
        Try

            xl = New Excel.Application
            Dim wkbk As Excel.Workbook
            Dim wkst As Excel.Worksheet
            xl.DisplayAlerts = False
            xl.Visible = False
            Dim TemplateLocation As String
            Dim FinalDocumentLocation As String
            Dim sTestFolder As String = DynamicProperty("TestFolderPath")

            wkbk = xl.Workbooks.Open(TemplateLocation)
            wkst = wkbk.Sheets(1)

            For Each ctl As Control In Ucont
                For n As Integer = 1 To wkbk.Names.Count
                    Dim nm As Excel.Name = wkbk.Names.Item(n)
                    If StrComp(nm.Name, ctl.Name, CompareMethod.Text) = 0 Then
                        If TypeOf ctl Is TextBox Then
                            nm.RefersToRange.Value = CType(ctl, TextBox).Text
                        ElseIf TypeOf ctl Is ComboBox Then
                            nm.RefersToRange.Value = CType(ctl, ComboBox).Text
                        End If
                    End If
                Next

                'TODO P1 - This still doesn't write to the textbox shape
                For n As Integer = 1 To wkst.Shapes.Count
                    Dim shp As Excel.Shape = wkst.Shapes.Item(n)
                    If StrComp(shp.Name, ctl.Name, CompareMethod.Text) = 0 Then
                        If TypeOf ctl Is TextBox Then
                            shp.RefersToRange.Value = CType(ctl, TextBox).Text
                        ElseIf TypeOf ctl Is ComboBox Then
                            shp.RefersToRange.Value = CType(ctl, ComboBox).Text
                        End If
                    End If
                Next
            Next

            wkst.Range("tb_date").Value = Now.ToShortDateString
            wkst.Range("tb_docno").Value = docnumber.ToString("000")
            wkst.Range("tb_ContractName").Value = CurrentContract.ContractName
            wkst.Range("tb_ContractNo").Value = CurrentContract.ID

            xl.ActiveWorkbook.SaveAs(FinalDocumentLocation & CurrentContract.ID & " - " _
            & Doctype.ToString & docnumber.ToString("000") & ".xls")  '& document number

        Catch ex As Exception
            EH.Log(ex)
        Finally
            xl.DisplayAlerts = True
            xl.Quit()
            xl = Nothing
        End Try


    End Sub
Reply With Quote
  #4  
Old 03-02-2005, 01:40 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

Bob,

I'm really busy the next 2-3 days, but looking at your code, I'm confused and I think you are a bit as well:
Code:
For n As Integer = 1 To wkst.Shapes.Count Dim shp As Excel.Shape = wkst.Shapes.Item(n) If StrComp(shp.Name, ctl.Name, CompareMethod.Text) = 0 Then If TypeOf ctl Is TextBox Then shp.RefersToRange.Value = CType(ctl, TextBox).Text ElseIf TypeOf ctl Is ComboBox Then shp.RefersToRange.Value = CType(ctl, ComboBox).Text End If End If Next
You seem to have simply copied in your code using Name Objects and tried to apply it to Shapes. But Shapes don't have a .RefersToRange...

I'm not really around today and tomorrow, so I can't really kick this around right now, but to get the Text from a Shape, I think you'd want to look into something like:
Code:
shp.TextFrame.Characters.Text = CType(ctl, TextBox).Text
Give it a try, but I might not be back before Friday...

-- 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; 03-02-2005 at 01:45 PM.
Reply With Quote
  #5  
Old 03-03-2005, 02:55 AM
staticbob's Avatar
staticbob staticbob is offline
Freshman
 
Join Date: Apr 2004
Location: Manchestershire UK
Posts: 33
Default

Mike,

Thanks for the response, even when you are so busy ! I'll give it a good "kick around" and let you know what I come up with.

Cheers
Bob
Reply With Quote
  #6  
Old 03-07-2005, 08:10 AM
staticbob's Avatar
staticbob staticbob is offline
Freshman
 
Join Date: Apr 2004
Location: Manchestershire UK
Posts: 33
Default

Mike,

Thanks for the pointers, I now have this code which is still not working and is giving an invalid cast exception.

Sorry, my .NET is not great having only just moved into it from VBA.

Thanks
Bob
Code:
For Each ctl As Control In Ucont For n As Integer = 1 To wkbk.Names.Count Dim nm As Excel.Name = wkbk.Names.Item(n) If StrComp(nm.Name, ctl.Name, CompareMethod.Text) = 0 Then If TypeOf ctl Is TextBox Then nm.RefersToRange.Value = CType(ctl, TextBox).Text ElseIf TypeOf ctl Is ComboBox Then nm.RefersToRange.Value = CType(ctl, ComboBox).Text End If End If Next 'TODO P1 - This still doesn't write to the textbox shape For n As Integer = 1 To wkst.Shapes.Count Dim shp As Excel.Shape = wkst.Shapes.Item(n) If StrComp(shp.Name, ctl.Name, CompareMethod.Text) = 0 Then 'shp.TextFrame.Characters.Text = CType(ctl, TextBox).Text 'this give an InvalidCastException, I believe I am now accessing the Excel tbox text property correctly ? End If Next Next

Last edited by herilane; 03-07-2005 at 08:12 AM. Reason: [vb] tags are preferrable to [code]
Reply With Quote
  #7  
Old 03-07-2005, 08:20 AM
herilane's Avatar
herilaneWrite to excel named ranges herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Mike's very busy at the moment, so I'll try to help as far as I can.

I would suggest breaking that problematic line into two parts (the part to the left of the equals sign, and the part to the right of it) to figure out which part is causing the exception.
So the first line would get the text from the textbox in your form and assign it to a string; the second line would put that string in the Excel textbox.

A side question: what is the reason for the loops? In Excel VBA I would normally refer to the ranges and shapes by name, instead of looping through all of them and comparing their names. Is there some .Net-specific issue with that? (I don't have VB.Net in front of me at the moment so I can't check.) I mean code like this:
Code:
xlBook.Names([b]"myrangename"[/b]).RefersToRange
Reply With Quote
  #8  
Old 03-09-2005, 08:38 AM
staticbob's Avatar
staticbob staticbob is offline
Freshman
 
Join Date: Apr 2004
Location: Manchestershire UK
Posts: 33
Default

Thanks for the reply Helen,

I now have this code, and its works well. Info IS populated from my .net textbox controls to the excel text box contents, IF the names match.

My last problem is the way this handles line feeds. Line feeds in the .NET control go to Excel as a square symbol. Can I substitute these when transfering ? I would really like the text in the XL text box to appear exactly like that in the .net control.
Thanks again

Bob

Code:
                For n As Integer = 1 To wkst.Shapes.Count
                    Dim shp As Excel.Shape = wkst.Shapes.Item(n)
                    If StrComp(shp.Name, ctl.Name, CompareMethod.Text) = 0 Then
                        If TypeOf ctl Is TextBox Then
                            shp.TextFrame.Characters.Text = CType(ctl, TextBox).Text
                        ElseIf TypeOf ctl Is ComboBox Then
                            shp.TextFrame.Characters.Text = CType(ctl, ComboBox).Text
                        End If
                    End If
                Next
            Next
Reply With Quote
  #9  
Old 03-09-2005, 08:49 AM
herilane's Avatar
herilaneWrite to excel named ranges herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Shapes in Excel use Chr(10) for line feed, not the usual carriage return + line feed. Replace vbCrLf with vbLf in all your strings, and they should display correctly.
Reply With Quote
  #10  
Old 03-18-2005, 07:52 AM
staticbob's Avatar
staticbob staticbob is offline
Freshman
 
Join Date: Apr 2004
Location: Manchestershire UK
Posts: 33
Default

Quote:
Originally Posted by herilane
Shapes in Excel use Chr(10) for line feed, not the usual carriage return + line feed. Replace vbCrLf with vbLf in all your strings, and they should display correctly.
Thanks Helen, I now have this code working fine.

Cheers
Bob

Code:
            For Each ctl As Control In Ucont
                For n As Integer = 1 To wkbk.Names.Count
                    Dim nm As Excel.Name = wkbk.Names.Item(n)
                    If StrComp(nm.Name, ctl.Name, CompareMethod.Text) = 0 Then
                        If TypeOf ctl Is TextBox Then
                            nm.RefersToRange.Value = CType(ctl, TextBox).Text
                        ElseIf TypeOf ctl Is ComboBox Then
                            nm.RefersToRange.Value = CType(ctl, ComboBox).Text
                        ElseIf TypeOf ctl Is DateTimePicker Then
                            nm.RefersToRange.Value = CType(ctl, DateTimePicker).Value
                        End If
                    End If
                Next

                'Check for Textbox shapes, replace the .net vbCrLf with vbLf in Excel.
                For n As Integer = 1 To wkst.Shapes.Count
                    Dim shp As Excel.Shape = wkst.Shapes.Item(n)
                    If StrComp(shp.Name, ctl.Name, CompareMethod.Text) = 0 Then
                        If TypeOf ctl Is TextBox Then
                            shp.TextFrame.Characters.Text = Replace(CType(ctl, TextBox).Text, vbCrLf, vbLf)
                        ElseIf TypeOf ctl Is ComboBox Then
                            shp.TextFrame.Characters.Text = Replace(CType(ctl, ComboBox).Text, vbCrLf, vbLf)
                        ElseIf TypeOf ctl Is DateTimePicker Then
                            shp.RefersToRange.Value = CType(ctl, DateTimePicker).Value
                        End If
                    End If
                Next
            Next

Last edited by staticbob; 03-18-2005 at 08:07 AM.
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

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
Write to excel named ranges
Write to excel named ranges
Write to excel named ranges Write to excel named ranges
Write to excel named ranges
Write to excel named ranges
Write to excel named ranges Write to excel named ranges Write to excel named ranges Write to excel named ranges Write to excel named ranges Write to excel named ranges Write to excel named ranges
Write to excel named ranges
Write to excel named ranges
 
Write to excel named ranges
Write to excel named ranges
 
-->