어떤 분이 회사에서 금형정보를 금형사진과 함께 엑셀에서 관리한다고 하셨는데 속도가 너무 늦어서 고민이라고 해서..
제가 엑셀에서 속도 문제없이 빨리 조회되도록 만들었습니다. 10만개까지 해 봤는데.. 그럭저럭 돌아갑니다.
금형이미지 파일은 디렉토리에 넣고 관리하는 방식으로 만들었습니다. 아래 그림 처럼 원하는 조건으로 조회할 수 있습니다. 자세한 로직은 alt+F11 하셔서 VBA 코드를 보시면 됩니다.
간단히 설명하면.. 엑셀의 특정 sheet를 DB형식으로 저장하고 SQL로 원하는 자료를 찾도록 하는 방식입니다.
금형이미지를 불러오는 테스트를 해 볼려면 첨부의 압축파일을 "금형정보관리_20160620.xlsm"과 동일한 위치에 풀면 됩니다
프로그램 소스코드입니다. 전체 내용은 첨부파일을 확인하세요.
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
설명해 주신대로 잠깐 테스트 해보았습니다.
많은 자재가 네임과 이미지가 따로 있다보니 어떻게 할까 계속 고민해 왔습니다.
어찌 이리도 필요하신걸 올려 주시는지.. 감사할 따름입니다.
댓글 감사합니다. 이것 말고도 실무에서 필요한 것 위주로 많이 올려봐야 하는데.. 시간이 나지 않네요^^
소중한 자료 공유해 주시는 것 만 해도 충분히 감사할 따름입니다.
정작 하나하나 배우고 싶지만 격무에 시달리다 보니.....
시간이 나지 않는다는 말씀 충분히 공감하고 또 공감하네요
와.. 이거 어떻게 할수가 없어서 액세스로 구현했었는데 정말 대단하신거 같아요
넘 좋은 자료 감사합니다.~^^~*
금형 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
시원스럽게 해결되었습니다, 보내주신 자료 감사드립니다. ~^^~*
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
넘~감사해요~
즐거운 주말 보내세요~^^~*
'Rows("2:2").Select '제목은 항상 첫줄에 있으므로 DAta는 항상 두번째줄에 추가되도록 한다.'
이부분을 제목은 항상 첫줄이고 기록되는 데이터는 마지막줄 일때는 어떻게 해야할까요?
다음과 같이 해 보세요.
Private Sub CommandButton1_Click()
Dim lastRow As Long
Sheets("Sheet1").Select
lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(lastRow, 1) = Me.txtName.Value
Cells(lastRow, 2) = Me.txtSSN.Value
Cells(lastRow, 3) = Me.txtDeptName.Value
End Sub
감사합니다^^
관리자 님의 도움 감사드립니다.
기록된 시작시간 (F2) 과 종료시간(G2) 셀을 읽어서 색깔로 표현하고자 합니다. (1시간 단위임)
값이 0시~ 5시면 (J2:O2)까지 Blue색깔로 셀을 자동으로 채우고,.
10시 ~23시이면 (T2:AG2)까지 Blue색깔로 셀을 자동으로 채우고자 합니다.
어떻게 해야 하는지요 ? 궁금합니다.
조건부 서식기능을 이용하시면 됩니다. 아래 사이트에 잘 설명되어 있습니다. 단, 색상을 표현하고자 하는 J2:O2영역을 먼저 선택하시고 조건부 서식을 설정하셔야 합니다.
http://juke.tistory.com/26
좋은 정보 감사드립니다. ^^~*
조회 하기 버튼이 있듯이 혹시 수정하기 버튼으로 가능한지요 ?
Sheet에 가서 수정하는것보다 조건에 맞는 조건에 따라 수정하는것이 있으면 좋겠다는 생각듭니다.
안녕하세요. 제가 만들어서 드릴수도 있으나 대면해서 이야기하지 않는 이상 원하시는 대로 완전한 기능을 구현하기 어려울 것 같고요, 아마도 이제 자료를 Database프로그램처럼 관리하고 싶으신 것으로 이해됩니다.
수정기능 외 자료 추가기능, 삭제기능도 아마 필요할 것 같고요. 그래서 원하시는 기능을 구현할 수 있는 적당한 책을 소개해 드리는 것이 좋을 것 같습니다.
"엑셀매크로&VBA바이블"이라는 책(저자:최준선)을 참고하시면 원하는 기능을 추가할 수 있을 겁니다. 그 책 Chapter17 실무활용폼 부분을 보시면 자료의 검색,입력,수정기능을 구현할 수 있습니다. http://www.yes24.com/24/goods/24566411
~^^♡ 책 추천감사합니다~
저도 막상 책이 필요하다고 생각하면서 고민중이었는데
서점에 가면 무엇을 사는것이 선택에 힘들더 군요
좋은 책 추전 감사드립니다
상세히 페이지 까지 알려 주셔서 벌써 해결된것 같이 힘이 나내요
감사합니다
문의 드립니다.
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()에 넣고 리턴
.....중략
오!! ..*^^* 친절한 답변 ~ 감사해요.!
말씀 하신것 같이 시도해 보겠습니다,
좋은 오후 되시고요...
먼저 감사하다는 말을 전합니다.
한가지 에러가 뜰때도 있고 안뜰때도 있고 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)로 보내주시면 살펴보고 연락드리겠습니다.
감사합니다.
좋은 자료 감사합니다.
그런데 세로로 찍은 사진을 조회해서 실행히시키면 가로로 화면에 나오는데 세로로 나오게 할수 없을까요?
그리고 사진 조회 항목을 더 늘리고 싶은데 어떻게 하면 될까요? ㅠ
죄송합니다. 초보라 제가 직접해보고 싶은데 도저히 알수가 없어 문의드립니다.
안녕하세요. 다음과 같이 답변드립니다.
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("하판사진")
이상입니다. 시행착오가 있을 수 있겠지만.. 프로그램 소스를 유심히 보시면 하실 수 있을 겁니다.
그럼, 즐거운 하루 되시기 바랍니다
안녕하세요. 제작년도를 년월일 (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"으로 변경
그럼.. 즐거운 하루 되세요.
정말 감사합니다!
안녕하세요. 예를들어, 2017-01-01 부터 2017-11-17까지의 값만 불러 오고 싶을 경우에 어떻게 수정을 할 수 있을지 도움 주실 수 있을까요? 고맙습니다.
안녕하세요. 간단히 구현절차를 알려드리면...
1. 시작날짜(2017-1-1)와 끝날짜(2017-11-17)를 입력할 수 있는 필드를 "검색하기"시트에 추가
2. 전에 알려드린대로 년도를 날짜 타입으로 변경
3. 단추1_Click()에서 시작날짜, 끝날짜를 입력받도록 수정
4. getMoldInfo에서 시작날짜,끝날짜 구간에 들도록 SQL 수정( ex:
[제작일자] >= "입력받은 시작날짜" and [제작일자] <= "입력받은 끝날짜" ) 코드까지 짜서 드리는 못하는 점 양해 부탁드립니다. 사이트에 있는 Database강좌를 보시거나 (https://xlworks.net/category/tutorials/tut_database/)
엑셀 VBA, database 프로그래밍 도서를 참고하시기를 권해 드립니다.
감사합니다.
조회하기 후에 하이퍼링크가 열지않아서 문의 드립니다.
어디에 사진을 저장하고 축척해야 조회하기 한 후 사진이 열릴가요??
안녕하세요. 조회하려면 사진을 "금형정보관리_20160620.xlsm" 파일과 동일한 위치에 저장해야 합니다.
세로 100만개이상,가로 12개 자료를넣으려하는데 104만에서 안되어요
방법 있나요?
한시트에서 보야하는거거든요
안녕하세요. 엑셀 2007버전부터 2016버전까지 세로(행)의 갯수는 1048576개까지만 가능합니다.
자세한 내용은 다음 링크를 확인해 보세요.
https://support.office.com/ko-kr/article/excel-사양-및-제한-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=2007
감사합니다.
안녕하세요 항상 감사합니다.. 많이 배우고 가요 다름이아니라 다른건 다 되는데 휴대전화번호가 안되네요.. 명령어도 모르겠구.. 소스코드 첨부할께요..
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)
여기서 또 오류가 나네요 ㅠ.ㅠ
안녕하세요. 항상 많은 도움을 얻고있습니다. 감사합니다.
하이퍼링크 항목 대신에 메모부분을 불러오고 싶어서 질문드립니다.
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
감사합니다.
안녕하세요. 이번에 엑셀 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) 도움을 드릴 수 있습니다.
감사합니다.
안녕하세요^^
먼저 소중한 자료 사용하게 허락해 주셔서 감사 드립니다.
제가 "금형이름" 부분을 "코드"로 수정하고 두번째 시트의 "코드"에 약 10여개의 숫자를 입력하여
"조회"를 하려고 수정을 하였는데요
문자+숫자는 "조회" 시 error가 없는데, 숫자만 적용하고 "조회"를 하면 오류가 나네요
제가 엑셀만 조금 사용하는 초보다 보니 염치불구하고 문의 드리니다.
감사합니다.
안녕하세요. 답변이 늦었네요.
금형이름 필드는 원래 문자필드로 정의되어 있으므로(정확히는 2행부터 다음 몇개의 행에 입력된 값의 data type으로 자동으로 인식됨) 이 필드에 문자와 숫자를 섞어서 사용할 수 없습니다. 만약 숫자로 된 값을 입력해서 사용하시려면 숫자를 문자로 인식하도록 숫자앞에 작은따옴표를 붙이시기 바랍니다. 예) '15, '100.. 등
감사합니다.
고맙습니다.^^
해결 하였습니다.
안녕하세요 시트를 통해 업무에 많은 도움이 되고 있습니다. 다름이 아니라 날짜 입력에 월만 추출하여 검색하고 싶은데 어려움이 있어 문의 드립니다. 예)2018-01-01에서 1월 만 검색하고 싶습니다
안녕하세요, 어머니가 사업하시면서 항상 겪는 자료정리를 해결해드리고 싶었는데, 좋은 자료 제공해 주셔서 감사합니다...!
근데 조회하기를 누르면, 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 라는 곳에 노란색으로 하이라이트가 되어있던데, 이런 문제는 어떻게 해결하는지 알려 주실수 있을까요 ? ~
안녕하세요. 파일을 열때 상단에 보면 보안경고가 나타나는데 매크로 사용가능하도록 해 주시고요, 그래도 안되면 옵션에서 매크로 사용가능하도록 설정하고 사용해 보시기 바랍니다.
감사합니다.
안녕하세요. 좋은 자료 올려주셔서 너무감사합니다. 업무하는데 정말 큰 도움이되었습니다!!
다름이 아니라 아무것도 입력하지않고 조회하기를 눌렀을때 모든자료가 출력되게 하지말고
아무것도 안뜨게 하고싶은데 조언좀 부탁드립니다 ㅜㅜ 다시한번 감사합니다!
안녕하세요. 조회조건을 입력하지 않고 조회를 하면 전체조회를 하는 것이 일반적이긴합니다. 원하시는 대로 하시려면 다음과 같이 단추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
감사합니다.
안녕하세요 좋은강좌 잘보고 잘 배웠습니다. 완전 초보다보니 어렸네요^^
다름이 아니라 강좌에 나온 자료를 토대로 변경하여 만들었는데 잘안되는 2가지가 있어서 이렇게 적어봅니다.
한가지는 검색어 지정을 해주면 완전 동일한 항목만 뜨게 하고 싶은데 검색어가 포함된 내용한까지 검색이되네요. Like부분을 수정하면되것 같은데 잘안되네요.
두번쨰는 데이터에 하이퍼링크를 걸어서 데이터 입력해두었는데 검색결과에 하이퍼링크가 동일하게 넘어 오지 않아 검색결과에서는 파일이 열리지 않아요. 이건 도통 모르겠습니다.
도와주시면 너무나 감사하겠습니다. 혹시몰라 이메일로 자료 보내놓겠습니다.
안녕하세요. 다음과 같이 답변드립니다.
1. SQL에서 LIKE 연산자는 유사한 것을 찾을 때 씁니다. 일치하는 것을 찾을 때는 다음과 같이 "="을 사용해야합니다. 필드명이 character이면 조회조건에 사용하는 값을 홑따옴표롤 둘러싸야합니다.
" AND 필드명 = '" & Replace(조회인수, "'", "''") & "'"
2. 하이퍼링크는 완전한 경로를 적어주어야 합니다. 폴더명과 확장자명도 일치해야 합니다. 예제로 올린 XLSM 파일의 이미지 파일 경로를 확인해 보면 폴더명과 확장자명이 붙어 있습니다.
감사합니다.
안녕하세요 관리자님 전에 한번 여쭤봤는데 도움을 주셔서 잘 사용하고 있습니다.
다시한번 여쭤보려고 댓글 남깁니다.
금형data시트에서 금형이름에 메모를 추가해서 제품사진을 넣었을때
검색해서 밑에 목록이 쭉 뜨면 거기에도 금형data의 메모에 입력한 사진이 나오게
할수있는 방법이 있을까요?
감사합니다.
안녕하세요. 아래 댓글을 참고하세요.
https://xlworks.net/mgt_largedata_in_excel/#comment-1287
안녕하세요 많은 도움이 되었습니다.
다름이 아니라 날짜를 검색할 때 두가지 열에서 일치하는 값이 있으면 목록에 가져오게 하고 싶은데 가능할까요? 만약에 날짜가 제조일 / 사용일이 있다면 하나의 검색을 했을 때 제조일 중 혹은 사용일 중 일치하는 값을 가져오는 기능입니다.
안녕하세요. SQL의 OR연산자를 이용하시면 됩니다.
WHERE 제조일 = '입력값' OR 사용일 = '입력값'
감사합니다.
안녕하세요
자료공개 너무 감사드립니다.
관련해서 이 자료를 개인용도로 사용해도 무방할까요?
안녕하세요. 개인용도로 사용하셔도 됩니다. 혹시 이자료를 다른 분께 공유하실 경우에는 출처를 밝혀 주시면 좋겠습니다^^
감사합니다.
좋은 자료에 감사드립니다.
파일이 많으면 해당 파일이 삭제되거나 없을 수도 있습니다.
이를 조회해서 해당 파일이 없으면 위의 엑셀에 붉은색으로 표기하는 방법은 어떻게 하면 될까요?
안녕하세요. 다음과 같은 함수를 만들어서 loop돌면서 check해서 없으면 글자의 색상을 바꾸도록 구현하면 될것 같습니다.
Function checkExistFile(ByVal fileName As String) As Boolean If Dir(fileName) <> "" Then checkExistFile = True Else checkExistFile = False End If End Function감사합니다.
안녕하세요, 올려주신 자료로 잘 공부 하고 있습니다.
해당 자료로 활용하고 싶은 부분이 있어서 문의 드립니다.
>Cells(START_ROW + i, 11).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 11), Address:=rs("하판사진")
해당 구문에서 하판사진에 Image 가 없을 경우 Error 가 발생합니다만,
Image 가 있을 경우는 link 를 출력하고, 없을 경우에도 "없는 상태 그대로 출력" 가능한 방법이 있는지 문의 드립니다.
감사합니다.
안녕하세요. 다음과 같이 하시면 됩니다.
If IsNull(rs("하판사진")) Then Cells(START_ROW + i, 11) = "이미지 없음" Else Cells(START_ROW + i, 11) = rs("하판사진") Cells(START_ROW + i, 11).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 11), Address:=rs("하판사진") End If감사합니다.
안녕하세요, 유용한 자료 공유 감사합니다.
해당 내용으로 자료 관리를 하려고 하는데
연도별로 나눌 경우 excel 파일이 여러개가 될텐데,
다수의 excel 파일 내에서 해당 내용을 검색하여 표시 되도록 구현하려면 어떤 code가 추가되야할까요?(예를 들어, 2018년.xlsx / 2019년.xlsx / 검색용.xlsx 으로 구성하여 내용 기입은 2018년/2019년에만 작성하고 검색용은 내용 기입이 안된 상태로 두 excel 파일에서의 검색용도로만 활용하려고 합니다.)
안녕하세요.
파일이 2개로 나누어질경우 프로그램이 복잡해 집니다. 2018년을 먼저 검색하고 없으면 2019년을 검색하는 식으로 전체 파일을 돌아가면서 검색해야 합니다. 구체적으로는 특정 디렉토리내의 파일이 있는 지 확인하고 있으면 검색하고, 다음 파일이 있으면 검색하고 파일이 더 이상 없으면 검색을 멈추고 결과를 검색용.xlsx에 보여 주는 등 처리할 로직이 많을 것 같습니다. 코드로 표현하기에는 양이 많아서 간단히 글로 설명하는 점 양해바랍니다.
감사합니다.
안녕하세요, 공유해주신 자료가 정말 큰 도움이 되었네요 감사합니다.
다만 검색란의 필터를 없애거나 다른 빈칸에 추가하고 싶은데 이럴 때의 코드는 어떻게되나요?
이것저것 손대봤지만 초보라 오류만 잔뜩 생기네요ㅠ
안녕하세요. 다른 빈칸에 추가하면 위치에 맞게 값을 참고하도록 해야하고 Datadase쪽 SQL도 바꿔주셔야 합니다. 간단한 예제이지만 VBA와 Datadase프로그래밍 지식이 필요합니다. 인터넷이나 책을 통해서 학습하시기를 권해드립니다. 감사합니다.
답변감사합니다.
다만 제 나름대로 이것저것 찾아보고 적용해봤지만 에러가 자꾸 발생하는지라... 추가하기가 힘들다면 없애는 방향으로 알려주실 수 있으실까요?
자체해결했습니다^^ 필터가 아닌 콤보박스 해제를 하니 해결되었네요
필터링으로만 검색하다보니 제가 원하는 자료가 안나온 것 같습니다.
시간내주셔서 감사합니다.
안녕하세요. 유용한 포스팅 덕에 잘 활용을 하고있는데, 검색으로도 잘 해결을 못하겠는 부분이 있어서 질문을 하고 싶습니다.
다른게 아니라 파일을 동작하면 한번씩 "-2147467259(80004005)'런타임 오류" 가 뜨는데 디버그를 선택하면 코드 중 rs.Open vSQL, vConn, adOpenStatic, adLockReadOnly, adCmdText 부분을 가리킵니다.
몇번 껏다 켜보면 다시 작동하는데 이 오류는 어떤 이유로 뜨는건지, 해당 오류가 생기는걸 막기위해선 어떤식으로 접근해야 할지 궁금합니다.
안녕하세요. DB연결하고 오픈할때 오류가 발생하는 것 같습니다. 껏다 켜면 다시 작동하는 것을 보면 PC에 문제가 있는 것 같습니다만, 파일을 admin@xlworks.net으로 보내주시면 확인하고 답변드리겠습니다.
감사합니다.
안녕하세요 . 유용한 프로그램 잘 쓰고 있습니다.
다만 궁금한게 있어 댓글을 남깁니다.
우측사진,좌측 사진란에 사진대신 메로를 입력하고 싶습닙다.
설정이 궁금합니다.
감사합니다.
이 글의 댓글에서 "메모"로 검색하시면 중간쯤에 메모를 가져오는 방법이 나와있으미 참고하시면 됩니다.
관리자님
좋은 프로그램 제공해 주셔서 감사합니다
다름이 아니라 제작년도란에 일반적으로 설치 위치로 바꾸고 싶습니다.
제작년도는 명령어 year로 되어 있습니다.
그러고 보니 숫자 즉 연도를 입력을 안하고 다른걸로 하며 안된다고 합니다.
이거 하는 방법 좀 가르쳐 주세요
관리자님
좋은 프로그램 제공해 주셔서 감사합니다
다름이 아니라 제작년도란에 일반적으로 설치 위치로 바꾸고 싶습니다.
제작년도는 명령어 year로 되어 있습니다.
그러고 보니 숫자 즉 연도를 입력을 안하고 다른걸로 하며 안된다고 합니다.
이거 하는 방법 좀 가르쳐 주세요
금형정보를 관리하는 프로그램은 데이터베이스를 이용합니다. 데이터베이스에 데이터를 입력하게 되는데 숫자, 문자, 날짜등 데이터타입이 정해져 있습니다. 숫자필드에 문자를 넣으면 에러가 납니다. 필요하시면 문자필드를 새로 만들어서 문자가 들어가도록 프로그래밍을 하셔야 합니다.
감사합니다.
안녕하세요
좋은 프로그램 활용해보렸는데 컴파일 오류입니다 : 프로젝트나 라이브러리를 찾을 수 없습니다.
라는 오류가 나오네요 어떻게 해결해야 할까요?
안녕하세요. 답변이 많이 늦었습니다.
파일을 연 상태에서 Alt+F11 누른 다음 다음 순서로 해 보세요.
1. Visual Basic Editor 화면에서 도구 > 참조 메뉴 선택
2. 사용 가능한 참조 에서 "누락" 으로 표시된 항목 앞의 체크 표시를 해제
위와 같이 해도 안되면 아마도 엑셀 또는 Office가 잘못설치되었거나 Windows업데이트가 최신이 아니라서 그럴 수 있습니다.
Windows업데이트를 최신으로 해 보시고, 그래도 안되면 Office를 삭제 후 새로 설치해 보시기 바랍니다.
감사합니다.
테스트중입니다. 저장후에 껐다 키고 다시 저장을 누르면 오류가 뜨면서 더이상 저장이 안되네요.
멍청해서 쓰지도못해ㅠ
ㅁㅁ
올린 댓글이 왜안보이는걸까요ㅠㅠ
좋은프로그램 잘 활용해보려는데 잘안되네요
검색 종목이 5개가 아니라 7개를 이용하려고하는데 따로 ODBC? ACCESS DB?에서 테이블을 만들어줘야하나요?
현재 검색구조 : 금형이름 거래처명 제작년도 금형제조처 제품명 ..........................
저는 코드상에서 Integer기준으로 변수 선정하고 다 마춰서한거 같은데... sQL문 만들 조회조건도 Integer기준 Year이랑 똑같이하고 ㅠㅠ
안녕하세요. 금형관리 프로그램은 ACCESS DB를 이용하지 않고 대신 엑셀의 시트를 테이블로 이용하는 방식입니다. 검색 필드가 늘어나면 "금형DATA"시트에 칼럼을 추가하면 됩니다.
감사합니다.
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.[Area] 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 [몇층],[Area],[위치],[전압],[전류],[판넬],[장비],[아이디],[아이피]" & _
" 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("Area")
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
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), CInt(Cells(4, 5)), CInt(Cells(6, 5))
End Sub
Sub getMoldInfo( _
argMold As String, _
argCustomer As String, _
argYear As Integer, _
argMaker As String, _
argProduct As String, _
argX As Integer, _
argY As Integer)
' 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
Dim vSQLX As String
Dim vSQLY 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.[Area] 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
If argX = 0 Then
vSQLX = ""
Else
vSQLX = " AND m.[가로] = " & argX
End If
If argY = 0 Then
vSQLY = ""
Else
vSQLY = " AND m.[세로] = " & argY
End If
vSQL = "SELECT [몇층],[Area],[부하율],[전압],[전류],[판넬],[장비],[아이디],[아이피],[가로],[세로]" & _
" FROM [DATA$] AS m" & _
" WHERE m.[몇층] > '' " & _
vSQLmold & vSQLcustomer & vSQLyear & vSQLmaker & vSQLproduct & vSQLX & vSQLY
'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("Area")
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("아이피")
Cells(START_ROW + i, 12) = rs("가로")
Cells(START_ROW + i, 13) = 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
--------------------------------------------------위에 코드는 잘못 올렸습니다.---------------------------------------
안녕하세요?
인터넷에서 매크로를 사용하는 방법을 찾다가
좋은 자료를 발견하게 되어서 참조해보고 있습니다.
아직 걸음마 단계이기 때문이라 그런지 처음부터 많이 헤매고 있습니다.
그래서 2가지 정도의 질문을 하려고 합니다
첫째는
항목의 이름을 바꾸어서 사용하려면 DB와 MODULE1 코드 항목 명 외에 바꾸어야 할 파트가 있는지 궁금합니다.
둘째는
여러가지 항목이 중복되는 아이템의 경우 어떤 추가적인 명령어를 입력해서 검색하는데 원활하게
사용할 수 있을지 궁금합니다
그럼 이만 글을 마치며 새해복 많이 받으세요
1. 항목의 이름을 바꾸는 것은 database의 필드명을 바꾸는 것입니다. 모든 프로그램 영역에 영향을 주게 됩니다. form,DB,Module전부 확인해서 고쳐야 합니다.
2. 중복된 항목을 원활하게 검색한다는 것으 어떤 의미인가요?
감사합니다.
안녕하세요
해당 파일 항목 추가 중 오류 발생해서 질문드립니다.
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구조 : 관리번호 장비명 기기분야 등급 기기번호(S/N) 규격/분해능 구입일 교정주기 검교정일 성적서번호 교정기관 차기검교정일 사용부서 제작사 상태 조치 비고
'기존 검색내용 지우기
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.[기기번호(S/N)] LIKE '%" & Replace(argProduct, "'", "''") & "%'"
End If
vSQL = "SELECT [관리번호],[장비명],[기기분야],[등급],[기기번호(S/N)],[규격/분해능],[구입일],[교정주기],[검교정일],[성적서번호],[교정기관],[차기검교정일],[사용부서],[제작사],[상태],[조치],[비고]" & _
" 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("기기번호(S/N)")
Cells(START_ROW + i, 8) = rs("규격/분해능")
Cells(START_ROW + i, 9) = rs("구입일")
Cells(START_ROW + i, 10) = rs("교정주기")
Cells(START_ROW + i, 11) = rs("검교정일")
Cells(START_ROW + i, 12) = rs("성적서번호")
Cells(START_ROW + i, 13) = rs("교정기관")
Cells(START_ROW + i, 14) = rs("차기검교정일")
Cells(START_ROW + i, 15) = rs("사용부서")
Cells(START_ROW + i, 16) = rs("제작사")
Cells(START_ROW + i, 17) = rs("상태")
Cells(START_ROW + i, 18) = rs("조치")
Cells(START_ROW + i, 19) = 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
이렇게 항목 추가 후
필요한 매개 변수 중 한 개 이상 값이 주어지지 않았습니다. 라고
오류가 뜨는데.. 무엇이 문제일까요?ㅠㅠ
해결되었습니다.
혹시 하이퍼링크 그림파일을 -> 엑셀파일로 변경하려고 하는데 가능한지요?
금형DATA시트에 "금형사진\A001_우측.jpg"와 같이 입력되어 있는 부분을 다음의 예처럼 엑셀파일이 있는 경로를 입력하면 됩니다.
C:\temp\aaa.xlsx
안녕하세요, 자료감사합니다.
금형DATA시트를 유형1DATA, 유형2DATA와 같이 두개의 시트로 관리하고 싶은데,
조회하기 결과가 유형1DATA, 유형2DATA의 데이터가 모두 나열되게하려면
아래 구문의 FROM에서 2개의 시트를 데이터베이스로 입력받을수 있는 방법이 있나요?
vSQL = "SELECT [금형이름],[거래처명],[제작년도],[금형제조처],[제품명],[우측사진],[좌측사진],[상판사진],[하판사진]" & _
" FROM [금형DATA$] AS m" & _
" WHERE m.[금형이름] > '' " & _
vSQLmold & vSQLcustomer & vSQLyear & vSQLmaker & vSQLproduct
2개의 시트를 입력못받는다면, 2개의 시트를 함께 조회하는 다른방법은 없을까요?
다음과 같이 union all을 사용하면 됩니다.
vSQL = "SELECT [금형이름],[거래처명],[제작년도],[금형제조처],[제품명],[우측사진],[좌측사진],[상판사진],[하판사진]" & _
" FROM (select * from [유형1DATA$] union all select * from [유형2DATA$]) AS m" & _
" WHERE m.[금형이름] > '' " & _
vSQLmold & vSQLcustomer & vSQLyear & vSQLmaker & vSQLproduct
해결하였습니다,
답변 정말 감사합니다!!
안녕하세요. 올려주신 샘플 잘 사용하고 있습니다. 감사합니다.
데이터 테이블에서 수식을 사용하여 적용된 숫자 예를들어 672.090909 인데, 셀서식 설정으로 672만 보이게 하고있는데요.
검색으로 출력을 해보면
672.090909로 다 표기가 되고 셀에 오류표시가 뜨네요. 셀서식을 숫자로해도 바뀌지 않고요.
셀의 오류메시지 선택하여 숫자로 변환하고 다시 셀서식을 변경하면 제대로 보이긴하는데 이걸 처음부터 672로 보이게 할수있는 방법이 있을까요?
조언 부탁드립니다.
안녕하세요. 입력된 값이 672.090909 이면 셀서식 설정을 바꿔도 내부적으로 저장되는 값은 여전히 672.090909 입니다. 셀서식 설정은 내부의 값을 바꾸는 것이 아니라 단지 표시되는 값을 다르게 표시할 뿐입니다. 처음부터 672로 보이게 하려면 데이터테이블의 값을 672로 정수형태로 입력하시고 저장하셔야 합니다.
초기화면에 세로검색 항목 거래처명 갯수가 10개이상 검색 단추에 3개이상 넣을려면 어떻게 해야 하나요?
삼성 현대 기아 쌍용 아우디 벤츠 등등 검색 단추를 클릭하면 볼수있으면 좋겠는데 현재 3개이상은 검색이 안되네요 도와주시면 감사하겠습니다.
안녕하세요. 검색단추는 엑셀의 데이터유효성검사 기능을 이용했는데요. 다음과 같이 갯수를 늘리면 됩니다.
1)"기초DATA" 시트의 A열에 원하는 만큼 항목추가, 쌍용자동차, 벤츠 등 추가
2)"검색하기" 시트에서 거래처 검색(C6셀)을 마우스로 선택 > 리본메뉴 데이터 탭 > 데이터유효성검사 버튼 클릭 > 데이터 유효성 팝업에서 설정탭 선택 > 원본필드 오른쪽에 있는 버튼(위쪽 화살표모양) 클릭하면 "기초DATA"시트로 이동됨, 이상태에서 A열에 입력한 값목록을 마우스로 선택한 후 Enter > 확인 버튼 클릭
이제 검색버튼에 추가한 값이 보일겁니다.
감사합니다.
안녕하세요. 좋은 프로그램 만들어 주셔서 감사합니다.
저의 경우 사진을 보려는 용도가 아니라서 하이퍼링크는 필요가 없습니다.
하이퍼링크(글자색 바뀌는)만 삭제할 수 있을지요?
getMoldInfo 프로시저에서 하이퍼링크 넣는 부분을 주석처리하면 됩니다(총 4군데 입니다).
Cells(START_ROW + i, 8).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 8), Address:=rs("우측사진")
Cells(START_ROW + i, 9).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 9), Address:=rs("좌측사진")
Cells(START_ROW + i, 10).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 10), Address:=rs("상판사진")
Cells(START_ROW + i, 11).Hyperlinks.Add Anchor:=Cells(START_ROW + i, 11), Address:=rs("하판사진")
안녕하세요. 좋은 자료 감사합니다.금형에 대한 생산 수량 항목을 넣었는데 숫자에 대한 천단위별 콤마표시하는 방법을 몰라서요.
도움좀 부탁드립니다 ㅠ
안녕하세요. 셀에 값을 표시하는 것이므로 셀 서식을 지정하시면 됩니다.
숫자가 출력되는 셀을 선택 → 리본메뉴 [홈] 탭 → [표시 형식] 그룹 → 쉼표 모양 버튼 클릭
자세한 내용은 다음 글을 참고하세요.
https://xlworks.net/excel-tutorial-intermediate-format-cells/
또는 DB에서 가져올 때 처리하는 방법도 있습니다.
가져오는 필드명이 '수량'이라고 했을 때
Cells(START_ROW + i, 12) = Format(rs("수량"), "#,##0")
하이퍼링크 클릭시 파일을 찾을 수 없다고 합니다.
경로는 제대로 연결하였는데요.
DATA에서는 제대로 열리는데, 조회시 URL를 클릭하면 열리지 않는다고 하네요.
도와주세요ㅠㅠ
DATA탭 하이퍼링크 URL이 C:\temp\aaa.jpg 라면
조회탭에서는 하이퍼링크 주소가 aaa.jpg 로 나오네요ㅠㅠ
DATA탭에 있는 주소를 그대로 조회탭에 가져오는데요, 다르게 표시된다니 이상하네요. 파일을 admin@xlworks.net으로 보내주시면 확인하고 답변드리겠습니다.
검색조건에 , (콤마)를 기준으로 포문 돌릴 수 있나요???
금형이름 : A001, A002, A003
이런식으로 조건을 ,(콤마)로 검색조건을 여러건을 주고 싶어요~
FOR문을 돌려서 SQL이 다음과 같이 만들어지면 됩니다.
SQL WHERE 절 :
WHERE [금형이름] IN ('A001','A002','A003')