DetailPage-MSS-KB

기술 자료

기술 자료: 247412 - 마지막 검토: 2006년 2월 17일 금요일 - 수정: 7.0

요약

이 문서에서는 Microsoft Visual Basic 응용 프로그램에서 Microsoft Excel로 데이터를 전송하는 여러 가지 방법을 설명합니다. 또한 가장 적합한 솔루션을 선택할 수 있도록 각 방법에 대한 장점과 단점에 대해서도 설명합니다.

추가 정보

Excel 통합 문서로 데이터를 전송하는 데 가장 일반적으로 사용되는 방법은 자동화입니다. 자동화를 통해 통합 문서에서 데이터 위치를 융통성 있게 지정할 수 있으며 실행하는 동안 통합 문서의 서식을 지정하고 다양한 설정을 사용할 수 있습니다. 자동화를 사용하면 데이터를 전송하는 여러 가지 방법을 사용할 수 있습니다.
  • 셀 단위로 데이터 전송
  • 배열에 있는 데이터를 셀 범위로 전송
  • ADO 레코드 집합의 데이터를 CopyFromRecordset 메서드를 사용하여 셀 범위로 전송
  • ODBC 또는 OLEDB 데이터 원본에서 쿼리 결과가 포함된 Excel 워크시트에 QueryTable 만들기
  • 데이터를 클립보드로 전송한 다음 클립보드 내용을 Excel 워크시트로 붙여넣기
Excel로 데이터를 전송하는 데 사용할 수 있는 방법 중 자동화를 사용하지 않아도 되는 방법도 있습니다. 이는 서버쪽 응용 프로그램을 실행하는 경우 클라이언트에서 전송한 데이터를 대량으로 처리하기 위한 좋은 방법이 될 수 있습니다. 다음 방법을 사용하여 자동화를 사용하지 않고 데이터를 전송할 수 있습니다.
  • Excel에서 나중에 워크시트의 셀로 구문 분석할 수 있는 탭 또는 쉼표로 구분된 텍스트 파일로 데이터 전송
  • ADO를 사용하여 데이터를 워크시트로 전송
  • DDE(동적 데이터 교환)를 사용하여 Excel로 데이터 전송
다음 절에서는 각 솔루션에 대한 자세한 내용을 설명합니다.

자동화를 사용하여 셀 단위로 데이터 전송

자동화를 사용하여 한 번에 한 셀씩 워크시트로 데이터를 전송할 수 있습니다.
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add


   'Add data to cells of the first worksheet in the new workbook
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1").Value = "Last Name"
   oSheet.Range("B1").Value = "First Name"
   oSheet.Range("A1:B1").Font.Bold = True
   oSheet.Range("A2").Value = "Doe"
   oSheet.Range("B2").Value = "John"

   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
데이터를 셀 단위로 전송하는 방법은 데이터 양이 적은 경우 완벽한 방법이 될 수 있습니다. 이 방법을 통해 융통성 있게 통합 문서의 원하는 곳에 데이터를 배치할 수 있으며 실행하는 동안 조건에 따라 셀 서식을 지정할 수 있습니다. 그러나 이 방법은 Excel 통합 문서로 전송할 데이터 양이 많은 경우에는 사용하지 않는 것이 좋습니다. 실행하는 동안 얻는 각 Range 개체가 인터페이스를 요청하므로 이 방법으로 데이터를 전송하면 속도가 느려질 수 있습니다. 또한 Microsoft Windows 95 및 Windows 98에는 인터페이스 요청에 대해 64K 제한이 있습니다. 이 인터페이스 요청에 대한 64K 제한에 도달하거나 초과하는 경우 자동화 서버(Excel)가 응답하지 않거나 메모리 부족을 나타내는 오류가 발생할 수 있습니다. 이러한 Windows 95 및 Windows 98의 제한은 다음 기술 자료 문서에서 설명합니다.
216400  (http://support.microsoft.com/kb/216400/ ) PRB: 프로세스 간 COM 자동화를 사용하면 Win95/98에서 클라이언트 응용 프로그램이 중지될 수 있다
따라서 셀 단위 데이터 전송은 데이터 양이 적은 경우에만 적합합니다. 크기가 큰 데이터 집합을 Excel로 전송해야 하는 경우 뒤에서 설명하는 솔루션 중 하나를 고려하십시오.

Excel을 자동화하는 예제 코드는 Microsoft 기술 자료의 다음 문서를 참조하십시오.
219151  (http://support.microsoft.com/kb/219151/ ) Visual Basic에서 Microsoft Excel을 자동화하는 방법

자동화를 사용하여 워크시트의 범위로 데이터 배열 전송

데이터 배열을 한 번에 여러 셀 범위로 전송할 수 있습니다.
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add

   'Create an array with 3 columns and 100 rows
   Dim DataArray(1 To 100, 1 To 3) As Variant
   Dim r As Integer
   For r = 1 To 100
      DataArray(r, 1) = "ORD" & Format(r, "0000")
      DataArray(r, 2) = Rnd() * 1000
      DataArray(r, 3) = DataArray(r, 2) * 0.7
   Next

   'Add headers to the worksheet on row 1
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")

   'Transfer the array to the worksheet starting at cell A2
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
셀 단위가 아니라 배열을 사용하여 데이터를 전송하는 경우 데이터 양이 많을 때 크게 향상된 성능을 확인할 수 있습니다. 워크시트의 300개 셀에 데이터를 전송하는 위의 코드에서 다음 줄을 보십시오.
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
이 줄은 두 인터페이스 요청(Range 메서드가 반환하는 Range 개체에 대한 요청, Resize 메서드가 반환하는 Range 개체에 대한 요청)을 나타냅니다. 반면에 셀 단위로 데이터를 전송하려면 Range 개체에 대해 300개의 인터페이스를 요청해야 합니다. 가능하면 데이터를 대량으로 전송하고 인터페이스 요청 수를 줄이는 것이 좋습니다.

자동화를 사용하여 워크시트 범위로 ADO 레코드 집합 전송

Excel 2000에는 ADO(또는 DAO) 레코드 집합을 워크시트의 범위로 전송할 수 있는 CopyFromRecordset 메서드가 도입되었습니다. 다음 코드는 Excel 2000, Excel 2002 또는 Office Excel 2003을 자동화하고 CopyFromRecordset 메서드를 사용하여 Northwind 예제 데이터베이스에 있는 Orders 테이블 내용을 전송하는 방법을 보여 줍니다.
   'Create a Recordset from all the records in the Orders table
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("Orders", , adCmdTable)
   
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   
   'Transfer the data to Excel
   oSheet.Range("A1").CopyFromRecordset rs
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
   
   'Close the connection
   rs.Close
   conn.Close
Excel 97에서도 CopyFromRecordset 메서드를 제공하지만 이 메서드는 DAO 레코드 집합에만 사용할 수 있습니다. Excel 97에서 제공하는 CopyFromRecordset은 ADO를 지원하지 않습니다.

ADO 및 CopyFromRecordset 메서드 사용에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
246335  (http://support.microsoft.com/kb/246335/ ) 자동화를 사용하여 ADO 레코드 집합에서 Excel로 데이터를 전송하는 방법

자동화를 사용하여 워크시트에 QueryTable 만들기

QueryTable 개체는 외부 데이터 원본에서 반환된 데이터로 만든 테이블을 나타냅니다. Microsoft Excel을 자동화하는 동안 SQL 문자열과 함께 OLEDB 또는 ODBC 데이터 원본에 연결 문자열을 입력함으로써 간단하게 QueryTable을 만들 수 있습니다. Excel에서 레코드 집합을 생성하고 지정한 위치에 있는 워크시트에 이 레코드 집합을 삽입합니다. QueryTable을 사용하면 CopyFromRecordset 메서드에 비해 여러 가지 장점을 얻을 수 있습니다.
  • Excel에서 레코드 집합을 생성하고 워크시트에 이를 배치하는 일을 처리합니다.
  • 업데이트된 레코드 집합을 얻기 위해 나중에 새로 고칠 수 있도록 쿼리를 QueryTable에 저장할 수 있습니다.
  • QueryTable을 워크시트에 추가할 때 새 데이터를 수용하기 위해 워크시트의 셀에 이미 있는 데이터를 이동하도록 지정할 수 있습니다(자세한 내용은 RefreshStyle 속성 참조).
다음 코드는 Excel 2000, Excel 2002 또는 Office Excel 2003을 자동화하여 Northwind 예제 데이터베이스의 데이터로 Excel 워크시트에서 새 QueryTable을 만드는 방법을 보여 줍니다.
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   
   'Create the QueryTable
   Dim sNWind As String
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   Dim oQryTable As Object
   Set oQryTable = oSheet.QueryTables.Add( _
   "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
   oQryTable.RefreshStyle = xlInsertEntireRows
   oQryTable.Refresh False
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

클립보드 사용

워크시트로 데이터를 전송하는 메커니즘으로 Windows 클립보드를 사용할 수도 있습니다. 워크시트의 여러 셀에 데이터를 붙여 넣으려면 열이 탭 문자로 구분되고 행이 캐리지 리턴으로 구분된 문자열을 복사할 수 있습니다. 다음 코드는 Visual Basic에서 Clipboard 개체를 사용하여 데이터를 Excel로 전송하는 방법을 보여 줍니다.
   'Copy a string to the clipboard
   Dim sData As String
   sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
           & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
           & "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
   Clipboard.Clear

   Clipboard.SetText sData
   
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   

   'Paste the data
   oBook.Worksheets(1).Range("A1").Select
   oBook.Worksheets(1).Paste
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Excel에서 행과 열로 구문을 분석할 수 있는 구분된 텍스트 파일 만들기

Excel에서는 탭 또는 쉼표로 구분된 파일을 열고 데이터를 셀로 올바르게 구문 분석할 수 있습니다. 자동화를 거의 사용하지 않고 많은 양의 데이터를 워크시트로 전송하는 경우 이 기능을 이용할 수 있습니다. 이 방법은 서버쪽 텍스트 파일을 생성할 수 있기 때문에 클라이언트-서버 응용 프로그램에 유용할 수 있습니다. 그런 다음 해당하는 경우 자동화를 사용하여 클라이언트에서 텍스트 파일을 열 수 있습니다.

다음 코드는 ADO 레코드 집합에서 쉼표로 구분된 텍스트 파일을 만드는 방법을 보여 줍니다.
   'Create a Recordset from all the records in the Orders table
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim sData As String
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("Orders", , adCmdTable)
   
   'Save the recordset as a tab-delimited file
   sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
   Open "C:\Test.txt" For Output As #1
   Print #1, sData
   Close #1
    
   'Close the connection
   rs.Close
   conn.Close
   
   'Open the new text file in Excel
   Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
      Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
텍스트 파일에 .CSV 확장명이 있는 경우 Excel에서는 텍스트 마법사를 표시하지 않고 파일을 열며 파일이 쉼표로 구분되어 있다고 자동으로 가정합니다. 마찬가지로 파일에 .TXT 확장명이 있는 경우 Excel에서는 탭 구분 기호를 사용하여 파일을 자동으로 구문 분석합니다.

앞의 코드 예제에서 Excel은 Shell 명령문을 사용하여 시작했고 파일 이름이 명령줄 인수로 사용되었습니다. 앞의 예제에서는 자동화가 사용되지 않았습니다. 그러나 원할 경우 자동화를 최소한으로 사용하여 텍스트 파일을 열고 Excel 통합 문서 형식으로 저장할 수 있습니다.
   'Create a new instance of Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
       
   'Open the text file
   Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
   
   'Save as Excel workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
   oExcel.Quit
Visual Basic 응용 프로그램에서 파일 I/O를 사용하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
172267  (http://support.microsoft.com/kb/172267/ ) SAMPLE: RECEDIT.VBP에서는 Visual Basic에서 파일 I/O를 보여준다

ADO를 사용하여 워크시트에 데이터 전송

Microsoft Jet OLE DB Provider를 사용하여 기존 Excel 통합 문서의 테이블에 레코드를 추가할 수 있습니다. Excel에서 "테이블"은 이름이 정의된 범위일 뿐입니다. 범위의 첫 번째 행은 머리글(또는 필드 이름)을 포함하고 이후의 모든 행은 레코드를 포함해야 합니다. 다음 단계는 MyTable이라는 빈 테이블이 있는 통합 문서를 만드는 방법을 보여 줍니다.
  1. Excel에서 새 통합 문서를 시작합니다.
  2. 다음 머리글을 Sheet1의 셀 A1:B1에 추가합니다.

    A1: FirstName B1: LastName
  3. 셀 B1 서식을 오른쪽 맞춤으로 지정합니다.
  4. A1:B1을 선택합니다.
  5. 삽입 메뉴에서 이름을 선택한 다음 정의를 선택합니다. 이름으로 MyTable을 입력하고 확인을 누릅니다.
  6. 새 통합 문서를 C:\Book1.xls로 저장하고 Excel을 종료합니다.
ADO를 사용하여 레코드를 MyTable에 추가하려면 다음과 유사한 코드를 사용할 수 있습니다.
   'Create a new connection object for Book1.xls
   Dim conn As New ADODB.Connection
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
   conn.Execute "Insert into MyTable (FirstName, LastName)" & _
      " values ('Bill', 'Brown')"
   conn.Execute "Insert into MyTable (FirstName, LastName)" & _
      " values ('Joe', 'Thomas')"
   conn.Close
이 방법으로 테이블에 레코드를 추가하면 통합 문서의 서식이 유지됩니다. 앞의 예제에서 열 B에 추가된 새 필드의 서식은 오른쪽 맞춤으로 지정됩니다. 행에 추가된 각 레코드는 바로 위 행의 서식을 사용합니다.

레코드를 워크시트의 셀에 추가할 때는 해당 셀에 있는 이전 데이터를 덮어씁니다. 즉, 새 레코드를 추가할 때 워크시트의 행이 "아래로 밀려 내려가지" 않습니다. 워크시트에서 데이터 레이아웃을 설계할 때는 이 점을 염두에 두어야 합니다.

참고 ADO 또는 DAO를 사용하여 Excel 워크시트에서 데이터를 업데이트하는 방법은 Office 2003 서비스 팩 2(SP2)를 설치하거나 Microsoft 기술 자료 문서 904018에 포함된 Access 2002용 업데이트를 설치한 후 Access 내의 Visual Basic for Applications 환경에서 작동하지 않습니다. 이 방법은 Word, Excel 및 Outlook과 같은 다른 Office 응용 프로그램의 Visual Basic for Applications 환경에서는 제대로 작동합니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
904953  (http://support.microsoft.com/kb/904953/ ) Office Access 2003 또는 Access 2002에서 Excel 통합 문서로 연결된 테이블의 데이터를 변경하거나 추가하거나 삭제할 수 없다
904018  (http://support.microsoft.com/kb/904018/ ) 2005년 10월 18일자 Access 2002용 업데이트에 대한 설명

ADO를 사용하여 Excel 통합 문서에 액세스하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
195951  (http://support.microsoft.com/kb/195951/ ) ASP에서 ADO를 사용하여 Excel 데이터를 쿼리 및 업데이트하는 방법

DDE를 사용하여 Excel로 데이터 전송

DDE는 자동화를 대신하여 Excel과 통신하고 데이터를 전송하는 수단으로 사용할 수 있지만, 자동화 및 COM이 등장하면서 더 이상 다른 응용 프로그램과 통신하는 기본 방법으로 사용하지 않으며 사용할 수 있는 다른 솔루션이 없을 때만 사용해야 합니다.

DDE를 사용하여 Excel로 데이터를 전송하려면 다음을 수행할 수 있습니다.
  • LinkPoke 메서드를 사용하여 특정 셀 범위로 데이터를 포크합니다.

    또는
  • LinkExecute 메서드를 사용하여 Excel에서 실행할 명령을 보냅니다.
다음 코드 예제에서는 워크시트의 셀로 데이터를 포크하고 명령을 실행할 수 있도록 Excel과의 DDE 대화를 설정하는 방법을 보여 줍니다. 이 예제를 사용하여 LinkTopic의 Excel|MyBook.xls에 대해 DDE 대화를 성공적으로 설정하려면 이름이 MyBook.xls인 통합 문서가 실행 중인 Excel 인스턴스에 이미 열려 있어야 합니다.

참고 이 예제에서 Text1은 Visual Basic 폼의 Text Box 컨트롤을 나타냅니다.
   'Initiate a DDE communication with Excel
   Text1.LinkMode = 0
   Text1.LinkTopic = "Excel|MyBook.xls"
   Text1.LinkItem = "R1C1:R2C3"
   Text1.LinkMode = 1
   
   'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls
   Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
                "four" & vbTab & "five" & vbTab & "six"
   Text1.LinkPoke
   
   'Execute commands to select cell A1 (same as R1C1) and change the font
   'format
   Text1.LinkExecute "[SELECT(""R1C1"")]"
   Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
   
   'Terminate the DDE communication
   Text1.LinkMode = 0
Excel에서 LinkPoke를 사용하는 경우 LinkItem에 대한 행-열(R1C1) 표시에서 범위를 지정합니다. 여러 셀로 데이터를 포크하는 경우 열은 탭으로 구분되고 행은 캐리지 리턴으로 구분되는 문자열을 사용할 수 있습니다.

LinkExecute를 사용하여 Excel에서 명령을 수행하도록 요청할 때는 XLM(Excel 매크로 언어) 구문으로 Excel에 명령을 제공해야 합니다. XLM 설명서는 Excel 버전 97 이상에는 포함되어 있지 않습니다. XLM 설명서를 구하는 방법에 대한 자세한 내용은 Microsoft 기술 자료 문서를 참조하십시오.
143466  (http://support.microsoft.com/kb/143466/ ) XL97: Macro97.exe 파일을 온라인 서비스에서 구할 수 있다
DDE는 Excel과 통신하는 데 사용할 권장 솔루션이 아닙니다. 자동화를 사용하는 것이 융통성 있게 작업하고 Excel에서 제공하는 새로운 기능에 보다 많이 액세스할 수 있는 방법입니다.

참조

자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
306022  (http://support.microsoft.com/kb/306022/ ) Visual Basic .NET을 사용하여 Excel 통합 문서에 데이터를 전송하는 방법




Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹 (http://support.microsoft.com/newsgroups/default.aspx) 에 참여하시기 바랍니다.

본 문서의 정보는 다음의 제품에 적용됩니다.
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic for Applications 5.0
  • Microsoft Visual Basic for Applications 6.0
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Professional Edition
키워드: 
kbinfo kbautomation kbdde KB247412
공유
추가 지원 옵션
Microsoft Community 지원 포럼
직접 문의하기
Microsoft Certified Partner 찾기
Microsoft Store
소기업이 아닙니까?
다음에서 팔로우하십시오.