VBA로 엑셀을DB로? 우주에서 제일 쉽게 알려줄께요.(ADODB)

Рет қаралды 56,202

엑사남ExcelLoveMan-

Күн бұрын

Пікірлер: 94

@marine-gift 3 жыл бұрын

정말 너무너무 좋은 강의네요 ,감사 드립니다^^

@크리스천-v3o 3 жыл бұрын

잘보고갑니다^^많은도움받고있습니다 추가적으로 다른 파일에 가져오고 일부항목만 가져오는 vba강의도 추가적으로 있으면 더더 좋을거같아요^^

@kpro21c 2 жыл бұрын

최고의 강사 십니다. 가장 쉽게, 가장 체계적으로, 정말 대단하신 분, 너무 고맙습니다!!

@insungpark4951 2 жыл бұрын

필터와 함수만 썼었는데 새로운 개념이라 새롭네요 ~ :)

@ContacT_M 3 жыл бұрын

보통 DB를 사용해서 프로그램을 구성할때 DB 속에 여러 테이블을 연동해서 사용해야 유효성이 높아 질수 있다고 생각됩니다. 하시는김에 SQL이나 MYSQL, 엑세스혹은 엑셀 파일이나 텍스트 파일로 여러 테이블을 연동해서 사용하는 강의를 연재 해주신다면 엑셀 활용에 큰 도움이 될것 같습니다.

@excelloveman-3399 3 жыл бұрын

네 의견 감사합니다 엑세스 연동은 계획에 있고요 웹호스팅 mssql 연동한 것도 준비 예정입니다 다만 초보분들 입문 강의다보니 비즈니스적인 큰 데이터는 어렵고 한두개 테이블로 진행 할 예정입니다

@ContacT_M 3 жыл бұрын

감사합니다.

@한국수달보호협회장 3 жыл бұрын

회사에서 저런 데이터 거르는 매크로 만들었는데 몇천행 만드는데 1분걸리더라구요. SQL은 수십만개가 1초도안걸리네요 꼭 배워야겠습니다

@R2N2R 4 жыл бұрын

너무 잘 보고 있습니다. 나중에 필터에 대한 강의도 꼭 좀 부탁드려요. 회사에서 VBA에로 코딩을 하고 있는데요. 매크로 기록으로 안되는 것중 하나가 필터 된 열에서 바로 아래칸 선택하는 것이더라구요... B1 열 필터 걸었을때 우리 눈에는 B1 바로 아래 B3 이 나왔는데 필터 된 셀을 Select 하는 방법이나 혹은 필터된 Range 선택하고 복사 붙여넣기 하는 방법이 너무 궁금합니다.

@excelloveman-3399 4 жыл бұрын

네 필터 강의는 이미 있습니다 원하시는 내용은 없을수도 있습니다 카카오 오픈 채팅방에 강의관련 문의 하시면 여러 좋은 답변 받으실 수 있을거에요 공지 꼭 읽어보시고 도움되시길 바랍니다

@jjskim3387 3 жыл бұрын

따봉입니다. 좋은거 배워갑니다

@excelloveman-3399 3 жыл бұрын

감사합니다

@hyunas6384 3 жыл бұрын

강사님의 친절한 설명에 엑셀이 재밌네요 감사합니다 그 path설정할때 해당 시트가 아니고 다른폴더의 시트를 활용할려면 경로를 어떻게 지정하나요?

@excelloveman-3399 3 жыл бұрын

안녕하세요 강의 관련 질문은 아래 주소의 오픈채팅방 이용 부탁드립니다. 파일이나 캡쳐본으로 서로 전달해야 빠른 풀이 및 이해가 가능합니드 제 강의를 듣는 구독자 분들이 VBA 학습을 위해 만든 방입니다. 입장 후 인사와 공지 준수는 필수 입니다^^ 학습 하시는데 많은 도움이 되실거에요. 유튜브 '엑사남'의 Excel VBA 함께하기 open.kakao.com/o/glXWEB3b

@임선묵-s7z 2 жыл бұрын

염치 없지만 질문드립니다. 다른 폴더또는 다른 드라이브에 있는 경우에는 이런식으로 하면 에러가 발생하네요 다른 폴더에 있는 파일을 참고 할때는 어떻게 해야 하는 거죠?? OR_File_Name = "데이터" OR_Path = "E:\Download\" 경로 = OR_Path & OR_File_Name & ".xlsx" OLEDB데이터문자열 = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & 경로 & ";" & _ "Extended Properties=""Excel 12.0 xml;HDR=YES"";" 연결.Open OLEDB데이터문자열 두 파일이 다른 폴더에 존재할 경우는 가장 아래 줄에서 에러가 발생을 합니다.

@excelloveman-3399 2 жыл бұрын

안녕하세요 강의 관련 질문은 아래 주소의 오픈채팅방 이용 부탁드립니다. 파일이나 캡쳐본으로 서로 전달해야 빠른 답변이 가능합니다. 제 강의를 듣는 구독자 분들이 VBA 학습을 위해 만든 방입니다. 입장 후 인사와 공지 준수는 필수 입니다^^ 학습 하시는데 많은 도움이 되실거에요. 유튜브 '엑사남'의 Excel VBA 함께하기 open.kakao.com/o/glXWEB3b

@빅데이타-l4s 4 жыл бұрын

안녕하세요. 강의 감사합니다. 간만에 SQL문을 만나니 감회가 새롭군요. 신입때 연습한다고 DELETE문 잘못 썼다가 데이타 날리는 바람에 엄청 곤욕을 치뤘던 기억이 납니다. 20년전쯤 일이지만요 ^^ ^^ 합격이 4만5천개정도인데 2초정도 걸리고 불합격이 15만5천갠데 7초정도 걸리네요. 이 정도면 정상인건가요? 그리구요 한 시트에 테이블이 1개만 있어야 되는 건가요? Dim Rs As New ADODB.Recordset Dim strPath As String Dim strSQL As String Dim StrConn As String Dim lngF As Long Dim lngJ As Long strPath = ThisWorkbook.FullName '''' 커넥션 스트링 ''' StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strPath & ";" & _ "Extended Properties=Excel 12.0;" strSQL = "SELECT 성명,평균점수 FROM [Table_1$] WHERE 합격여부='합격'" '''' DB SQL문을 사용합니다. Rs.Open strSQL, StrConn If Rs.EOF Then MsgBox "조건에 맞는 데이터가 없습니다." Else Range("F11").CurrentRegion.Offset(1).Clear Range("F11").CopyFromRecordset Rs End If Rs.Close Set Rs = Nothing strSQL = "SELECT 성명,평균점수 FROM [Table_1$] WHERE 합격여부='불합격'" '''' DB SQL문을 사용합니다. Rs.Open strSQL, StrConn If Rs.EOF Then MsgBox "조건에 맞는 데이터가 없습니다." Else Range("J11").CurrentRegion.Offset(1).Clear Range("J11").CopyFromRecordset Rs End If Rs.Close Set Rs = Nothing lngF = Range("F" & Rows.Count).End(xlUp).Row '합격 마지막 행 lngJ = Range("J" & Rows.Count).End(xlUp).Row '불합격 마지막 행 Range("F3:G3").Copy If lngF > 10 Then Range("F11:G" & lngF).PasteSpecial xlPasteFormats If lngJ > 10 Then Range("J11:K" & lngJ).PasteSpecial xlPasteFormats Application.CutCopyMode = False

@excelloveman-3399 4 жыл бұрын

네 엑셀에 뿌리는 시간이 있어서 시간은 적당한것 같고요 다음 강의에서 범위활용에 대해서 다루려고 합니다 감사합니다

@kslee5402 4 жыл бұрын

좋은 강의 우연히 발견했는데 꽤 유용할 것 같습니다. (11분29초에서 잠시 멈추고 마음을 가다듬고 다시 보겠습니다^^ ) 그전에 잠깐 질문 좀 드려도 될까요? 제가 코로나 환자를 공식적으로 다루고 있습니다. 질병관리본부에서 다운받은 엑셀파일에는 1만명이 조금 안되는 환자의 인적사항이 있는데 용량이 4메가 정도 됩니다. 그 데이터를 1번 시트에 매번 업데이트를 합니다. (이 강의로 치면 DB, 저는 로우데이터의 개념으로 활용) 2번 시트에는 이름과 생년월일을 입력하면 1번 시트에 존재하는 환자의 주민번호, 주소, 진단일자 등... 필요한 내용이 index,match 함수로 불러와집니다. 그리고 그 환자에 대한 처리결과(?)가 정해지면 날짜, 결과(자가격리, 입원 등), 결과상세(이송병원명) 등... 을 입력하게 됩니다. 3번 시트에는 피벗테이블이 있고 슬라이서 버튼과 VBA 실행 버튼이 있습니다. 슬라이서 버튼으로 원하는 날짜, 원하는 병원을 클릭하면 특정 날짜에 특정 병원으로 입원할 대상자만 표현이 됩니다. 그 다음 VBA 명령 버튼을 누르면 다른 중요한 내용은 모두 남겨두고 특정 병원에서 받아야 할 특정 날짜의 환자 명단만 별도의 빈 엑셀 파일에 값으로 붙여넣기 한 상태로, 원하는 파일명으로 저장되도록 구현을 했습니다. 위 과정을 올해 2월 부터 수개월 동안 다듬고 다듬어서 만든 엑셀파일이 이제 너무 무거워져서 작동을 할 수 없는 지경이 되었습니다. 파일 용량은 6메가 정도 됩니다. 조건부 서식과 index 함수가 무거워서 누적해서 쌓인 곳은 모두 적용을 해제하고, 최근 명단에만 적용했는데도 불구하고 굉장히 무겁습니다. 1번 시트에 해당되는 질병관리본부에서 다운받은 그 데이터도, 하루에 몇번씩 다운받고 복사해서 붙여넣어야 하는 엑셀파일 입니다. 그 엑셀파일이 너무 무거워 별도로 두고 연결을 하려고 했는데 이 강의를 찾게 되어 무척 반갑습니다 이제 질문입니다. 1번 시트라고 지칭한 그 파일을 다운받을 때 마다 새로운 파일이 생성됩니다. 파일명은 항상 동일해서 OOO(1).xlsx OOO(2).xlsx 이런식으로 다운 받을 때 마다 (숫자)가 붙게 됩니다. 이런 상황에서 그 파일을 열어 복사하고 붙여넣기 과정 없이 그대로 두고 DB로 활용하는 것도 가능할까요?

@excelloveman-3399 4 жыл бұрын

6메가 정도의 엑셀이면 무거우면 안되는 용량입니다 아마도 쓸데없는 용량이 차지하는 부분인거 같습니다 그리고 질문하신 내용은 모든 엑셀의 표는 DB로 활용 가능합니다 단 가져올 곳에서 매크로를 동작하여야 합니다

@TheJaebeomPark 4 жыл бұрын

새로운 개념 잘 배우고 갑니다

@excelloveman-3399 4 жыл бұрын

넵 감사합니다

@소리소문-c1n 3 жыл бұрын

감사합니다

@뽀리너죠 4 жыл бұрын

안녕하세요 DB 강의 복습하고 있는데 시트 3개를 각각 DB로 가져올 수 있나요? 시트 1개로 칼럼을 조인하는거 까지는 테스트 해봤는데 3개도 가져올 수 있나 해서요 FROM [db1$] A, [db2$] B, [db3$] C 이런식으로 이너조인 하려고 했더니 DB를 못가져오는거 같아서 여러개 시트를 DB 조회 방법이 있나 질문드립니다.

@excelloveman-3399 4 жыл бұрын

곧 강의 올리겠습니다

@뽀리너죠 4 жыл бұрын

@@excelloveman-3399 아... 넵~! 다음 강의에 있는 내용이군요...

@ContacT_M Жыл бұрын

strSQL을 원하는 셀에서 데이터입력 했듯이 Rs를 한꺼번에 출력하지 않고 원하는 셀에 각각 출력하는 방법은 없을까요? 쿼리를 여러개 만들어야 할까요?

@excelloveman-3399 Жыл бұрын

안녕하세요 엑사남입니다^^ 강의 관련 질문은 아래 주소의 오픈채팅방 이용 부탁드립니다. 파일이나 캡쳐본으로 서로 전달해야 빠른 답변이 가능합니다. 제 강의를 듣는 구독자 분들이 VBA 학습을 위해 만든 방입니다. 입장 후 인사와 공지 준수는 필수 입니다^^ 학습 하시는데 많은 도움이 되실거에요. 유튜브 '엑사남'의 Excel VBA 함께하기 open.kakao.com/o/glXWEB3b

@ContacT_M Жыл бұрын

감사합니다.

@bigmood6421 4 жыл бұрын

좋은 정보 감사합니다. 그런데 약 100만행 160열정도의 데이터를 불러올때는 sql이 빠를까요 고급필터를 사용하는게 빠를까요?

@excelloveman-3399 4 жыл бұрын

엑셀로 가져온다고요? 두가지 방법 다 무리입니다... 가져오고 나서가 문제입니다 그것으로 뭘 할 수 있을까요?

@excelloveman-3399 4 жыл бұрын

100만행의 160열의 데이터를 엑셀에 뿌려놓는 이유가 뭔가요? 그렇게 큰 데이터는 DB 상에 raw data 로 있는게 맞습니다 엑셀을 가공 요약된 결과를 보여주기 위한 툴이며 팔요한 데이터만 추출해서 사용하는것이 맞습니다

@bigmood6421 4 жыл бұрын

@@excelloveman-3399 제가 지금 그 데이터를 불러와서 그래프를 출력시키려고하려하는데.. 코드를 사용해서 그래프출력은 어렵지않은데 데이터가워낙많다보니 불러오는 시간이 너무길더라고요. 데이터베이스를 구성하면 시트에옮기지않더라도 차트를 출력할수있나요?

@excelloveman-3399 4 жыл бұрын

100만행의 데이터를 전부 차트에 적용해야 하는거라면 엑셀로는 어렵습니다 파워bi 의 파워피벗 또는 대쉬보드 툴을 사용하셔야ㅜ할듯 합니다

@excelloveman-3399 4 жыл бұрын

그리고 그정도 데이터 양이라면 불러와서 저장하고 다른 가능을 사용하는것도 리소스 부족으로 쉽지 않을듯 합니다

@YEONGGILful 4 жыл бұрын

혹시, cell정보도 가져올수있을까요?? 예를들어, Range("B2")를 가져와 를 할수있을까요. 여러파일 여러시트의 똑같은 셀을 가져올때 해당 코드를 적용하고 싶어 질의드려봅니다.

@excelloveman-3399 4 жыл бұрын

안녕하세요. 구체적인 질문 설명 부탁드립니다.

@YEONGGILful 4 жыл бұрын

@@excelloveman-3399 여러파일 속 여러시트에 있는 자료를 취합할때, VBA를 통해 가능하긴하지만 파일들이 클 경우 시간이 엄청 소요되는 현상이 있어, DB를 불러오는 것처럼 적용해보고자 합니다. (파일을 열지않고 가져오는것 같아 속도가빠른것 같습니다.) 예를들어, workbooks("연습1").sheets("Test1").range("B2") workbooks("연습2").sheets("Test1").range("B2") workbooks("연습3").sheets("Test1").range("B2") 위 자료들을 "where 합격"

@excelloveman-3399 4 жыл бұрын

세개의 파일을 for 문으로 순환하면서 원하는 where 조건에 맞게 아래로 이어서 select 하면 되지 않을까요?

@YEONGGILful 4 жыл бұрын

@@excelloveman-3399 where 조건에 cells나 range도 선택 가능할까요??

@excelloveman-3399 4 жыл бұрын

"select ... ... from ... where 합격여부 '" & range("a1") & "'"

@뽀리너죠 4 жыл бұрын

재밌게 보고 갑니다~ DB개념을 정말 쉽게 잘 설명해 주시네요~ 다음 강의도 기대하겠습니다. Sub Data_Select() Dim Rs As New ADODB.Recordset Dim strPath As String Dim strSQL As String Dim strConn As String Dim i As Long Dim lngR1 As Long Dim lngR2 As Long strPath = ThisWorkbook.FullName strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strPath & ";" & _ "Extended Properties=Excel 12.0;" strSQL = "SELECT 성명, 평균점수 FROM [Table_1$] WHERE 합격여부='합격'" Rs.Open strSQL, strConn If Rs.EOF Then MsgBox "조건에 맞는 데이터가 없습니다." Rs.Close Set Rs = Nothing Exit Sub Else Range("F11").CurrentRegion.Offset(1).Clear Range("F11").CopyFromRecordset Rs End If Rs.Close Set Rs = Nothing strSQL = "SELECT 성명, 평균점수 FROM [Table_1$] WHERE 합격여부='불합격'" Rs.Open strSQL, strConn If Rs.EOF Then MsgBox "조건에 맞는 데이터가 없습니다." Rs.Close Set Rs = Nothing Exit Sub Else Range("J11").CurrentRegion.Offset(1).Clear Range("J11").CopyFromRecordset Rs End If Rs.Close Set Rs = Nothing lngR1 = Cells(Rows.Count, "F").End(xlUp).Row lngR2 = Cells(Rows.Count, "J").End(xlUp).Row Range("F3:G3").Copy Range("F11:G" & lngR1).PasteSpecial xlPasteFormats Range("J3:K3").Copy Range("J11:K" & lngR2).PasteSpecial xlPasteFormats End Sub

@excelloveman-3399 4 жыл бұрын

네네 감사합니다 ㅎㅎ

@뽀리너죠 4 жыл бұрын

질문있습니다. 칼럼명을 Select로 조회할때 행의 번호는 상관없이 열에서 첫번째 성명, 평균점수가 있는 값을 자동으로 찾는건가요? 따로 지정을 안했는데 값을 가지고 오는게 신기하네요.

@excelloveman-3399 4 жыл бұрын

컬럼명 가져오는 구문은 없을건데요?

@뽀리너죠 4 жыл бұрын

이번 예제 시트에서 table_1에 있는 데이터 성명, 컴퓨터일반, 스프레드시트, 평균점수, 합격여부 행들이 1열에만 있어야 sql구문으로 불러올 수 있나요? 행 또는 열 칸을 띄운다던지 했을때 성명 합격여부 값들을 자동으로 인식하고 가져올 수 있는지 여쭤보았습니다

@excelloveman-3399 4 жыл бұрын

필드 제목을 가져오는 구문이 따로 있습니다 다음 강의에서 다루겠습니다

@선군-r3i 3 жыл бұрын

"CopyFromRecordset" DB를 가로 방향이 아닌 세로 방향으로 받을수 있는 가요??? .

@그리드소프트 4 жыл бұрын

제가 했던건 소꿉장난이였군요. 감사합니다

@excelloveman-3399 4 жыл бұрын

그 소꿉장난도 모르는 분들이 많죠 응원합니다

@bi4662 4 жыл бұрын

Rs open strsql strconn에서 매개변수설정이 다 안되었다고 디버그가 생기는 이유는 뭘까요?

@excelloveman-3399 4 жыл бұрын

rs.open . 이 없는거 같아요 그리고 강의 관련 질문은 아래 주소의 오픈채팅방 이용 부탁드립니다. 제 강의를 듣는 구독자 분들이 VBA 학습을 위해 만든 방입니다. 입장 후 인사와 공지 준수는 필수 입니다^^ 학습 하시는데 많은 도움이 되실거에요. 유튜브 '엑사남'의 Excel VBA 함께하기 open.kakao.com/o/glXWEB3b

@conancho5680 2 жыл бұрын

엑사남님 엑셀에서 관계형 데이타베이스를 만들어서 사용할 수 있어요??

@excelloveman-3399 2 жыл бұрын

엑셀로는 제약이 많죠 엑세스 연동시 가능합니다 안녕하세요 강의 관련 질문은 아래 주소의 오픈채팅방 이용 부탁드립니다. 파일이나 캡쳐본으로 서로 전달해야 빠른 답변이 가능합니다. 제 강의를 듣는 구독자 분들이 VBA 학습을 위해 만든 방입니다. 입장 후 인사와 공지 준수는 필수 입니다^^ 학습 하시는데 많은 도움이 되실거에요. 유튜브 '엑사남'의 Excel VBA 함께하기 open.kakao.com/o/glXWEB3b

@conancho5680 2 жыл бұрын

@@excelloveman-3399 알려주신대로 오픈채팅방 가봤는데...대화들이 없네요. 혹시 특별한 절차가 있을가요? 닉은 변경 했습니다

@excelloveman-3399 2 жыл бұрын

주말이라 그럴겁니다. 문의 있으시면 남겨보세요.

@타일러-w4l 4 жыл бұрын

진짜 너무 쉽게 설명해주시네 너무해

@excelloveman-3399 4 жыл бұрын

감사합니다

@inggoyd 3 жыл бұрын

좋은 강의 감사합니다~ ^^

@ContacT_M 3 жыл бұрын

☆

@excelloveman-3399 3 жыл бұрын

😍

@MrKang846 3 жыл бұрын

알려주시대로 잘되다가 엑셀종료후 다시 해보는데, Rs.open에서 에러가 발생합니다.(연결된 microsoft Excel 워크시트를 보기위한 연결이 손실되었습니다.) Sub GETDB() Dim Rs As New ADODB.Recordset Dim strPath As String Dim strSQL As String Dim strConn As String Dim i As Long strPath = ThisWorkbook.FullName ''커넥션 스트링''' strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strPath & ";" & _ "Extended Properties=Excel 12.0;" strSQL = "SELECT * FROM[운임(FCL)$] WHERE POD = 'JEBEL ALI'" Rs.Open strSQL, strConn If Rs.EOF Then MsgBox "조건에 맞는 데이터가 없습니다." Rs.Close Set Rs = Nothing Exit Sub Else Range("a1").CurrentRegion.Offset(1).Clear Range("a1").CopyFromRecordset Rs End If Rs.Close Set Rs = Nothing End Sub

@excelloveman-3399 3 жыл бұрын

@tvletsplaywithsoy4178 4 жыл бұрын

5% 의 미완입니다. 이크ㅜ.,ㅡ

@지봉성-h6h 3 жыл бұрын

VBA어디로 들어가요?

@jkevin1569 4 жыл бұрын

캬 멋져부러요

@excelloveman-3399 4 жыл бұрын

감사합니다

@hojae76lee 3 жыл бұрын

알거 같으면서 모르겠고...잡힐 듯 말 듯...

@excelloveman-3399 3 жыл бұрын

1상 기초 기본부터 다 보신 분들에게 쉬운 내용이에요 ^^

@hojae76lee 3 жыл бұрын

@@excelloveman-3399 기초 기본 부터 보러 가겠습니다~~

@sondongyoun 4 жыл бұрын

채널을 응원하며 과제물을 제출 합니다. Sub 과제물() Dim str합격 As String Dim str불합격 As String str합격 = "합격" str불합격 = "불합격" Call Data_Select(str합격, Range("F10")) Call Data_Select(str불합격, Range("J10")) End Sub Public Sub Data_Select(str As String, rngT As Range) Dim Rs As New ADODB.Recordset Dim strPath As String Dim strSQL As String Dim strConn As String Dim i As Long strPath = ThisWorkbook.FullName '''' 커넥션 스트링 ''' strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strPath & ";" & _ "Extended Properties=Excel 12.0;" strSQL = "SELECT 성명, 평균점수 FROM [Table_1$] WHERE 합격여부='" & str & "'"'''' DB SQL문을 사용합니다. Rs.Open strSQL, strConn If Rs.EOF Then MsgBox "조건에 맞는 데이터가 없습니다." Rs.Close Set Rs = Nothing Exit Sub Else rngT.CurrentRegion.Offset(1).Clear rngT.CopyFromRecordset Rs End If Rs.Close Set Rs = Nothing End Sub

@excelloveman-3399 4 жыл бұрын

코더시군요 ㅎㅎ 귯!!!

@sondongyoun 4 жыл бұрын

@@excelloveman-3399 채널을 보기 전에는 VBA 기록기만 사용하였는데.... 채널 보고 나서 많이 늘었어요. 감사합니다.

@excelloveman-3399 4 жыл бұрын

봐주시고 해주셔서 감사합니다