엑셀에서 대량의 자료를 관리하기(금형정보)

공개됨 글쓴이 Admin댓글 64개

어떤 분이 회사에서 금형정보를 금형사진과 함께 엑셀에서 관리한다고 하셨는데 속도가 너무 늦어서 고민이라고 해서..

제가 엑셀에서 속도 문제없이 빨리 조회되도록 만들었습니다. 10만개까지 해 봤는데.. 그럭저럭 돌아갑니다.

금형이미지 파일은 디렉토리에 넣고 관리하는 방식으로 만들었습니다. 아래 그림 처럼 원하는 조건으로 조회할 수 있습니다. 자세한 로직은 alt+F11 하셔서 VBA 코드를 보시면 됩니다.

간단히 설명하면.. 엑셀의 특정 sheet를 DB형식으로 저장하고 SQL로 원하는 자료를 찾도록 하는 방식입니다.

금형이미지를 불러오는 테스트를 해 볼려면 첨부의 압축파일을 “금형정보관리_20160620.xlsm”과 동일한 위치에 풀면 됩니다

mold

 

프로그램 소스코드입니다. 전체 내용은 첨부파일을 확인하세요.

Option Explicit
'//
'// https://xlworks.net
'//
'// 엑셀에서 대량의 자료를 관리하기
'// 금형정보관리.xlsm
'// 2016.6.20 조회조건에 데이터유효성검사 추가
'//

Sub 단추1_Click()
 
 Sheets("검색하기").Select
 
 ' 사용자가 입력한 조건값을 아래 procedure에 넘긴다.
 getMoldInfo Cells(4, 3), Cells(6, 3), CInt(Cells(8, 3)), Cells(10, 3), Cells(12, 3)

End Sub
Sub getMoldInfo( _
 argMold As String, _
 argCustomer As String, _
 argYear As Integer, _
 argMaker As String, _
 argProduct As String)

' DB사용시 주의 사항 :
' - 64bit Office(Excel)에서 DAO 3.6 Library를 더 이상 지원하지 않으므로 DAO대신 ABODB를 사용
' - 사전에 VBA편집기의 도구(Tools) => 참조(References)에서 "Microsoft ActiveX Data Object 2.8 Library" 선택
' - recordCount를 사용하기위해서는 adOpenForwardOnly 대신에 adOpenStatic 써야함.


 Dim rs As New ADODB.Recordset
 Dim vSQL As String
 Dim vConn As String
 
 Dim vSQLmold As String
 Dim vSQLcustomer As String
 Dim vSQLyear As String
 Dim vSQLmaker As String
 Dim vSQLproduct As String

 Const START_ROW As Integer = 15
 Dim i As Integer
 
 Application.ScreenUpdating = False
 
 ' Excel을 Database로 사용
 ' Table구조 : 금형이름 거래처명 제작년도 금형제조처 제품명 우측사진 좌측사진 상판사진 하판사진
 
 '기존 검색내용 지우기
 Sheets("검색하기").Select
 Rows(START_ROW & ":" & START_ROW).Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.Delete Shift:=xlUp
 
 'SQL문을 만들 조회조건 설정 : 입력된 값이 없으면 조건절을 만들지 않는다.
 If argMold = "" Then
     vSQLmold = ""
 Else
     vSQLmold = " AND m.[금형이름] LIKE '%" & Replace(argMold, "'", "''") & "%'"
 End If
 
 If argCustomer = "" Then
     vSQLcustomer = ""
 Else
     vSQLcustomer = " AND m.[거래처명] LIKE '%" & Replace(argCustomer, "'", "''") & "%'"
 End If
 
 If argYear = 0 Then
     vSQLyear = ""
 Else
     vSQLyear = " AND m.[제작년도] = " & argYear
 End If
 
 If argMaker = "" Then
     vSQLmaker = ""
 Else
     vSQLmaker = " AND m.[금형제조처] LIKE '%" & Replace(argMaker, "'", "''") & "%'"
 End If
 
 If argProduct = "" Then
     vSQLproduct = ""
 Else
     vSQLproduct = " AND m.[제품명] LIKE '%" & Replace(argProduct, "'", "''") & "%'"
 End If
 
 vSQL = "SELECT [금형이름],[거래처명],[제작년도],[금형제조처],[제품명],[우측사진],[좌측사진],[상판사진],[하판사진]" & _
 " FROM [금형DATA$] AS m" & _
 " WHERE m.[금형이름] > '' " & _
 vSQLmold & vSQLcustomer & vSQLyear & vSQLmaker & vSQLproduct
 
 'MsgBox "sql : " & vSQL
 
 'Excel을 Database로 사용
 vConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=Excel 12.0;"
 rs.Open vSQL, vConn, adOpenStatic, adLockReadOnly, adCmdText
 
 If rs.RecordCount = 0 Then
     MsgBox ("입력한 조건에 해당하는 출력정보가 없습니다")
     Range("B" & START_ROW).Select
     Exit Sub
 End If

 
 '자료가 존재하면 엑셀에 내용을 출력하고 이미지는 하이퍼링크를 건다.
 If Not rs.EOF Then
     Do Until (rs.EOF = True) 
         Cells(START_ROW + i, 2) = i + 1
         Cells(START_ROW + i, 3) = rs("금형이름")
         Cells(START_ROW + i, 4) = rs("거래처명")
         Cells(START_ROW + i, 5) = rs("제작년도")
         Cells(START_ROW + i, 6) = rs("금형제조처")
         Cells(START_ROW + i, 7) = rs("제품명")
 
         Cells(START_ROW + i, 8) = rs("우측사진")
         Cells(START_ROW + i, 8).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 8), Address:=rs("우측사진")
 
         Cells(START_ROW + i, 9) = rs("좌측사진")
         Cells(START_ROW + i, 9).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 9), Address:=rs("좌측사진")
 
         Cells(START_ROW + i, 10) = rs("상판사진")
         Cells(START_ROW + i, 10).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 10), Address:=rs("상판사진")
 
         Cells(START_ROW + i, 11) = rs("하판사진")
         Cells(START_ROW + i, 11).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 11), Address:=rs("하판사진")
 
         rs.MoveNext
         i = i + 1
     Loop
 End If


 Range("B" & START_ROW).Select
 
 Application.ScreenUpdating = True
 
 rs.Close
 Set rs = Nothing
 
 MsgBox "조회가 완료되었습니다" 
 
End Sub

 

첨부파일 : 금형정보관리_20160620.xlsm,  금형사진.zip

엑셀에서 대량의 자료를 관리하기(금형정보)에 1개의 응답

  1. 설명해 주신대로 잠깐 테스트 해보았습니다.
    많은 자재가 네임과 이미지가 따로 있다보니 어떻게 할까 계속 고민해 왔습니다.
    어찌 이리도 필요하신걸 올려 주시는지.. 감사할 따름입니다.

    • 댓글 감사합니다. 이것 말고도 실무에서 필요한 것 위주로 많이 올려봐야 하는데.. 시간이 나지 않네요^^

  2. 소중한 자료 공유해 주시는 것 만 해도 충분히 감사할 따름입니다.
    정작 하나하나 배우고 싶지만 격무에 시달리다 보니…..
    시간이 나지 않는다는 말씀 충분히 공감하고 또 공감하네요

  3. 와.. 이거 어떻게 할수가 없어서 액세스로 구현했었는데 정말 대단하신거 같아요

  4. 넘 좋은 자료 감사합니다.~^^~*

    금형 DATA Sheet에 자료를 입력시 정해진 목록를 선택하는 방법으로 입력하고자 할때는 어떻게 해야 하는 지요?

    예) LG전자 / 삼성전자/ 현대자동차등등을 목록으로 관리하여 자료를 입력하고자 합니다
    입력시 오타 발생할수 있는 관계로.^^

    오랬동안 찾고 있던 자료라 넘 감사해요..

    • 안녕하세요. 말씀하신 것은 데이터유효성검사라는 기능을 이용해서 파일 수정해서 올려 놓았습니다.

      직접해 보실려면 다음과 같이 하시면 됩니다.

      1. Sheet하나를 만든 다음 목록으로 표시할 값을 입력하고
      2. 목록을 표시할 필드를 마우스로 선택
      3. 리본메뉴에서
      3.1 데이터 > 데이터유효성검사 > 설정탭 > 제한대상 > 목록을 선택하면
      3.2 하단에 원본(S)라고 된 것이 보임, 그 오른쪽에 있는 빨간화살표가 작게 그려진 버튼을 클릭하면
      3.3 데이터 유효성이라는 작은 팝업이 나타남, 이 상태에서 처음에 만든 목록을 표시할 값을 입력한 영역을 선택 > 엔터 > 확인

      *데이터유효성검사에 대해서는 http://www.gigumi.com/168 에 가시면 잘 설명되어 있습니다.

      • 친절하고 빠른 답변 감사드립니다.

        제가 궁금한것은 금형DATA Sheet에 있는 R1, C1에 있는 타이틀 “금형이름” 을 다른이름 “점포이름” 으로 바꾼후 검색하기 Sheet에 조회하기 버튼을 누르니 Runtime Error가 화면에 표기 되는군요.

        더불어 “Macro Procedure상에서 “금형이름” 를 “점포이름”으로 5개 모두 바꾸어 보아도 동일하게 Runtime 오류가 발생합니다.

        vConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\” & ActiveWorkbook.Name & “;” & “Extended Properties=Excel 12.0;”
        rs.Open vSQL, vConn, adOpenStatic, adLockReadOnly, adCmdText

        발생하는 원인과 교체시 오류가 나지 않는 방법을 알려 주시면 매우 감사드리겠습니다.

        • 안녕하세요. 점포이름으로 바꿔봤는데 잘 됩니다. 아마도 어딘가 공백이 있거나 틀린 부분이 있는것 같습니다. 한번 확인해 보시고요, 일단 제가 점포이름으로 바꿔서 정상적으로 실행되는 파일을 올립니다.
          다음 링크 누르시면 됩니다.

          금형정보관리_20160620_점포이름으로수정.xlsm

          • 시원스럽게 해결되었습니다, 보내주신 자료 감사드립니다. ~^^~*

  5. POP Up 으로 입력 자료를 받아서 특정 Sheet에 자료를 입력하고자 합니다.
    특정 Sheet에 있는 기존 자료는 밑으로 한줄씩 밀리고, 새로 삽입되는 자료는 맨 첫째줄에 삽하는 형태를 구현하고자 합니다, 어떻게 해야 하는지요 ??

    • 다음과 같이 해 보시기 바랍니다.

      1. 엑셀의 특정Sheet에 이름, 사번,부서라는 필드가 있다고 가정하고
      2. form으로 팝업을 만들고 이름,사번,부서에 해당하는 textbox와 command button1개를 만들고
      3. command button click event에 아래의 code를 추가

      Sheets(“Sheet1”).Select
      Rows(“2:2”).Select ‘제목은 항상 첫줄에 있으므로 DAta는 항상 두번째줄에 추가되도록 한다.
      Selection.Insert Shift:=xlDown

      Range(“A2”).Value = Me.txtName.Value
      Range(“B2”).Value = Me.txtSSN.Value
      Range(“C2”).Value = Me.txtDeptName.Value

  6. 관리자 님의 도움 감사드립니다.

    기록된 시작시간 (F2) 과 종료시간(G2) 셀을 읽어서 색깔로 표현하고자 합니다. (1시간 단위임)
    값이 0시~ 5시면 (J2:O2)까지 Blue색깔로 셀을 자동으로 채우고,.
    10시 ~23시이면 (T2:AG2)까지 Blue색깔로 셀을 자동으로 채우고자 합니다.

    어떻게 해야 하는지요 ? 궁금합니다.

    • 조건부 서식기능을 이용하시면 됩니다. 아래 사이트에 잘 설명되어 있습니다. 단, 색상을 표현하고자 하는 J2:O2영역을 먼저 선택하시고 조건부 서식을 설정하셔야 합니다.
      http://juke.tistory.com/26

  7. 조회 하기 버튼이 있듯이 혹시 수정하기 버튼으로 가능한지요 ?

    Sheet에 가서 수정하는것보다 조건에 맞는 조건에 따라 수정하는것이 있으면 좋겠다는 생각듭니다.

    • 안녕하세요. 제가 만들어서 드릴수도 있으나 대면해서 이야기하지 않는 이상 원하시는 대로 완전한 기능을 구현하기 어려울 것 같고요, 아마도 이제 자료를 Database프로그램처럼 관리하고 싶으신 것으로 이해됩니다.
      수정기능 외 자료 추가기능, 삭제기능도 아마 필요할 것 같고요. 그래서 원하시는 기능을 구현할 수 있는 적당한 책을 소개해 드리는 것이 좋을 것 같습니다.
      “엑셀매크로&VBA바이블”이라는 책(저자:최준선)을 참고하시면 원하는 기능을 추가할 수 있을 겁니다. 그 책 Chapter17 실무활용폼 부분을 보시면 자료의 검색,입력,수정기능을 구현할 수 있습니다. http://www.yes24.com/24/goods/24566411

  8. ~^^♡ 책 추천감사합니다~
    저도 막상 책이 필요하다고 생각하면서 고민중이었는데
    서점에 가면 무엇을 사는것이 선택에 힘들더 군요
    좋은 책 추전 감사드립니다
    상세히 페이지 까지 알려 주셔서 벌써 해결된것 같이 힘이 나내요
    감사합니다

  9. 문의 드립니다.

    Form 에서 Sub 함수를 호출하여 검색된 자료를 (select 문 사용) 자료를 받아 Form 에 반영하고자 합니다.

    문의) 검색된 자료를 Excel Sheet 저장하지 않고 Sub or Function 함수에서 자료를 바로 넘겨 받고자 합니다.

    다만, Function은 1개의 값만 Return하여 주어 Sub 혹은 Function에서 여러개 값을 Return 하고자 할때는 어떻게 해야 하는지요 ?

    늘 감사드립니다. ^^

    • 안녕하세요. 질문이 이해안되는 부분이 있습니다.
      “다만, Function은 1개의 값만 Return하여 주어 Sub 혹은 Function에서 여러개 값을 Return 하고자 할때는 어떻게 해야 하는지요 ?” –> 이 질문은 Function은 한개의 값만 인수로 받고, 여러개의 값(아마도 SQL로 조회한 값들인 것으로 추측됨)을 어떻게 Return을 받을 수 있는가에 대한 질문인것 같습니다. 맞는지요??

        • 보충하여 설명드리면, Sub 함수는 인수를 Return 받지 못한것 같습니다. (Function은 1개의 값만 Return하여 주는것으로 이해됨)

          관리자님의 말씀같이 Sub 함수내에서 SQL로 조회한 값 들을 어떻게 Return을 받을 수 있는가에 대한 질문입니다.

          감사합니다.

          • 추가로 Form에서 Sub를 호출하여

            Sub 함수내에서 Form의 값을 변경을 시도해 보았으나 변경이 되지 않아 문의 드립니다.

            Private Sub ComboBox1_Change()
            ComboBox2.ListIndex = ComboBox1.ListIndex
            Call ShopVsAlba(ComboBox1.Value, ComboBox3.Value)
            End Sub

            Sub ShopVsAlba(vShop As String, vAlba As String)

            Dim rs As New ADODB.Recordset
            Dim vSQL As String
            Dim vConn As String

            ‘ Application.ScreenUpdating = False
            ‘ Excel을 Database로 사용

            vSQL = “SELECT [SHOPCODE],[SHOPNAME],[ALBACODE],[ALBANAME],[SS],[SE],[MS],[ME],[TS],[TE],[WS],[WE],[US],[UE],[FS],[FE],[AS],[AE] FROM [Sheet2$] AS m WHERE m.[SHOPCODE] = ‘” & vShop & “‘ and m.[ALBACODE] = ‘” & vAlba & “‘”

            vConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\” & ActiveWorkbook.Name & “;” & “Extended Properties=Excel 12.0;”

            rs.Open vSQL, vConn, adOpenStatic, adLockReadOnly, adCmdText

            If rs.RecordCount = 0 Then
            MsgBox (“입력한 조건에 해당하는 출력정보가 없습니다”)
            Exit Sub
            End If

            If rs.RecordCount > 1 Then
            MsgBox (“조회한 자료가 중복되어 있습니다. 정정바랍니다.”)
            Exit Sub
            End If

            MsgBox “자료가 있습니다” & rs(“SHOPCODE”) & rs(“SHOPNAME”) & rs(“ALBACODE”) & rs(“ALBANAME”) & rs(“SS”) & _
            rs(“SE”) & rs(“MS”) & rs(“ME”)

            ‘ *** 자료가 있는 경우는 SS, SE, MS, ME 값을 Form 에 있는 값을 변경하고자 하고자 합니다.

            rs.Close
            Set rs = Nothing

            End Sub

          • 값을 리턴받기 위해서는 function을 이용해야 합니다. 그런데 여러 필드의 값을 여러개 리턴 받아야 하므로 필드를 구조체로 선언하고 선언된 구조체를 배열로 받아야 합니다.
            이 내용은 현재 연재중인 Database강의 4강에 포함될 예정입니다(오늘 중으로 4강 업로드 예정)

            ‘//선언부에 구조체선언
            Type shop
            shopcode As String
            shopname As String
            albacode As String
            End Type

            ‘//function으로 작성하고 구조체 shop을 배열로 리턴 받음
            Function ShopVsAlba(vShop As String, vAlba As String) as shop()

            …..중략
            sql로 값을 resultset으로 받아서 shop()에 넣고 리턴
            …..중략

  10. 오!! ..*^^* 친절한 답변 ~ 감사해요.!
    말씀 하신것 같이 시도해 보겠습니다,
    좋은 오후 되시고요…

  11. 먼저 감사하다는 말을 전합니다.
    한가지 에러가 뜰때도 있고 안뜰때도 있고 PC가 바뀌거나 뭔가 이상합니다.

    에러는 rs.Open vSQL, vConn, adOpenStatic, adLockReadOnly, adCmdText
    여기에 노란줄이 뜨면서 발생하는겁니다.

    ‘MsgBox “sql : ” & vSQL

    ‘Excel을 Database로 사용
    vConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\” & ActiveWorkbook.Name & “;” & “Extended Properties=Excel 12.0;”
    rs.Open vSQL, vConn, adOpenStatic, adLockReadOnly, adCmdText

    If rs.RecordCount = 0 Then
    MsgBox (“입력한 조건에 해당하는 출력정보가 없습니다”)
    Range(“B” & START_ROW).Select
    Exit Sub
    End If

    • 안녕하세요.
      노란줄이 뜨면서 어떤 에러가 났는지 알려주시면 좋을 것 같습니다. 그리고, 에러가 뜰때도 있고 안뜰때도 있으면 엑셀 sheet에 입력되어 있는 data 또는 조회 조건으로 입력한 값에 문제가 있을 수 있을 것 같습니다.
      가능하시다면 문제의 파일을 이메일(admin@xlworks.net)로 보내주시면 살펴보고 연락드리겠습니다.
      감사합니다.

  12. 좋은 자료 감사합니다.
    그런데 세로로 찍은 사진을 조회해서 실행히시키면 가로로 화면에 나오는데 세로로 나오게 할수 없을까요?
    그리고 사진 조회 항목을 더 늘리고 싶은데 어떻게 하면 될까요? ㅠ
    죄송합니다. 초보라 제가 직접해보고 싶은데 도저히 알수가 없어 문의드립니다.

    • 안녕하세요. 다음과 같이 답변드립니다.
      1. 세로로 찍은 사진이 가로로 나오는 것은 이미지뷰어 프로그램(예:알씨 등..)에서 그렇게 설정이 되어 있어서 그런것 같습니다. 이미지뷰어 프로그램의 설정을 확인해 보시기 바랍니다.
      알씨의 경우 “도구>환경설정>보기설정>사진 자동회전(촬영정보반영)”에서 check box표시를 해제해 보시기 바랍니다.
      2. 사진 조회항목을 더 늘리기 위해서는 여러 군데를 고쳐야 합니다. VBA와 Database프로그래밍에 관해 약간의 지식이 있으면 도움이 될듯하며 아래의 항목을 고치시기 바랍니다.
      – 금형DATA sheet에 필드 추가(하판사진 필드 오른쪽에 원하는 만큼의 필드 추가)
      – 모듈 Module1의 getMoldInfo procedure : “SELECT [금형이름],[거래처명],[제작년도],[금형제조처],[제품명],[우측사진],[좌측사진],[상판사진],[하판사진]” 라고 되어 있는데 부분에서 필드 추가 ==> ,[필드A],[필드B] 이런식으로 추가
      – 모듈 Module1의 getMoldInfo procedure : 다음의 소스 아래에 추가된 필드에 해당하는 로직 추가하되 숫자가 바뀌는 부분을 유심히 보시기 바랍니다. 필드가 추가될때 숫자가 늘어납니다. 현재 11이 마지막이고 추가하면 12, 13… 이런식으로 되겠지요.
      Cells(START_ROW + i, 11) = rs(“하판사진”)
      Cells(START_ROW + i, 11).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 11), Address:=rs(“하판사진”)

      이상입니다. 시행착오가 있을 수 있겠지만.. 프로그램 소스를 유심히 보시면 하실 수 있을 겁니다.
      그럼, 즐거운 하루 되시기 바랍니다

  13. 안녕하세요. 제작년도를 년월일 (2017-01-01)로 변경하고자하는데 argYear As Integer 이부분을 변경하면 될런지.. 도움 부탁 드리겠습니다.

    • 안녕하세요. 다음과 같이 해 보시기 바랍니다.
      1. “검색하기” 시트에서 C8셀,E열전체의 표시형식을 “간단한 날짜”형식으로 변경
      2. “금형DATA” 시트에서 C열전체의 표시형식을 “간단한 날짜”형식으로 변경
      3. Sub 단추1_Click() 에서 “CInt(Cells(8, 3))”로 되어 있는 부분을 “Cells(8, 3)”로 변경
      4. Sub getMoldInfo의 인자값 받는 부분의 “argYear As Integer”를 “argYear As Long”으로 변경

      그럼.. 즐거운 하루 되세요.

  14. 조회하기 후에 하이퍼링크가 열지않아서 문의 드립니다.
    어디에 사진을 저장하고 축척해야 조회하기 한 후 사진이 열릴가요??

    • 안녕하세요. 조회하려면 사진을 “금형정보관리_20160620.xlsm” 파일과 동일한 위치에 저장해야 합니다.

  15. 세로 100만개이상,가로 12개 자료를넣으려하는데 104만에서 안되어요
    방법 있나요?
    한시트에서 보야하는거거든요

  16. 안녕하세요 항상 감사합니다.. 많이 배우고 가요 다름이아니라 다른건 다 되는데 휴대전화번호가 안되네요.. 명령어도 모르겠구.. 소스코드 첨부할께요..

    Option Explicit
    ‘//
    ‘// https://xlworks.net
    ‘//
    ‘// 엑셀에서 대량의 자료를 관리하기
    ‘// 금형정보관리.xlsm
    ‘// 2016.6.20 조회조건에 데이터유효성검사 추가
    ‘//

    Sub 단추1_Click()

    Sheets(“검색하기”).Select

    ‘ 사용자가 입력한 조건값을 아래 procedure에 넘긴다.
    getMoldInfo Cells(4, 3), Cells(6, 3), Cells(8, 3), Cells(10, 3), Cells(12, 3)

    End Sub
    Sub getMoldInfo( _
    argMold As String, _
    argCustomer As String, _
    argNember As Integer, _
    argMaker As String, _
    argProduct As String)

    ‘ DB사용시 주의 사항 :
    ‘ – 64bit Office(Excel)에서 DAO 3.6 Library를 더 이상 지원하지 않으므로 DAO대신 ABODB를 사용
    ‘ – 사전에 VBA편집기의 도구(Tools) => 참조(References)에서 “Microsoft ActiveX Data Object 2.8 Library” 선택
    ‘ – recordCount를 사용하기위해서는 adOpenForwardOnly 대신에 adOpenStatic 써야함.

    Dim rs As New ADODB.Recordset
    Dim vSQL As String
    Dim vConn As String

    Dim vSQLmold As String
    Dim vSQLcustomer As String
    Dim vSQLnember As String
    Dim vSQLmaker As String
    Dim vSQLproduct As String

    Const START_ROW As Integer = 15
    Dim i As Integer

    Application.ScreenUpdating = False

    ‘ Excel을 Database로 사용
    ‘ Table구조 : 이름 주민번호 핸드폰번호 연락처 분회 조합원상태 지부 가입일 탈퇴일

    ‘기존 검색내용 지우기
    Sheets(“검색하기”).Select
    Rows(START_ROW & “:” & START_ROW).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp

    ‘SQL문을 만들 조회조건 설정 : 입력된 값이 없으면 조건절을 만들지 않는다.
    If argMold = “” Then
    vSQLmold = “”
    Else
    vSQLmold = ” AND m.[이름] LIKE ‘%” & Replace(argMold, “‘”, “””) & “%'”
    End If

    If argCustomer = “” Then
    vSQLcustomer = “”
    Else
    vSQLcustomer = ” AND m.[지부] LIKE ‘%” & Replace(argCustomer, “‘”, “””) & “%'”
    End If

    If argYear = 0 Then
    vSQLyear = “”
    Else
    vSQLnember = ” AND m.[핸드폰번호] = ” & argNember
    End If

    If argMaker = “” Then
    vSQLmaker = “”
    Else
    vSQLmaker = ” AND m.[분회] LIKE ‘%” & Replace(argMaker, “‘”, “””) & “%'”
    End If

    If argProduct = “” Then
    vSQLproduct = “”
    Else
    vSQLproduct = ” AND m.[주민번호] LIKE ‘%” & Replace(argProduct, “‘”, “””) & “%'”
    End If

    vSQL = “SELECT [이름],[지부],[핸드폰번호],[분회],[주민번호],[조합원상태],[특이사항],[주소],[가입일]” & _
    ” FROM [금형DATA$] AS m” & _
    ” WHERE m.[이름] > ” ” & _
    vSQLmold & vSQLcustomer & vSQLnember & vSQLmaker & vSQLproduct

    ‘MsgBox “sql : ” & vSQL

    ‘Excel을 Database로 사용
    vConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\” & ActiveWorkbook.Name & “;” & “Extended Properties=Excel 12.0;”
    rs.Open vSQL, vConn, adOpenStatic, adLockReadOnly, adCmdText

    If rs.RecordCount = 0 Then
    MsgBox (“입력한 조건에 해당하는 조합원정보가 없습니다”)
    Range(“B” & START_ROW).Select
    Exit Sub
    End If

    ‘자료가 존재하면 엑셀에 내용을 출력하고 이미지는 하이퍼링크를 건다.
    If Not rs.EOF Then
    Do Until (rs.EOF = True)

    Cells(START_ROW + i, 2) = i + 1
    Cells(START_ROW + i, 3) = rs(“이름”)
    Cells(START_ROW + i, 4) = rs(“지부”)
    Cells(START_ROW + i, 5) = rs(“핸드폰번호”)
    Cells(START_ROW + i, 6) = rs(“분회”)
    Cells(START_ROW + i, 7) = rs(“주민번호”)

    Cells(START_ROW + i, 8) = rs(“조합원상태”)

    Cells(START_ROW + i, 9) = rs(“특이사항”)

    Cells(START_ROW + i, 10) = rs(“주소”)

    Cells(START_ROW + i, 11) = rs(“가입일”)

    rs.MoveNext
    i = i + 1
    Loop
    End If

    Range(“B” & START_ROW).Select

    Application.ScreenUpdating = True

    rs.Close
    Set rs = Nothing

    MsgBox “조회가 완료되었습니다”

    End Sub

    보시면 휴대폰번호 란입니다..ㅠ 도움좀 부탁드릴게요 고수님 ㅠㅠㅠ

    • 안녕하세요. 휴대전화번호가 안된다고 하셨는데요. argYear 라는 파라미터는 존재하지 않네요. 그래서 아마도 컴파일 에러가 휴대전화부분 로직에서 나는 것 같습니다.

      If argYear = 0 Then
      vSQLyear = “”
      Else
      vSQLnember = ” AND m.[핸드폰번호] = ” & argNember
      End If
      위 코드를 아래처럼 바꿔보시기 바랍니다. 파라미터에 휴대전화번호가 들어오지 않으면 sql문장을 만들지 않고 들어올때만 sql문장을 만드는 로직입니다.
      만약 휴대전화번호가 숫자가 아니라 문자형식(010-3434-1212 처럼)으로 되어 있으면 If argNember = 0 Then 를 If argNember = “” Then 로 바꾸시기 바랍니다.
      If argNember = 0 Then
      vSQLnember = “”
      Else
      vSQLnember = ” AND m.[핸드폰번호] = ” & argNember
      End If

      • 네 답변 감사드리구요 ㅠㅠ 바꿨는데

        Sub 단추1_Click()

        Sheets(“검색하기”).Select

        ‘ 사용자가 입력한 조건값을 아래 procedure에 넘긴다.
        getMoldInfo Cells(4, 3), Cells(6, 3), CInt(Cells(8, 3)), Cells(10, 3), Cells(12, 3)

        여기서 또 오류가 나네요 ㅠ.ㅠ

  17. 안녕하세요. 항상 많은 도움을 얻고있습니다. 감사합니다.

    하이퍼링크 항목 대신에 메모부분을 불러오고 싶어서 질문드립니다.

    Cells(START_ROW + i, 11) = rs(“하판사진”)
    Cells(START_ROW + i, 11).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 12), Address:=rs(“하판사진”)

    Hyperlinks <– 이부분을 수정하면 될 듯 한데 메모는 어떻게 써야할지 모르겠습니다. ㅠㅠ

    – 메모는 채우기를 통해서 이미지 입니다.

    • 안녕하세요. 다음과 같이 하시면 됩니다. 단, 주의하실 점은 세번째줄처럼 파일의 전체 경로를 적어주어야 합니다.
      Cells(START_ROW + i, 11).ClearComments
      Cells(START_ROW + i, 11).AddComment rs(“하판사진”).Value
      Cells(START_ROW + i, 11).Comment.Shape.Fill.UserPicture “C:\Users\xxxx\Downloads\” & rs(“하판사진”).Value

      추가로 더 궁금한 내용은 다음 링크를 참고하세요
      https://www.thespreadsheetguru.com/the-code-vault/vba-insert-image-into-cell-comment

      감사합니다.

  18. 안녕하세요. 이번에 엑셀 vba를 처음 배우고 있는 대학생입니다.
    올려주신 내용 정말 유익하게 잘 보았습니다.
    세 가지만 질문 올리겠습니다.

    제가 지금 하고싶은 것은 sql data 베이스를 여러 곳에서 불러오고자 (연도별로 있는 파일 또는 시트)

    vSQL = “SELECT [금형이름],[거래처명],[제작년도],[금형제조처],[제품명],[우측사진],[좌측사진],[상판사진],[하판사진]” & _
    ” FROM [금형DATA$] AS m” & _
    ” WHERE m.[금형이름] > ” ” & _

    에서 FROM 부분을

    FROM [ startFile & 금형DATA$] 처럼도 해보고
    FROM [startFile & “금형DATA$”] 처럼 여러가지 해 보았으나
    되지않았습니다. (startFile은 for each 문을 활용하여 연도를 바꾸어줄 생각이었습니다.)

    혹시 이렇게 FROM 안의 부분에 변수를 활용하는 것은 불가능한 것인가요?

    그리고 FROM 부분에서 현재 파일이 아닌 다른 파일을 참조해 오는것이 가능한지, 한번에 여러 파일(또는 시트)를 vSQL로 만드는 것이 가능한지 알고싶습니다.

    읽어주셔서 감사합니다.

    • 안녕하세요. 다음과 같이 답변드립니다.
      1. SQL FROM절에 변수 넣기 :
      ” FROM [” & startFile & “금형DATA$] AS m” & _
      2. 현재 파일이 아닌 다른 파일을 참조하기 : DB연결문자 만드는 부분의 “ActiveWorkbook.Name”을 실제이름으로 변경, 단, 다른 파일도 같은 폴더에 있어야 함.
      vConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\” & “금형정보관리_20160620_다른파일.xlsm” & “;” & “Extended Properties=Excel 12.0;”
      3. 한번에 여러 파일 사용 : 동일한 파일에서 여러 시트를 참고하되 SQL UNION을 사용하면 될것 같습니다.
      감사합니다.

      • 좋은 정보 감사합니다.
        댓글을 적었는데 사라져서 다시 질문을 드려봅니다.

        3. 한번에 여러 파일 사용 : 동일한 파일에서 여러 시트를 참고하되 SQL UNION을 사용하면 될것 같습니다.

        한파일내 여러 시트를 참조하기 와
        여러파일에서 데이터를 가져오게 하려면 어떻게 수정을 해야 할까요?
        시도를 해보는데 뜻대로 되지가 않습니다.

        감사합니다.

        • 안녕하세요.
          구체적으로 어떤 부분이 안되는지를 알려주시고 작업하고 계신 파일을 보내주셔야(admin@xlworks.net) 도움을 드릴 수 있습니다.
          감사합니다.

  19. 안녕하세요^^
    먼저 소중한 자료 사용하게 허락해 주셔서 감사 드립니다.

    제가 “금형이름” 부분을 “코드”로 수정하고 두번째 시트의 “코드”에 약 10여개의 숫자를 입력하여
    “조회”를 하려고 수정을 하였는데요

    문자+숫자는 “조회” 시 error가 없는데, 숫자만 적용하고 “조회”를 하면 오류가 나네요
    제가 엑셀만 조금 사용하는 초보다 보니 염치불구하고 문의 드리니다.

    감사합니다.

    • 안녕하세요. 답변이 늦었네요.
      금형이름 필드는 원래 문자필드로 정의되어 있으므로(정확히는 2행부터 다음 몇개의 행에 입력된 값의 data type으로 자동으로 인식됨) 이 필드에 문자와 숫자를 섞어서 사용할 수 없습니다. 만약 숫자로 된 값을 입력해서 사용하시려면 숫자를 문자로 인식하도록 숫자앞에 작은따옴표를 붙이시기 바랍니다. 예) ’15, ‘100.. 등
      감사합니다.

  20. 안녕하세요 시트를 통해 업무에 많은 도움이 되고 있습니다. 다름이 아니라 날짜 입력에 월만 추출하여 검색하고 싶은데 어려움이 있어 문의 드립니다. 예)2018-01-01에서 1월 만 검색하고 싶습니다

  21. 안녕하세요, 어머니가 사업하시면서 항상 겪는 자료정리를 해결해드리고 싶었는데, 좋은 자료 제공해 주셔서 감사합니다…!

    근데 조회하기를 누르면, Cannot run the macro “file name”. The macro may not be available in this workbook or all macros may be disabled 이렇게 에러 메시지가 뜨더라구요 ㅠ, Alt + F11 해서 들어가보니

    rs.Open vSQL, vConn, adOpenForwardOnly, adLockReadOnly, adCmdText 라는 곳에 노란색으로 하이라이트가 되어있던데, 이런 문제는 어떻게 해결하는지 알려 주실수 있을까요 ? ~

    • 안녕하세요. 파일을 열때 상단에 보면 보안경고가 나타나는데 매크로 사용가능하도록 해 주시고요, 그래도 안되면 옵션에서 매크로 사용가능하도록 설정하고 사용해 보시기 바랍니다.
      감사합니다.

  22. 안녕하세요. 좋은 자료 올려주셔서 너무감사합니다. 업무하는데 정말 큰 도움이되었습니다!!
    다름이 아니라 아무것도 입력하지않고 조회하기를 눌렀을때 모든자료가 출력되게 하지말고
    아무것도 안뜨게 하고싶은데 조언좀 부탁드립니다 ㅜㅜ 다시한번 감사합니다!

    • 안녕하세요. 조회조건을 입력하지 않고 조회를 하면 전체조회를 하는 것이 일반적이긴합니다. 원하시는 대로 하시려면 다음과 같이 단추1_Click 이벤트를 수정해서 사용하시면 됩니다.

      Sub 단추1_Click()

      Sheets(“검색하기”).Select

      ‘//조회조건을 아무것도 입력하지 않을 때는 조회하지 않도록 처리
      If Cells(4, 3) & Cells(6, 3) & Cells(8, 3) & Cells(10, 3) & Cells(12, 3) = “” Then
      MsgBox “조회조건을 최소한 한개는 입력해야 합니다”
      Exit Sub
      End If

      ‘ 사용자가 입력한 조건값을 아래 procedure에 넘긴다.
      getMoldInfo Cells(4, 3), Cells(6, 3), CInt(Cells(8, 3)), Cells(10, 3), Cells(12, 3)

      End Sub

      감사합니다.

  23. 안녕하세요 좋은강좌 잘보고 잘 배웠습니다. 완전 초보다보니 어렸네요^^
    다름이 아니라 강좌에 나온 자료를 토대로 변경하여 만들었는데 잘안되는 2가지가 있어서 이렇게 적어봅니다.
    한가지는 검색어 지정을 해주면 완전 동일한 항목만 뜨게 하고 싶은데 검색어가 포함된 내용한까지 검색이되네요. Like부분을 수정하면되것 같은데 잘안되네요.
    두번쨰는 데이터에 하이퍼링크를 걸어서 데이터 입력해두었는데 검색결과에 하이퍼링크가 동일하게 넘어 오지 않아 검색결과에서는 파일이 열리지 않아요. 이건 도통 모르겠습니다.
    도와주시면 너무나 감사하겠습니다. 혹시몰라 이메일로 자료 보내놓겠습니다.

    • 안녕하세요. 다음과 같이 답변드립니다.
      1. SQL에서 LIKE 연산자는 유사한 것을 찾을 때 씁니다. 일치하는 것을 찾을 때는 다음과 같이 “=”을 사용해야합니다. 필드명이 character이면 조회조건에 사용하는 값을 홑따옴표롤 둘러싸야합니다.
      ” AND 필드명 = ‘” & Replace(조회인수, “‘”, “””) & “‘”
      2. 하이퍼링크는 완전한 경로를 적어주어야 합니다. 폴더명과 확장자명도 일치해야 합니다. 예제로 올린 XLSM 파일의 이미지 파일 경로를 확인해 보면 폴더명과 확장자명이 붙어 있습니다.
      감사합니다.

  24. 안녕하세요 관리자님 전에 한번 여쭤봤는데 도움을 주셔서 잘 사용하고 있습니다.
    다시한번 여쭤보려고 댓글 남깁니다.
    금형data시트에서 금형이름에 메모를 추가해서 제품사진을 넣었을때
    검색해서 밑에 목록이 쭉 뜨면 거기에도 금형data의 메모에 입력한 사진이 나오게
    할수있는 방법이 있을까요?
    감사합니다.

  25. 안녕하세요 많은 도움이 되었습니다.

    다름이 아니라 날짜를 검색할 때 두가지 열에서 일치하는 값이 있으면 목록에 가져오게 하고 싶은데 가능할까요? 만약에 날짜가 제조일 / 사용일이 있다면 하나의 검색을 했을 때 제조일 중 혹은 사용일 중 일치하는 값을 가져오는 기능입니다.

    • 안녕하세요. SQL의 OR연산자를 이용하시면 됩니다.
      WHERE 제조일 = ‘입력값’ OR 사용일 = ‘입력값’
      감사합니다.

  26. 안녕하세요
    자료공개 너무 감사드립니다.
    관련해서 이 자료를 개인용도로 사용해도 무방할까요?

    • 안녕하세요. 개인용도로 사용하셔도 됩니다. 혹시 이자료를 다른 분께 공유하실 경우에는 출처를 밝혀 주시면 좋겠습니다^^
      감사합니다.

  27. 좋은 자료에 감사드립니다.

    파일이 많으면 해당 파일이 삭제되거나 없을 수도 있습니다.
    이를 조회해서 해당 파일이 없으면 위의 엑셀에 붉은색으로 표기하는 방법은 어떻게 하면 될까요?

댓글 남기기

이메일은 공개되지 않습니다.