안녕하세요 지금 ADODB 를 이용해서 오픈 된 파일의 데이터를 불러 오는데서 막혀서 도움 요청 드립니다

VBA
작성자
밍장군
작성일
2020-09-14 17:49
조회
40
일단 여기 "오빠두엑셀" 님이 말씀하신 대로 예제를 이용해서 연결 여부를 확인하는 예제를 실행해보니

Sub test()
Dim cnt As ADODB.Connection
Dim OLEDB As String
Dim path As String
Dim DB As String

path = thisworkbook.path & "\"
DB = "a.xls"
Debug.Print path & DB
'1st)OLEDB = "Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" & path & DB & ";extended properties=""excel 8.0;HDR=YES;IMEX=1;';"""
'2nd)OLEDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & DB & ";extended properties=""excel 12.0 xmlHDR=YES"";"

Set cnt = New ADODB.Connection
cnt.Open OLEDB

If cnt.State = adStateOpen Then
MsgBox "connected"
Else
MsgBox "not conned"
End If
cnt.Close
Set cnt = Nothing

End Sub

1st case 에서는 공급자를 찾을 수 없습니다 올바르게 설치 되지 않았을 수 있습니다

2nd case 에서는 설치가능한 ISAM 을 찾을 수 없습니다 라는 에러 메세지가 뜹니다

라는 오류 메세지가 뜹니다

제가 사용 하는 pc 는 윈10 64비트 이며 activeX ADO 6.2 라이브러리 참조 메뉴에서 추가했습니다

 

해서 여기저기 뒤지다가

Sub extract_All_Gap11()
'------------------------------------------------------------
' VBE 도구(Tools) => 참조(References)에
' Microsoft ActiveX Data Object Library 2.8 또는 6.0 참조후
'------------------------------------------------------------
Dim strSQL As String 'SQL 조건을 넣을 변수
Dim strConn As String 'Connection 넣을 변수
Dim adoRS As ADODB.Recordset 'ADO Recordset 넣을 변수
Dim i As Integer '반복 구문에 사용할 변수
Application.ScreenUpdating = False '화면 업데이트 (일시) 정지
Dim path As String

strSQL = "SELECT * " & _
"FROM [Sheet1$] " & _
"WHERE 이름 = '갑' ;" 'SQL 조건을 변수에 넣음

strConn = "Provider= Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= " & ThisWorkbook.FullName & ";" & _
"Extended Properties= Excel 12.0;" '연결 조건을 변수에 넣음

Set adoRS = New ADODB.Recordset 'Early binding으로 recordset을 변수에

adoRS.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
'SQL 조건에 따라 연결하여 recordset 열기
If Not adoRS.EOF Then '파일의 마지막이 아니라 데이터가 존재하면
With Sheets("Sheet2") 'Sheet2 시트에서
.Range("A1").CurrentRegion.Clear '기존 데이터 삭제

Sheets(1).Rows(1).SpecialCells(2).Copy .Cells(1, 1) '제목 복사

.Range("A2").CopyFromRecordset adoRS '데이터 복사
.Activate '활성화
End With
Else
MsgBox "자료가 없습니다", 64, "데이터 오류" '메시지 출력
End If

adoRS.Close 'recordset 닫음

Set adoRS = Nothing '개체변수 초기화(메모리 비우기)
End Sub

 

이걸 찾았는데 동작은 하더라구요

해서 아래와 같이 2가지 질문 좀 드릴께요
  1. 1번째 코드 실행 시 에러메세지 원인이 무엇인가요? 1번 에러 2번 에러 (첨부파일의 시트3 가 a.xls 파일이 네트워크 상에 저장되어 있습니다)
  2. 2번째 코드 실행 시 경로를 넣으려는데 예를 들어 첨부파일에서 3번 시트가 네트워크상의 임의 폴더에 저장되어 있다면 시트이름과하고 2번 행의 데이터가 where 변수 에 들어가야 할 거 같은데 어떻게 수정해야 할까요
이 수수께끼를 풀어야 하는데 고민한지 5일이 되어 갑니다
밍장군
회원등급 : 가지1단계
포인트 : 325 EP
전체 2

  • 2020-09-15 10:44

    안녕하세요.

    1. Extended Property 에 큰따옴표와 작은따옴표가 잘못사용되어 있네요. Extended Property는 작은따옴표로 묶어주셔야합니다.

    "extended properties='excel 12.0 xmlHDR=YES';"

    2. 2번째 행 데이터를 가져오시려면 레코드셋에 ID 필드가 추가되어야 합니다 .(순번이 입력된 필드)

    그렇지 않을 경우 가능한 방법은 adoRS.MoveNext 와 For문을 사용하여 For 변수가 2일 때 값을 출력하시는 방법이 있습니다.

    데이터가 동작속도는 ID 필드를 추가하여 작성하는 것이 월등히 빠르게 동작합니다.

    제시해드린 답변이 도움이 되셨길 바랍니다.


    • 2020-09-16 16:23

      답변 감사합니다

       


    전체 1,171
    번호 카테고리제목작성자작성일추천조회
    1167 차트/그래프
    New 동적범위를 이용한 날짜 합계 구하는 함수 문의 (1)
    째쨈 | 00:40 | 추천 0 | 조회 10
    째쨈00:40010
    1166 피벗테이블
    New 대량의 두개의 데이터를 합친후 데이터수정 및 합계결과 뽑아내는법이 궁금합니다 (1)
    자유인 | 00:35 | 추천 0 | 조회 13
    자유인00:35013
    1165 VBA
    New VBA 여러개 시트를 활용한 피벗데이블 질문 첨부파일 (1)
    sundaychefchef | 2020.09.23 | 추천 0 | 조회 18
    sundaychefchef2020.09.23018
    1164 VBA
    New if문의 조건에 대해 질문드립니다. (1)
    엑셀늅늅이 | 2020.09.23 | 추천 0 | 조회 16
    엑셀늅늅이2020.09.23016
    1163 함수/공식
    New 엑셀 배열을 합칠 수 있는 방법이 있을까요? (2)
    나그네 | 2020.09.23 | 추천 0 | 조회 21
    나그네2020.09.23021
    1162 함수/공식
    New 경우의 수 관련 질문드립니다. (3)
    speedoo | 2020.09.23 | 추천 0 | 조회 16
    speedoo2020.09.23016
    1161 함수/공식
    New 데이터 뽑기 첨부파일 (1)
    soung**** | 2020.09.23 | 추천 0 | 조회 24
    soung****2020.09.23024
    1160 VBA
    New 기초적인 질문 하나만 할께요 2차원 배열에서 range값을 배열로 받아서 다른 시트에 배열 붙여넣는 방법이 어떻게 되나요 (1)
    밍장군 | 2020.09.23 | 추천 0 | 조회 12
    밍장군2020.09.23012
    1159 VBA
    New (VBA) 도형서식 -> 채우기 -> 그림 또는 질감 채우기 -> 다음에서 그림 삽입 -> 클립보드 선택 구현 (1)
    건주 | 2020.09.23 | 추천 0 | 조회 22
    건주2020.09.23022
    1158 VBA
    New 엑셀이서도 검색어 자동완성기능을 구현할 수 있을까요? 첨부파일 (1)
    fre | 2020.09.23 | 추천 0 | 조회 22
    fre2020.09.23022
    1157 차트/그래프
    New 차트 최대값 및 Xfilter 함수 문의드립니다. (1)
    Elkins | 2020.09.23 | 추천 0 | 조회 20
    Elkins2020.09.23020
    1156 VBA
    New WorkSheet_Change 와 같은 사용자 정의 함수를 만드는 방법은 없나요? (1)
    엑셀늅늅이 | 2020.09.22 | 추천 0 | 조회 26
    엑셀늅늅이2020.09.22026
    1155 파워쿼리/피벗
    New 상단바가 잠금 걸렸는데 왜이러는지 모르겠습니다. 첨부파일 (4)
    신윤재 | 2020.09.22 | 추천 1 | 조회 25
    신윤재2020.09.22125
    1154 문서서식
    New 이미지 삽입 후 크기를 조절해도 압축되지 않고 원본 해상도 유지하는 방법? (5)
    악순환의 굴레 | 2020.09.22 | 추천 0 | 조회 24
    악순환의 굴레2020.09.22024
    1153 VBA
    New application.ontime 실행 시 멈추는 함수가 동작을 하지 않습니다 (재 질문, 오빠두 님 말대로 코드 수정 완료) - VBA 명령문 매 초마다 반복 (3)
    밍장군 | 2020.09.22 | 추천 0 | 조회 21
    밍장군2020.09.22021
    1152 함수/공식
    New 절대주소 고정했는데 셀 삽입하면 고정이 안되네요? (1)
    해랑사 | 2020.09.22 | 추천 0 | 조회 39
    해랑사2020.09.22039
    1151 기능/도구
    New 셀서식 왕초보 질문입니다! (3)
    Hannah | 2020.09.22 | 추천 0 | 조회 28
    Hannah2020.09.22028
    1150 VBA
    New 전역변수로 포맷까지 지정할 순 없나요? (2)
    밍장군 | 2020.09.22 | 추천 0 | 조회 19
    밍장군2020.09.22019
    1149 문서서식
    New 90일습관을 위한 엑셀 시트를 만들고 싶습니다. (3)
    파파깡 | 2020.09.22 | 추천 0 | 조회 36
    파파깡2020.09.22036
    1148 피벗테이블
    New 피벗으로 누적 데이터를 취하고 당일 실적을 별도로 뽑아서 (1)
    넘버원수박 | 2020.09.21 | 추천 0 | 조회 28
    넘버원수박2020.09.21028
    1147 함수/공식
    New 월별로 생산수량 나누기 첨부파일 (4)
    ejrdn**** | 2020.09.21 | 추천 0 | 조회 49
    ejrdn****2020.09.21049
    1146 함수/공식
    New 날짜 관련 데이터 추출 질문 드립니다. 첨부파일 (2)
    kh**** | 2020.09.21 | 추천 0 | 조회 32
    kh****2020.09.21032
    1145 VBA
    New 마지막 수정한 폴더이름 변수에 넣으려는데 오류가 나네요 (2)
    밍장군 | 2020.09.21 | 추천 0 | 조회 25
    밍장군2020.09.21025
    1144 함수/공식
    New 품목 중 중복인 항목의 각각 다른 특정값 합치는 방법? 첨부파일 (3)
    악순환의 굴레 | 2020.09.21 | 추천 0 | 조회 36
    악순환의 굴레2020.09.21036
    1143 함수/공식
    New DDE 함수 내에 다른 셀에 있는 코드를 불러서 변수로 사용하는방법 좀 알려 주세요 ~ 첨부파일
    아이콘 | 2020.09.21 | 추천 0 | 조회 24
    아이콘2020.09.21024
    1142 함수/공식
    New OFFSET 함수 ㅜㅜ 첨부파일 (1)
    gudwns**** | 2020.09.21 | 추천 0 | 조회 36
    gudwns****2020.09.21036
    1141 VBA
    New 엑셀 VBA 파일 합치기 질문드려요.. 첨부파일 (1)
    박상억 | 2020.09.21 | 추천 0 | 조회 24
    박상억2020.09.21024
    1140 함수/공식
    New 엑셀 SUM 관련 ㅠㅠ .. 첨부파일 (1)
    gudwns**** | 2020.09.21 | 추천 0 | 조회 43
    gudwns****2020.09.21043
    1139 함수/공식
    New '지정한 수식에서 현재 파일 형식에 허용되는 것보다 많은 중첩 수준을 사용하므로 수식을 업력할 수 없습니다.'메세지가 뜨네요! (1)
    러브포토맨 | 2020.09.21 | 추천 0 | 조회 21
    러브포토맨2020.09.21021
    1138 VBA
    New active x 체크박스 크기와 체크 색상 관련 질문 드립니다. 첨부파일 (2)
    신윤재 | 2020.09.21 | 추천 0 | 조회 21
    신윤재2020.09.21021