Excel Hyperlinks to Sql Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Excel Hyperlinks to Sql Server

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-29 : 14:36:40

In my source excel there is one column which has email ids, but all as hyperlinks, when I import that excel file to sql, all I get is null values. Any one has come acrossed this? I cant manually delete the hyper link because I'll be fed around half million records in excel files.

Any ideas?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

bluemetal
Starting Member

26 Posts

Posted - 2005-05-01 : 20:19:21

Thats weird! I just created an Excel file with hyperlink-ed addresses and other fields as well, and it imported throught DTS perfectly. I thinkt he problem is elsewhere, probably the column doesn't match or something? Thats the case when it results in NULLs, you might want to check again.

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-02 : 00:04:26

Yes, thats very much weird... All those rows looks like blank, yet they have data when the cursor is moved over the cell, I get the email address as tool tip. Anyway finally I found couple of scripts on the net which I merged and execute as activex task. This script does work.


       Set xlapp = CreateObject("Excel.Application")

       ' Make it invisible...
	xlapp.Visible = False

       ' Add a new workbook

	Set xlbook = xlapp.Workbooks.Open(SourceFile)

       ' Add a module

	Set xlmodule = xlbook.VBProject.VBComponents.Add(1)

       ' Add a macro to the module...

	strCode = _
	"sub RemoveLink()" & vbCr & _
	"   Dim intCol,intRow,intLastRow "   & vbCr & _
	"   intRow = 1 "   & vbCr & _
	"   intCol = 6 "   & vbCr & _
	"  intLastRow = 65000 "   & vbCr & _
	"  On Error Resume Next "   & vbCr & _
	"   For intRow = 1 To intLastRow "   & vbCr & _
	"   Cells(intRow, intCol).Select "   & vbCr & _
	"   Selection.Hyperlinks(1).TextToDisplay = Replace(Selection.Hyperlinks(1).Address,""mailto:"", """") "   & vbCr & _
	"   i=i+1 "   & vbCr & _
	"   Next "   & vbCr & _
	"End Sub"
       
	xlmodule.CodeModule.AddFromString strCode

	' Run the new macro!
	xlapp.Run "RemoveLink"

	xlbook.Save
	xlbook.close
	xlapp.quit

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources