Notes

Sub SpecResultToDB()
Dim conn As Object
Dim connStr As String
Dim query As String
Dim folderPath As String
Dim fileName As String
Dim filePath As String
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim latestFile As String
Dim latestDate As Date
Dim currentFileDate As Date
Dim todayDate As String
Dim colMaterial As Integer, colPlant As Integer, colMessage As Integer
Dim specYN As String
Dim today As String

' 폴더 경로 설정
folderPath = "C:NERP생산"

' 오늘 날짜 구하기 (yyyyMMdd 형식)
todayDate = Format(Date, "yyyymmdd")
today = Format(Date, "yyyy-mm-dd")

' 초기값 설정
latestFile = ""
latestDate = #1/1/1900#

' 파일명 검색 (Spec_Result_오늘날짜로 시작하는 .xlsx 파일)
fileName = Dir(folderPath & "Spec_Result_" & todayDate & "*.xlsx")

Do While fileName <> ""
filePath = folderPath & fileName
currentFileDate = FileDateTime(filePath)

If currentFileDate > latestDate Then
latestDate = currentFileDate
latestFile = fileName
End If

fileName = Dir
Loop

If latestFile = "" Then
MsgBox "파일을 찾을 수 없습니다!", vbExclamation
Exit Sub
End If

filePath = folderPath & latestFile
Workbooks.Open filePath
Set ws = ActiveSheet

connStr = "Driver={SQL Server};" & _
"Server=10.182.162.181;" & _
"Database=tosca_com_repo;" & _
"Uid=toscadbuser;" & _
"Pwd=Pa55w0rd!!;" & _
"TrustServerCertificate=yes;"

Set conn = CreateObject("ADODB.Connection")
conn.Open connStr

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

' 열 이름 찾기
colMaterial = FindColumn(ws, "Material")
colPlant = FindColumn(ws, "Plant")
colMessage = FindColumn(ws, "Message")

If colMaterial = 0 Or colPlant = 0 Or colMessage = 0 Then
MsgBox "필요한 열 이름(Material, Plant, Message)을 찾을 수 없습니다. 엑셀 파일을 확인하세요.", vbCritical
Exit Sub
End If

For i = 2 To lastRow
' Message에 특정 문구가 있으면 specYN을 'Y'로 설정
If InStr(ws.Cells(i, colMessage).Value, "The inspection spec already exists for the material") > 0 Then
specYN = "'Y'"
Else
specYN = "'Y'"
End If

' SQL 쿼리 작성
query = "IF EXISTS (SELECT 1 FROM TTA.dbo.[DomesticPO_MRPTEST_WARN] " & _
"WHERE MATERIAL = '" & ws.Cells(i, colMaterial).Value & "' " & _
"AND PLANT = '" & ws.Cells(i, colPlant).Value & "') " & _
"BEGIN " & _
"UPDATE TTA.dbo.[DomesticPO_MRPTEST_WARN] SET " & _
"SPECYN = " & specYN & " " & _
"WHERE MATERIAL = '" & ws.Cells(i, colMaterial).Value & "' " & _
"AND PLANT = '" & ws.Cells(i, colPlant).Value & "' " & _
"AND TransactionDate BETWEEN '" & today & "' AND '" & today & "%'" & _
"AND COMPUTERNAME IS NOT NULL;" & _
"END"

conn.Execute query
Next i

conn.Close
Set conn = Nothing

End Sub

Function FindColumn(ws As Worksheet, columnName As String) As Integer
Dim lastCol As Integer
Dim i As Integer

lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

For i = 1 To lastCol
' 대소문자 무시하고 공백 제거 후 비교
If Trim(LCase(ws.Cells(1, i).Value)) = Trim(LCase(columnName)) Then
FindColumn = i
Exit Function
End If
Next i

FindColumn = 0 ' 열 이름을 찾지 못한 경우 0 반환
End Function