Sub RenameConnections()
Dim conn As WorkbookConnection
Dim ole As OLEDBConnection
For Each conn In ThisWorkbook.Connections
If conn.Name = "Connection1" Then
Set ole = conn.OLEDBConnection
Sheets("Sheet1").Range("A1:A5").Clear
Sheets("Sheet1").Cells(1, 1) = ole.CommandText
'Sheets("Sheet1").Cells(1, 1) = Sheets("Sheet1").Cells(38, 1)
len_be = InStr(1, UCase(Sheets("Sheet1").Cells(1, 1)), "BETWEEN")
Sheets("Sheet1").Cells(2, 1) = Mid(Sheets("Sheet1").Cells(1, 1), 1, len_be + 7)
len_and1 = InStr(len_be + 7, UCase(Sheets("Sheet1").Cells(1, 1)), "AND")
len_and1 = InStr(len_and1 + 3, UCase(Sheets("Sheet1").Cells(1, 1)), ") AND")
Sheets("Sheet1").Cells(3, 1) = Mid(Cells(1, 1), len_and1)
StartDate = Sheets("Sheet1").Cells(7, 5) & "/" & 1 & "/" & Sheets("Sheet1").Cells(7, 6)
StopDate = Sheets("Sheet1").Cells(9, 5) & "/" & lastday(Sheets("Sheet1").Cells(9, 5), Sheets("Sheet1").Cells(9, 6)) & "/" & Sheets("Sheet1").Cells(9, 6)
Sheets("Sheet1").Cells(4, 1) = Sheets("Sheet1").Cells(2, 1) & " '" & StartDate & "' and '" & StopDate & "'" & Sheets("Sheet1").Cells(3, 1)
ole.CommandText = Sheets("Sheet1").Cells(4, 1)
conn.Refresh
End If
Next
End Sub
Function lastday(m, y)
d = 1
While IsDate(d & "/" & MonthName(CInt(m)) & "/" & CInt(y))
d = d + 1
Wend
d = d - 1
lastday = d
End Function
