Get external xls files from web and create a pivot with vba

[borgasia]

I have a list of excel files at a website.

http://www.domain/projects/excel1.xls
http://www.domain/projects/excel2.xls
http://www.domain/projects/excel3.xls

The files are identical format, different data.

I cannot read the files, only when i copy them to local system. (Error: ODBC Excel Driver Login Failed - Invalid Internet Address)

Until now, i tried:

Sub ChDirNet(Path As String)
Dim Result As Long
Result = SetCurrentDirectoryA(Path)
If Result = 0 Then Err.Raise vbObjectError + 1, "Error changing to new path."
End Sub

Sub MergeFiles()
Dim PT As PivotTable
Dim PC As PivotCache
Dim arrFiles As Variant
Dim strSheet As String
Dim strPath As String
Dim strSQL As String
Dim strCon As String
Dim rng As Range
Dim i As Long

strPath = CurDir
ChDirNet ThisWorkbook.Path

arrFiles = Application.GetOpenFilename("Excel Workbooks (*.xls), *.xls", , , , True)

strSheet = "Time Sheet"

If Not IsArray(arrFiles) Then Exit Sub

Application.ScreenUpdating = False

If Val(Application.Version) > 11 Then DeleteConnections_12

Set rng = ThisWorkbook.Sheets(1).Cells
rng.Clear
For i = 1 To UBound(arrFiles)
If strSQL = "" Then
strSQL = "SELECT * FROM [" & strSheet & "$]"
Else
strSQL = strSQL & " UNION ALL SELECT * FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
End If
Next i
strCon = _
"ODBC;" & _
"DSN=Excel Files;" & _
"DBQ=" & arrFiles(1) & ";" & _
"DefaultDir=" & "" & ";" & _
"DriverId=790;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5"

Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)

With PC
.Connection = strCon
.CommandType = xlCmdSql
.CommandText = strSQL
Set PT = .CreatePivotTable(TableDestination:=rng(6, 1))
End With

With PT
With .PivotFields(1) 'Week
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields(2) 'Date
.Orientation = xlRowField
.Position = 2
End With
With .PivotFields(3) 'Who
.Orientation = xlRowField
.Position = 3
End With

With .PivotFields(4) 'Project
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields(5) 'Activity
.Orientation = xlColumnField
.Position = 2
End With
With .PivotFields(6) 'Hours Sum
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
With .PivotFields(4) 'Project
.Orientation = xlColumnField
.Position = 1
End With
End With


'Clean up
Set PT = Nothing
Set PC = Nothing

ChDirNet strPath
Application.ScreenUpdating = True
End Sub

Question:
1] Do I miss something.
2] How to change the

http://www.domain/projects/excel1.xls to \\server\projects\excel1.xls

How to change a certain value within array?