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

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

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: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: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

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


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

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: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:shp.TextFrame.Characters.Text = CType(ctl, TextBox).Text Give it a try, but I might not be back before Friday...

-- Mike

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

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
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

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:
xlBook.Names("myrangename").RefersToRange

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


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

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.

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


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