Importing hyperlink from Excel

  • Thread starter Thread starter John F
  • Start date Start date
J

John F

I am importing from Excel into a temporary table:

Dim rsTemp As New ADODB.Recordset

strSQL = "INSERT INTO tblTABuffer (TAKey, TaskID, IPT, Skill, Estimator,
Description, ValueType, " _
& "EngHrs, BOEAssessment) " _
& "SELECT TAKey, TaskID, IPT, Skill, Estimator, Description, Value,
EngHrs, BOEAssessment " _
& "FROM TAData IN """ & strFileName & """ ""Excel 5.0;"" WHERE EngHrs
<> 0 AND Value = ""Hours"" "

rsTemp.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly


then later copying from the temporary table, tblTABuffer to tblTAData:


'Add records from temporary table, tblTABuffer, to a permanent table,
tblTAData

Dim rsTAData As New ADODB.Recordset

strSQL = "INSERT INTO tblTAData (lngzProposalID, chrTAKey, TaskID,
chrIPT, chrSkill, " _
& "chrEstimator, chrDescription, chrValue, lngzEngHrs,
chrBOEAssessment ) " _
& "SELECT " & Me.txtProposalID & ", TAKey, TaskID, IPT, Skill,
Estimator, Description, ValueType," _
& "EngHrs, BOEAssessment FROM tblTABuffer "

rsTAData.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly



First question:

TaskID is an Excel column whose cells each contain a hyperlink. Importing
to tblTABuffer only brings in the display text to tblTABuffer.TaskID, but not
the address. Is there any way I can get both the display text and the
address?


Second question:

tblTAData will eventually be hosted on SQLServer, which does not have a
hyperlink field. Could I replace the TAKey field by 2 character fields
(which would be varchar in SQLServer), and in the SELECT clause replace
TaskID by TaskID.TextToDisplay and TaskID.Address?

Thanks,

John
 
Back
Top