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