edit connection OLEDBconnection / Last day of month

 

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

reset search window10

 microsoft.com/download/100295

ps พิมพ์ คำสั่งข้างล่างได้เลย

Get-ExecutionPolicy --> to see Policy

Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted ---> to unlock

Cr: 

https://www.youtube.com/watch?v=WbqWVFobJ3Y&ab_channel=TheGeekPage

Select folder and get list file in folder (VBA)

 Sub Button2_Click()

    Dim oFSO As Object

    Dim oFolder As Object

    Dim oFile As Object

    Dim i As Integer

    Dim FldrPicker As FileDialog

    Dim myFolder As String

    'Have User Select Folder to Save to with Dialog Box

    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker

        .Title = "Select A Target Folder"

        .AllowMultiSelect = False

    If .Show <> -1 Then Exit Sub 'Check if user clicked cancel button

        myFolder = .SelectedItems(1) & "\"

    End With

    Set oFSO = CreateObject("Scripting.FileSystemObject")

    Set oFolder = oFSO.GetFolder(myFolder)

    For Each oFile In oFolder.Files

        Cells(i + 1, 1) = oFile.Name

        i = i + 1

    Next oFile

    

End Sub