[Walter Briscoe]

I wish to count bus stop usage in London.
Sheet1 has about 1000 rows.
Each row has up to 2 hyperlinks elsewhere to information on the stops in
a particular direction. 1 link is valid where a particuar route is
circular and in one direction.
e.g. Route 4 has links with "4 Archway" and "4 Waterloo"
4 Archway consists of: Waterloo Station / Waterloo Road stop F;
Waterloo Bridge / South Bank stop P; Lancaster Place stop T; ...
Archway Station / Macdonald Road
4 Waterloo consists of: Archway Station / Junction Road stop U;
Whittington Hospital stop J; Dartmouth Park Hill stop HJ; ...
Waterloo Station / Waterloo Road
There are approximately 40k cells - COUNTA is my friend. ;)
My first cut used a one column sheet for each set of stops and amounted
to about 18Mb.
My second cut used a row for each set of stops in a Sheet2 and amounted
to about 13Mb.
Both cuts take an unreadonable time to open.
I could have links to 1000 files. This might be fast, but manipulating
a set of 1000 files seems absurd.
I was thinking of about 10 files of about 100 rows; how can I link to
anywhere other than A1 in another file?
I suppose I could have a folder containing a sheet1 file and a subfolder
containing 10 subfolders of 100 single column files.
I have yet to find a comparison between uses of the HYPERLINK function
and hyperlinks using the Insert Hyperlink dialog - accessed either by
Ctrl+k or Insert Hyperlink... in Excel 2003.
--
Walter Briscoe

[GS[_6_]]

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

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[Walter Briscoe]

In message of Wed, 24 Feb 2016
18:45:31 in microsoft.public.excel.programming, Walter Briscoe
writes
I wish to count bus stop usage in London.
Sheet1 has about 1000 rows.
Each row has up to 2 hyperlinks elsewhere to information on the stops in
a particular direction. 1 link is valid where a particuar route is
circular and in one direction.
e.g. Route 4 has links with "4 Archway" and "4 Waterloo"
4 Archway consists of: Waterloo Station / Waterloo Road stop F;
Waterloo Bridge / South Bank stop P; Lancaster Place stop T; ...
Archway Station / Macdonald Road
4 Waterloo consists of: Archway Station / Junction Road stop U;
Whittington Hospital stop J; Dartmouth Park Hill stop HJ; ...
Waterloo Station / Waterloo Road
There are approximately 40k cells - COUNTA is my friend. ;)
My first cut used a one column sheet for each set of stops and amounted
to about 18Mb.
My second cut used a row for each set of stops in a Sheet2 and amounted
to about 13Mb.
Both cuts take an unreadonable time to open.
I could have links to 1000 files. This might be fast, but manipulating
a set of 1000 files seems absurd.
I was thinking of about 10 files of about 100 rows; how can I link to
anywhere other than A1 in another file?
I suppose I could have a folder containing a sheet1 file and a subfolder
containing 10 subfolders of 100 single column files.
I have yet to find a comparison between uses of the HYPERLINK function
and hyperlinks using the Insert Hyperlink dialog - accessed either by
Ctrl+k or Insert Hyperlink... in Excel 2003.

I googled for an answer and worked something out.
In c:\users\ibm\appdata\roaming\microsoft\excel,
I created book1.xls and book2.xls.
In book1,
I put focus on A1, hit Ctrl+k to open the Insert Hyperlink dialog and
typed book2.xls into the address field. Text to display was also written
with book2.xls and clicking A1 caused transfer to be made to book2 [A1].
I put focus on C3, hit Ctrl+k and typed book2.xls#Sheet1!C3 into
address.
I put focus on B2, hit Ctrl+k and typed
file:///c:\users\appdata\roaming\microsoft\excel\book2.xls #Sheet1!B2.

I think I now have the material to create a set of files with more than
one link between pairs of files.

I would still like to read a comparison between hyperlinks and the
HYPERLINK function.
=HYPERLINK("book2.xls", "book2"), =HYPERLINK("book2.xls#Sheet1!B2",
"book2"), =HYPERLINK("c:\users\ibm\appdata\roaming\microsoft \excel\book2
..xls#Sheet1!C3", "book2") and
=HYPERLINK("file:///c:\users\ibm\appdata\roaming\microsoft\excel\book2 .x
ls#Sheet1!D4", "book2") are all effective.

AFAIK, the main difference between the two methods is that cell values
can be interpolated in =HYPERLINK calls, but not with the Ctrl+k method.
--
Walter Briscoe

[Walter Briscoe]

In message of Wed, 24 Feb 2016 17:40:24 in
microsoft.public.excel.programming, GS 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.
--
Walter Briscoe

[GS[_6_]]

In message of Wed, 24 Feb 2016 17:40:24
in microsoft.public.excel.programming, GS 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.

Here's a good place to start...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[Walter Briscoe]

In message of Thu, 25 Feb 2016
14:30:03 in microsoft.public.excel.programming, Walter Briscoe
writes
In message of Wed, 24 Feb 2016 17:40:24 in
microsoft.public.excel.programming, GS 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.
--
Walter Briscoe

[Walter Briscoe]

In message of Thu, 25 Feb 2016 11:57:24 in
microsoft.public.excel.programming, GS writes
In message of Wed, 24 Feb 2016 17:40:24
in microsoft.public.excel.programming, GS 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.

Here's a good place to start...

http://www.appspro.com/conference/Da...rogramming.zip


Garry,
I DO apologise. I did not check for a reply, before sending a second
response. My question about reading hyperlinks stands.

I have just downloaded .../DatabaseProgramming.zip and speed-read
DatabaseProgramming.doc. I used to be paid to write Oracle SQL and have
no difficulty with SQL as such. I will print that .doc and read it
carefully after my wife rises - our printer is in the bedroom.
Thank you for pointing me at ADODB. It is likely to be very useful to me
in reading from .xls files, where an SQL where might be appropriate.
e.g. I have a file called stations.xls. It consists of a row of column
titles and ~500 rows of station names and data. Is 500 rows and 80
columns likely to be too small to justify using ADODB?
I would need to measure before and after and will probably leave it be
as the speed does not seem to be a problem.
I show the start of my title row and a data row:
Station Name ABRv Zone Bakerloo
Baker Street BST 1 8S-4.3&F.2 9N-3.4&2.3
(That show doors nearest platform exits on the Bakerloo Line at Baker
Street. Platform 8 is southbound and I prefer carriage 4, door 3 to the
first carriage, door 2 for reasons I do not remember. [I don't make
enough use of cell comments.] ;)

OTOH, it takes 2 minutes to open my bus stop data file.
I should probably replace my laptop, but prefer a 4*3 screen to a 16*10.
;)
--
Walter Briscoe

[Walter Briscoe]

In message of Fri, 26 Feb 2016
06:26:15 in microsoft.public.excel.programming, Walter Briscoe
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 of Thu, 25 Feb 2016
14:30:03 in microsoft.public.excel.programming, Walter Briscoe
writes
In message of Wed, 24 Feb 2016 17:40:24 in
microsoft.public.excel.programming, GS 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