将文本框值转换为超链接 [英] Convert textbox value to hyperlink
问题描述
是否可以将文本框值（例如文本）转换为超链接并导出到Excel电子表格？
我有一个用获取文本框值并将其导出到Excel的Visual Studio.net一切都很好。但现在我要求在我们的工厂中将设备的图片文件添加到我的电子表格中。我创建了我的opendialog和一个按钮和文本框，我可以选择文件并发送到Excel没有问题我只是无法弄清楚如何将其导出为超链接。以下是我的代码。请帮助!!
我尝试过：
公共班Form4
Dim row_min As Object
Dim row_max As Object
Dim col_min As Object
Dim col_max As Object
Dim value1 As Integer
Dim value2 As Integer
Dim strtext As String
Private Sub Form11_Load（发件人作为对象，e作为EventArgs）处理MyBase.Load
结束子
$ / $ $ b $私有Sub Button1_Click（发送者为对象，e为EventArgs）处理Button1.Click
Dim xlApp = GetObject（，Excel.Application）
Dim xlBook = xlApp.Workbooks.Open（C：\ Users\lockhartB \Desktop \ Mobile Equipment.xls）
Dim xlSheet = xlBook.Worksheets（ Sheet1）
'设定能见度
xlApp.Visible = False
'显示范围的行和列。
row_min = xlSheet.UsedRange.Row
row_max = row_min + xlSheet.UsedRange.Rows.Count - 1
col_min = xlSheet.UsedRange。专栏
col_max = col_min + xlSheet.UsedRange.Columns.Count - 1
'输入数据
xlSheet.cells（row_max + 1，col_min）.Value = ComboBox1.Text
xlSheet.cells（row_max + 1，col_min + 1 ）.Value = ComboBox3.Text
xlSheet.cells（row_max + 1，col_min + 2）.Value = TextBox3.Text
xlSheet.cells（row_max + 1，col_min + 3）.Value = TextBox4.Text
xlSheet.cells（row_max + 1，col_min + 4）。Value = TextBox1.Text
xlSheet.cells（row_max + 1，col_min + 5）.Value = TextBox2.Text
xlSheet.cells（row_max + 1，col_min + 6）.Value = ComboBox2.Text
xlSheet.cells（row_max + 1，col_min + 7）.Value = TextBox5.Text
xlSheet.cells（row_max + 1，col_min + 8）.Value = TextBox6.Text
xlSheet.cells（row_max + 1，col_min + 9）.Value = ComboBox4.Text
xlSheet.cells （row_max + 1，col_min + 10）.Value = TextBox7.Text
xlSheet.cells（row_max + 1，col_min + 11）.Value = TextBox8.Text < br $>
xlSheet.cells（row_max + 1，col_min + 12）.Value = TextBox9.Text
'保存更改
xlBook.Save（）
'关闭工作簿
xlBook.Close（False）
'退出应用
xlApp.Quit（）
ComboBox1.SelectedIndex = -1
ComboBox2.SelectedIndex = -1
ComboBox3。 SelectedIndex = -1
ComboBox4.SelectedIndex = -1
对于每个控件作为控制在Me.Controls
如果TypeOf（控制）是系统.Windows.Forms.TextBox然后
control.Text =
结束如果
下一页
结束子
私人子按钮5_Click（发件人为对象，e为EventArgs）处理Button5.Click
OpenFileDialog1.ShowDialog（）
TextBox9.Text = OpenFileDialog1.FileName
尝试
Shell（TextBox9.Text）
Catch ex As Exception
结束尝试
End Sub
结束类
Is it possible to to take textbox value (Ex. Text) convert it to a hyperlink and export to an Excel spreadsheet?
I have a project written in Visual Studio.net that takes the textbox values and exports it to Excel everything works great. But now I have a request to add a picture file of equipment in our plant to my spreadsheet. I created my opendialog and a button and textbox and I can select the file and send to Excel with no problem I just can't figure out how to export it as a Hyperlink. Below is my code. Please help!!
What I have tried:
Public Class Form4
Dim row_min As Object
Dim row_max As Object
Dim col_min As Object
Dim col_max As Object
Dim value1 As Integer
Dim value2 As Integer
Dim strtext As String
Private Sub Form11_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim xlApp = GetObject("", "Excel.Application")
Dim xlBook = xlApp.Workbooks.Open("C:\Users\lockhartB\Desktop\Mobile Equipment.xls")
Dim xlSheet = xlBook.Worksheets("Sheet1")
'set visibility
xlApp.Visible = False
' Display the range's rows and columns.
row_min = xlSheet.UsedRange.Row
row_max = row_min + xlSheet.UsedRange.Rows.Count - 1
col_min = xlSheet.UsedRange.Column
col_max = col_min + xlSheet.UsedRange.Columns.Count - 1
'enter data
xlSheet.cells(row_max + 1, col_min).Value = ComboBox1.Text
xlSheet.cells(row_max + 1, col_min + 1).Value = ComboBox3.Text
xlSheet.cells(row_max + 1, col_min + 2).Value = TextBox3.Text
xlSheet.cells(row_max + 1, col_min + 3).Value = TextBox4.Text
xlSheet.cells(row_max + 1, col_min + 4).Value = TextBox1.Text
xlSheet.cells(row_max + 1, col_min + 5).Value = TextBox2.Text
xlSheet.cells(row_max + 1, col_min + 6).Value = ComboBox2.Text
xlSheet.cells(row_max + 1, col_min + 7).Value = TextBox5.Text
xlSheet.cells(row_max + 1, col_min + 8).Value = TextBox6.Text
xlSheet.cells(row_max + 1, col_min + 9).Value = ComboBox4.Text
xlSheet.cells(row_max + 1, col_min + 10).Value = TextBox7.Text
xlSheet.cells(row_max + 1, col_min + 11).Value = TextBox8.Text
xlSheet.cells(row_max + 1, col_min + 12).Value = TextBox9.Text
'save changes
xlBook.Save()
'close workbo
xlBook.Close(False)
'quit app
xlApp.Quit()
ComboBox1.SelectedIndex = -1
ComboBox2.SelectedIndex = -1
ComboBox3.SelectedIndex = -1
ComboBox4.SelectedIndex = -1
For Each control As Control In Me.Controls
If TypeOf (control) Is System.Windows.Forms.TextBox Then
control.Text = ""
End If
Next
End Sub
Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
OpenFileDialog1.ShowDialog()
TextBox9.Text = OpenFileDialog1.FileName
Try
Shell(TextBox9.Text)
Catch ex As Exception
End Try
End Sub
End Class
推荐答案
Office Interop中提供了此文档，请点击添加方法的链接。
MS Office Interop} Excel}超链接
此示例基于从TextBox9填充的column12 。 U
Documentation for this is provided within Office Interop, follow the link for the "Add" method.
MS Office Interop } Excel } Hyperlink
This sample is based on "column12" being filled from "TextBox9". U
xlSheet.Hyperlinks.Add(xlSheet.cells(row_max + 1, col_min + 12), TextBox9.Text)
公共类Form2
Dim row_min As Object
Dim row_max As Object
Dim col_min As Object
Dim col_max As Object
Dim value1 As Integer
Dim value2 As Integer
Private Sub Form11_Load（sender as Object，e As EventArgs）Handles MyBase.Load
结束子
Private Sub Button1_Click（发送者为对象，e为EventArgs）处理Button1.Click
Dim xlApp = GetObject（，Excel.Application）
Dim xlBook = xlApp.Workbooks.Open（G：\ Plantwide Data \ Mobile Movety Status \Mobile Equipment Downtime Report \ Downtime Report \移动设备停机时间和费用.xls）
Dim xlShee t = xlBook.Worksheets（Casting）
'设置可见性
xlApp.Visible = False
'显示范围的行和列。
row_min = xlSheet.UsedRange.Row
row_max = row_min + xlSheet.UsedRange.Rows.Count - 1
col_min = xlSheet.UsedRange。专栏
col_max = col_min + xlSheet.UsedRange.Columns.Count - 1
'输入数据
xlSheet.cells（row_max + 1，col_min）.Value = ComboBox1.Text
xlSheet.cells（row_max + 1，col_min + 1 ）.Value = ComboBox3.Text
xlSheet.cells（row_max + 1，col_min + 2）.Value = TextBox3.Text
xlSheet.cells（row_max + 1，col_min + 3）.Value = TextBox4.Text
xlSheet.cells（row_max + 1，col_min + 4）。Value = TextBox1.Text
xlSheet.cells（row_max + 1，col_min + 5）.Value = TextBox2.Text
xlSheet.cells（row_max + 1，col_min + 6）.Value = ComboBox2.Text
xlSheet.cells（row_max + 1，col_min + 7）.Value = TextBox5.Text
xlSheet.cells（row_max + 1，col_min + 8）.Value = TextBox6.Text
xlSheet.cells（row_max + 1，col_min + 9）.Value = ComboBox4.Text
xlSheet.Hyperlinks .Add（xlSheet.cells（row_max + 1，col_min + 10），TextBox7.Text）
xlSheet.Hyperlinks.Add（xlSheet.cells（row_max + 1， col_min + 11），TextBox8.Text）
xlSheet.Hyperlinks.Add（xlSheet.cells（row_max + 1，col_min + 12），TextBox9.Text）
'保存更改
xlBook.Save（）
'close workbo
xlBook.Close（错误）
'退出应用
xlApp.Quit（）
ComboBox1.SelectedIndex = -1
ComboBox2.SelectedIndex = -1
ComboBox3.SelectedIndex = -1
ComboBox4.SelectedIndex = -1
For Each control As Control In Me。控件
如果TypeOf（控件）是System.Windows.Forms.TextBox那么
control.Text =
结束如果
下一页
结束次级
Private Sub Button2_Click（发送者为对象，e为EventArgs）
Dim objOutlook As Object
Dim objOutlookMsg As Object
objOutlook = CreateObject（Outlook.Application）
objOutlookMsg = objOutlook.CreateItem（0）
使用objOutlookMsg
.To =< br $> b $ b .Cc =brannon.lockhart@centuryaluminum.com
.Subject =移动设备停机时间和成本
.Body =这个是消息的主体
.HTMLBody =附上是移动设备停机和成本的副本
.Attachments.Add（G：\ Plantwide Data \ Mobile Fleet Status \Mobile Equipment Downtime Report\Downtime Report \Mobile Equipment shutdown和Cost.xls）
。发送（）'我们去！
结束
objOutlookMsg =没什么
objOutlook =没什么
结束
End Sub
Public Class Form2
Dim row_min As Object
Dim row_max As Object
Dim col_min As Object
Dim col_max As Object
Dim value1 As Integer
Dim value2 As Integer
Private Sub Form11_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim xlApp = GetObject("", "Excel.Application")
Dim xlBook = xlApp.Workbooks.Open("G:\Plantwide Data\Mobile Fleet Status\Mobile Equipment Downtime Report\Downtime Report\Mobile Equipment Downtime and Cost.xls")
Dim xlSheet = xlBook.Worksheets("Casting")
'set visibility
xlApp.Visible = False
' Display the range's rows and columns.
row_min = xlSheet.UsedRange.Row
row_max = row_min + xlSheet.UsedRange.Rows.Count - 1
col_min = xlSheet.UsedRange.Column
col_max = col_min + xlSheet.UsedRange.Columns.Count - 1
'enter data
xlSheet.cells(row_max + 1, col_min).Value = ComboBox1.Text
xlSheet.cells(row_max + 1, col_min + 1).Value = ComboBox3.Text
xlSheet.cells(row_max + 1, col_min + 2).Value = TextBox3.Text
xlSheet.cells(row_max + 1, col_min + 3).Value = TextBox4.Text
xlSheet.cells(row_max + 1, col_min + 4).Value = TextBox1.Text
xlSheet.cells(row_max + 1, col_min + 5).Value = TextBox2.Text
xlSheet.cells(row_max + 1, col_min + 6).Value = ComboBox2.Text
xlSheet.cells(row_max + 1, col_min + 7).Value = TextBox5.Text
xlSheet.cells(row_max + 1, col_min + 8).Value = TextBox6.Text
xlSheet.cells(row_max + 1, col_min + 9).Value = ComboBox4.Text
xlSheet.Hyperlinks.Add(xlSheet.cells(row_max + 1, col_min + 10), TextBox7.Text)
xlSheet.Hyperlinks.Add(xlSheet.cells(row_max + 1, col_min + 11), TextBox8.Text)
xlSheet.Hyperlinks.Add(xlSheet.cells(row_max + 1, col_min + 12), TextBox9.Text)
'save changes
xlBook.Save()
'close workbo
xlBook.Close(False)
'quit app
xlApp.Quit()
ComboBox1.SelectedIndex = -1
ComboBox2.SelectedIndex = -1
ComboBox3.SelectedIndex = -1
ComboBox4.SelectedIndex = -1
For Each control As Control In Me.Controls
If TypeOf (control) Is System.Windows.Forms.TextBox Then
control.Text = ""
End If
Next
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs)
Dim objOutlook As Object
Dim objOutlookMsg As Object
objOutlook = CreateObject("Outlook.Application")
objOutlookMsg = objOutlook.CreateItem(0)
With objOutlookMsg
.To = ""
.Cc = "brannon.lockhart@centuryaluminum.com"
.Subject = "Mobile Equipment Downtime and Costs"
.Body = "This is the body of message"
.HTMLBody = "Attached is the copy of Mobile Equipment Downtime and Costs"
.Attachments.Add("G:\Plantwide Data\Mobile Fleet Status\Mobile Equipment Downtime Report\Downtime Report\Mobile Equipment Downtime and Cost.xls")
.Send() 'Let´s go!
End With
objOutlookMsg = Nothing
objOutlook = Nothing
End
End Sub
这篇关于将文本框值转换为超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
