Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink usage
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink usage
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink usage
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink usage
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink usage
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink usage
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink usage
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink usage
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help pLease... Creating a Usage Log | Excel Programming | |||
excel using 99% cpu usage | Excel Discussion (Misc queries) | |||
100% cpu usage | Excel Discussion (Misc queries) | |||
How to project usage. | Excel Worksheet Functions | |||
SQL - TOP 1 Usage | Excel Programming |