본문 바로가기

전체 글1609

i.dll 2024. 9. 27.
1 1 2024. 9. 26.
0926 0926 2024. 9. 26.
1 1 2024. 9. 26.
sql vba Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" sql = "SELECT " & _ "COUNT(*) AS TotalCount " & _ "FROM [Sheet1$] " & _ "WHERE [Column1] = 'Value1' " & _ "AND [Column2] = 'Value2' " & _ "AND [Column3] = 'V.. 2024. 7. 25.
보완2 Sub ListExcelFiles() Dim folderPath As String folderPath = "C:\Your\Folder\Path" ' 탐색할 폴더 경로를 여기에 입력하세요. Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ws.Cells.Clear ListFilesInFolder folderPath, ws, 1 End Sub Sub ListFilesInFolder(folderPath As String, ws As Worksheet, ByRef row As Long) Dim fileName As String Dim subFolder As String Dim currentPath As String ' 폴더 경로에 백슬래시 추가 If Ri.. 2024. 7. 23.
보완코드 Sub ListExcelFiles() Dim folderPath As String folderPath = "C:\Your\Folder\Path" ' 탐색할 폴더 경로를 여기에 입력하세요. Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ws.Cells.Clear ListFilesInFolder folderPath, ws, 1 End Sub Sub ListFilesInFolder(folderPath As String, ws As Worksheet, ByRef row As Long) Dim fileName As String Dim subFolder As String Dim currentPath As String ' 폴더 경로에 백슬래시 추가 If Ri.. 2024. 7. 23.
참조사용하지 않는 코드. 폴더 탐색해서 시트정리 Sub ListExcelFiles() Dim folderPath As String folderPath = "C:\Your\Folder\Path" ' 탐색할 폴더 경로를 여기에 입력하세요. Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ws.Cells.Clear ListFilesInFolder folderPath, ws, 1 End Sub Sub ListFilesInFolder(folderPath As String, ws As Worksheet, ByRef row As Long) Dim fileName As String Dim subFolder As String Dim currentPath As String ' 폴더 경로에 백슬래시 추가 If Ri.. 2024. 7. 23.
v2 폴더탐색 정리 Sub ListExcelFiles() Dim folderPath As String folderPath = "C:\Your\Folder\Path" ' 탐색할 폴더 경로를 여기에 입력하세요. Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ws.Cells.Clear ListFilesInFolder folderPath, ws, 1 End Sub Sub ListFilesInFolder(folderPath As String, ws As Worksheet, ByRef row As Long) Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") Dim folder As Object Set.. 2024. 7. 23.
탐색후 리스트작성 Sub ListExcelFiles() Dim folderPath As String folderPath = "C:\Your\Folder\Path" ' 탐색할 폴더 경로를 여기에 입력하세요. Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ws.Cells.Clear ListFilesInFolder folderPath, ws, 1 End Sub Sub ListFilesInFolder(folderPath As String, ws As Worksheet, ByRef row As Long) Dim fso As Object, folder As Object, file As Object, subFolder As Object Set fso = CreateObject.. 2024. 7. 23.
파일여부 확인 Sub CheckIfFolderIsEmpty() Dim folderPath As String Dim folder As Object Dim file As Object Dim isEmpty As Boolean ' 확인할 폴더 경로를 지정합니다. folderPath = "C:\Your\Folder\Path" ' FileSystemObject 생성 Set folder = CreateObject("Scripting.FileSystemObject").GetFolder(folderPath) ' 폴더가 비어 있는지 확인 isEmpty = True For Each file In folder.Files isEmpty = False Exit For Next file If isEmpty Then For Each file In.. 2024. 7. 12.
파일갯수 세기 Sub CountXlsxFiles() Dim folderPath As String Dim fileName As String Dim fileCount As Integer ' 폴더 경로를 설정합니다. 예: "C:\example\" folderPath = "C:\example\" ' 파일 갯수를 초기화합니다. fileCount = 0 ' 폴더 내의 첫 번째 파일을 찾습니다. fileName = Dir(folderPath & "*.xlsx") ' 파일이 존재하는 한 반복합니다. Do While fileName "" ' 파일 갯수를 증가시킵니다. fileCount = fileCount + 1 ' 다음 파일을 찾습니다. fileName = Dir Loop ' 결과를 메시지 박스로 출력합니다. MsgBox "폴더 내.. 2024. 7. 12.