Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108653

Re: Hyperlink usage

Show all headers | View raw

In message <8YMdcZQHA$zWFwM7@freenetname.co.uk> of Fri, 26 Feb 2016
06:26:15 in microsoft.public.excel.programming, Walter Briscoe
<wbriscoe@nospam.demon.co.uk> writes

I have a partial answer to the problem given below.
ADODB does not seem to be able to copy Hyperlinks or cells which
evaluate something. However, it does allow strings to be copied and a
string can contain anything, including "=...".

>In message <+RnNuvLr$wzWFwdV@freenetname.co.uk> of Thu, 25 Feb 2016
>14:30:03 in microsoft.public.excel.programming, Walter Briscoe
><wbriscoe@nospam.demon.co.uk> writes
>>In message <nalbbc$5h0$1@dont-email.me> of Wed, 24 Feb 2016 17:40:24 in
>>microsoft.public.excel.programming, GS <gs@v.invalid> writes
>>>Hi Walter,
>>>The logistics of your task are really better suited to database
>>>management functions. Why so many files? Why not 1 file for the data
>>>and 1 file to report the data however desired using ADODB so there's no
>>>need to open anything, and only having to ref 1 file!
>>>
>>Garry,
>>I have no ADODB knowledge, but will happily look at it on your advice.
>
>I Googled for Excel ADODB Example and found
><https://technet.microsoft.com/en-us/library/ee692882.aspx>
>I find it a pity the author does not declare variables and I failed to get
>Wscript.Echo to work, but substituted debug.print for that and got a
>working example.
>
>I created a c:\scripts folder, containing Test.xls
>In Sheet1, I wrote
>Name    Number
>A       1 Canada Water
>B       1 Canada Water
>C       2
>D       2
>E       1
>F       1
>
>The first "1 Canada Water" is a hyperlink with
>Text to display: "1 Canada Water"
>and Address: "https://tfl.gov.uk/bus/route/1/?direction=outbound"
>The second "1 Canada Water" is a function call:
>=HYPERLINK("https://tfl.gov.uk/bus/route/1/?direction=outbound",
>"1 Canada Water")
>
>I created a code module:
>Option Explicit
>
>Sub DisplayData()
>    Const adOpenStatic = 3
>    Const adLockOptimistic = 3
>    Const adCmdText = &H1
>
>    Dim objConnection As Variant
>    Dim objRecordset As Variant
>    Dim Probe As Variant
>
>
>    Set objConnection = CreateObject("ADODB.Connection")
>    Set objRecordset = CreateObject("ADODB.Recordset")
>
>    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                        "Data Source=C:\Scripts\Test.xls;" & _
>                       "Extended Properties=""Excel 8.0;HDR=Yes;"";"
>
>    objRecordset.Open "Select * FROM [Sheet1$]", objConnection, _
>        adOpenStatic, adLockOptimistic, adCmdText
>
>    Do Until objRecordset.EOF
>        Debug.Print objRecordset.Fields.Item("Name"), _
>                    objRecordset.Fields.Item("Number")
>        objRecordset.MoveNext
>    Loop
>End Sub
>
>The data written was
>A
>B
>C              2
>D              2
>E              1
>F              1
>
>I Googled for ADODB hyperlink, but found nothing useful.
>I saw various references which suggested hyperlinks can be used,
>but most referred to Access and/or wrote HTML.
>There were references to defining fields as hyperlinks,
>but no examples.
>Can I have a pointer to some material documenting ADODB, please?
>If I have to, I can split each hyperlink into 2 strings:
>"1 Canada Water"
>and "https://tfl.gov.uk/bus/route/1/?direction=outbound"
>and construct a hyperlink fromthem.

I modified the data above to
Name    Number
A       1 Canada Water
B       "=HYPERLINK(""https://tfl.gov.uk/bus/route/1/?direction=outbound
"", ""1 Canada Water"")"
C       2
D       1 Canada Water
E       1
F       1

[B2] is a hyperlink and [B4] contains
=HYPERLINK("https://tfl.gov.uk/bus/route/1/?direction=outbound", "1
Canada Water")
ADODB won't cope with either.
However some post-processing of [B3] can transform it into something
useful.

I threw together the following to copy Sheet1 to Sheet2.
Option Explicit

Sub DisplayData()
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Dim objConnection As Variant
    Dim objRecordset As Variant
    Dim Probe As Variant
    Dim sheet2row As Long
    Dim nameValue As Variant
    Dim numberValue As Variant

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")

    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=C:\Scripts\Test.xls;" & _
                       "Extended Properties=""Excel 8.0;HDR=Yes;"";"

    objRecordset.Open "Select * FROM [Sheet1$]", objConnection, _
        adOpenStatic, adLockOptimistic, adCmdText

    sheet2row = 1
    Sheets(2).Cells(sheet2row, 1) = "Name"
    Sheets(2).Cells(sheet2row, 2) = "Number"
    Do Until objRecordset.EOF
        nameValue = objRecordset.Fields.Item("Name")
        numberValue = objRecordset.Fields.Item("Number")
        Debug.Print nameValue, numberValue
        sheet2row = sheet2row + 1
        Sheets(2).Cells(sheet2row, 1) = nameValue
        Sheets(2).Cells(sheet2row, 2) = numberValue
        If numberValue Like """=*" Then
            ' Trim initial and final string quotes and halve others
            Sheets(2).Cells(sheet2row, 2) =
WorksheetFunction.Substitute( _
                Mid(numberValue, 2, Len(numberValue) - 2), _
                """""", """")
        End If
        objRecordset.MoveNext
    Loop
End Sub

I have solved my original problem by splitting my data into 10 files and
will probably not put ADODB into practice for that problem. (I always
have more that I want to do than time to do it. ;)
-- 
Walter Briscoe

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Hyperlink usage Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-02-24 18:45 +0000
  Re: Hyperlink usage GS <gs@v.invalid> - 2016-02-24 17:40 -0500
    Re: Hyperlink usage Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-02-25 14:30 +0000
      Re: Hyperlink usage GS <gs@v.invalid> - 2016-02-25 11:57 -0500
        Re: Hyperlink usage Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-02-26 07:10 +0000
      Re: Hyperlink usage Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-02-26 06:26 +0000
        Re: Hyperlink usage Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-03-19 08:51 +0000
  Re: Hyperlink usage Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-02-24 22:52 +0000

csiph-web