Convert textbox value to hyperlink

Question

0.00/5 (No votes)

See more:

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

Posted 30-Jul-18 5:36am

Member 13077455

Updated 1-Aug-18 9:05am

Add a Solution

2 solutions

Solution 2

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

Posted 1-Aug-18 9:05am

Member 13077455

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2018-07-30T05:59:00

Solution 1

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

VB

xlSheet.Hyperlinks.Add(xlSheet.cells(row_max + 1, col_min + 12), TextBox9.Text)

Posted 30-Jul-18 5:59am

MadMyche

Comments

Member 13077455 30-Jul-18 12:17pm

MadMyche Thank you so much it worked like a charm!!!!!

MadMyche 30-Jul-18 15:44pm

You're welcome

Member 13077455 1-Aug-18 15:05pm

MadMyche,

I have another question for you in my program everything works with the hyperlink the only issue I have is I have a button that gets the worksheet and attaches to email and sends out to a distribution list. The issue is when you open that attachment and click on the hyperlink it says (Can't open file). But if you go to the network drive and open the sheet where the original sheet is it will open the link. Below is my code for the email.

MadMyche 1-Aug-18 16:31pm

Try using the UNC path to the item (\\Server\Share\{folders}\filename.xls

Member 13077455 7-Aug-18 11:24am

Good morning! Hey I tried the last option you gave me with the UNC path but it didn't work. I want to run something by you that I figured out and see what you think if you don't mind. The picture files are coming from our server it is in the same folder as the spreadsheet I am writing to. When I save the report using the VB form and and email it if I go to the hyperlink I get this which is correct. \\USSEBFILEC1N1\DataPlant\Plantwide Data\Mobile Fleet Status\Casting Pictures\0224.JPG but when I hover over the hyperlink it is actually this (///C:\users\lockhartb\appdata\local\microsoft\windows\temporary\internet files\casting\pictures\0224.jpg) That is what is causing the issue. Any of your wisdom would be greatly appreciated!

MadMyche 7-Aug-18 12:05pm

If all of the users have access to that network location, what I would do is just save the Excel file to that location. This would also make the hyperlinks "pathless", and would only need to be the actual filename

Member 13077455 7-Aug-18 15:07pm

Hey thank you for all your help! I finally found the problem it had to do with a security setting in Excel. I had to uncheck the web options to save in the settings. It works like a charm now.Thank you!!