Visual Basic에서 Excel로 데이터를 전송하는 방법
요약
이 문서에서는 Microsoft Visual Basic 애플리케이션에서 Microsoft Excel로 데이터를 전송하는 다양한 방법을 설명합니다. 또한 이 문서에서는 가장 적합한 솔루션을 선택할 수 있도록 각 메서드의 장점과 단점을 제시합니다.
추가 정보
Excel 통합 문서로 데이터를 전송하는 데 가장 일반적으로 사용되는 방법은 Automation입니다. Automation을 사용하면 통합 문서에서 데이터의 위치를 지정할 수 있는 유연성과 통합 문서의 서식을 지정하고 런타임에 다양한 설정을 만들 수 있습니다. Automation을 사용하면 데이터를 전송하는 몇 가지 방법을 사용할 수 있습니다.
- 셀로 데이터 셀 전송
- 배열의 데이터를 셀 범위로 전송
- CopyFromRecordset 메서드를 사용하여 ADO 레코드 집합의 데이터를 셀 범위로 전송
- ODBC 또는 OLEDB 데이터 원본에 대한 쿼리 결과가 포함된 Excel 워크시트에 QueryTable 만들기
- 데이터를 클립보드로 전송한 다음 클립보드 내용을 Excel 워크시트에 붙여넣습니다.
Automation이 필요하지 않은 Excel로 데이터를 전송하는 데 사용할 수 있는 메서드도 있습니다. 애플리케이션 서버 쪽을 실행하는 경우 클라이언트에서 데이터를 대량으로 처리하는 데 좋은 방법이 될 수 있습니다. 다음 메서드를 사용하여 Automation 없이 데이터를 전송할 수 있습니다.
- Excel에서 나중에 워크시트의 셀로 구문 분석할 수 있는 탭 또는 쉼표로 구분된 텍스트 파일로 데이터 전송
- ADO를 사용하여 워크시트로 데이터 전송
- DDE(동적 데이터 교환)를 사용하여 Excel로 데이터 전송
다음 섹션에서는 이러한 각 솔루션에 대해 자세히 설명합니다.
참고 Microsoft Office Excel 2007을 사용하는 경우 통합 문서를 저장할 때 새 Excel 2007 통합 문서(*.xlsx) 파일 형식을 사용할 수 있습니다. 이렇게 하려면 다음 코드 예제에서 다음 코드 줄을 찾습니다.
oBook.SaveAs "C:\Book1.xls"
이 코드를 다음 코드 줄로 바꿉다.
oBook.SaveAs "C:\Book1.xlsx"
또한 Northwind 데이터베이스는 기본적으로 Office 2007에 포함되지 않습니다. 그러나 Microsoft Office Online에서 Northwind 데이터베이스를 다운로드할 수 있습니다.
Automation을 사용하여 셀로 데이터 셀 전송
Automation을 사용하면 한 번에 하나의 셀로 데이터를 워크시트로 전송할 수 있습니다.
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 제한에 도달하거나 초과하면 Automation 서버(Excel)가 응답하지 않거나 메모리 부족을 나타내는 오류가 발생할 수 있습니다.
다시 한 번 셀로 데이터 셀을 전송하는 것은 소량의 데이터에만 허용됩니다. 큰 데이터 집합을 Excel로 전송해야 하는 경우 나중에 제공되는 솔루션 중 하나를 고려해야 합니다.
Excel 자동화에 대한 자세한 샘플 코드는 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 메서드가 도입되었습니다. 다음 코드에서는 CopyFromRecordset 메서드를 사용하여 Excel 2000, Excel 2002 또는 Office Excel 2003을 자동화하고 Northwind Sample Database의 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
참고 Office 2007 버전의 Northwind 데이터베이스를 사용하는 경우 코드 예제에서 다음 코드 줄을 바꿔야 합니다.
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
이 코드 줄을 다음 코드 줄로 바꿉다.
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Excel 97은 CopyFromRecordset 메서드도 제공하지만 DAO 레코드 집합에서만 사용할 수 있습니다. Excel 97의 CopyFromRecordset은 ADO를 지원하지 않습니다.
ADO 및 CopyFromRecordset 메서드를 사용하는 방법에 대한 자세한 내용은 자동화를 사용하여 ADO 레코드 집합에서 Excel로 데이터를 전송하는 방법을 참조하세요.
자동화를 사용하여 워크시트에서 QueryTable 만들기
QueryTable 개체는 외부 데이터 원본에서 반환된 데이터에서 빌드된 테이블을 나타냅니다. Microsoft Excel을 자동화하는 동안 SQL 문자열과 함께 OLEDB 또는 ODBC 데이터 원본에 연결 문자열 제공하여 QueryTable을 만들 수 있습니다. Excel에서는 레코드 집합을 생성하고 지정한 위치의 워크시트에 삽입해야 합니다. QueryTables를 사용하면 CopyFromRecordset 메서드보다 몇 가지 이점이 있습니다.
- Excel은 레코드 집합 만들기 및 워크시트에 대한 배치를 처리합니다.
- 쿼리는 나중에 새로 고쳐 업데이트된 레코드 집합을 가져올 수 있도록 QueryTable을 사용하여 저장할 수 있습니다.
- 워크시트에 새 QueryTable이 추가되면 워크시트의 셀에 이미 있는 데이터를 새 데이터에 맞게 이동하도록 지정할 수 있습니다(자세한 내용은 RefreshStyle 속성 참조).
다음 코드에서는 Northwind 샘플 데이터베이스의 데이터를 사용하여 Excel 워크시트에 새 QueryTable을 만들기 위해 Excel 2000, Excel 2002 또는 Office Excel 2003을 자동화하는 방법을 보여 줍니다.
'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이 클립보드 개체를 사용하여 데이터를 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에서는 탭 또는 쉼표로 구분된 파일을 열고 데이터를 셀로 올바르게 구문 분석할 수 있습니다. 자동화를 거의 사용하지 않는 경우 워크시트로 대량의 데이터를 전송하려는 경우 이 기능을 활용할 수 있습니다. 텍스트 파일을 서버 쪽에서 생성할 수 있으므로 클라이언트 서버 애플리케이션에 좋은 방법이 될 수 있습니다. 그런 다음, 적절한 경우 Automation을 사용하여 클라이언트에서 텍스트 파일을 열 수 있습니다.
다음 코드는 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
참고 Northwind 데이터베이스의 Office 2007 버전을 사용하는 경우 코드 예제에서 다음 코드 줄을 바꿔야 합니다.
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
이 코드 줄을 다음 코드 줄로 바꿉다.
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
sNWind & ";"
텍스트 파일에 .CSV 확장자가 있는 경우 Excel에서 텍스트 가져오기 마법사를 표시하지 않고 파일을 열고 파일이 쉼표로 구분된 것으로 자동으로 가정합니다. 마찬가지로 파일에 .TXT 확장자가 있는 경우 Excel에서는 탭 구분 기호를 사용하여 파일을 자동으로 구문 분석합니다.
이전 코드 샘플에서 Excel은 Shell 문을 사용하여 시작되었으며 파일 이름은 명령줄 인수로 사용되었습니다. 이전 샘플에서는 Automation이 사용되지 않았습니다. 그러나 원하는 경우 최소한의 Automation을 사용하여 텍스트 파일을 열고 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
ADO를 사용하여 워크시트로 데이터 전송
Microsoft Jet OLE DB 공급자를 사용하여 기존 Excel 통합 문서의 테이블에 레코드를 추가할 수 있습니다. Excel의 "테이블"은 정의된 이름의 범위일 뿐입니다. 범위의 첫 번째 행에는 머리글(또는 필드 이름)이 포함되어야 하며 모든 후속 행에는 레코드가 포함되어야 합니다. 다음 단계에서는 MyTable이라는 빈 테이블을 사용하여 통합 문서를 만드는 방법을 보여 줍니다.
Excel 97, Excel 2000 및 Excel 2003
Excel에서 새 통합 문서를 시작합니다.
Sheet1의 셀 A1:B1에 다음 헤더를 추가합니다.
A1: FirstName B1: LastName
셀 B1의 서식을 오른쪽 맞춤으로 지정합니다.
A1:B1을 선택합니다.
삽입 메뉴에서 이름을 선택한 다음 정의를 선택합니다. MyTable이라는 이름을 입력하고 확인을 클릭합니다.
새 통합 문서를 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
Excel 2007
Excel 2007에서 새 통합 문서를 시작합니다.
Sheet1의 셀 A1:B1에 다음 헤더를 추가합니다.
A1: FirstName B1: LastName
셀 B1의 서식을 오른쪽 맞춤으로 지정합니다.
A1:B1을 선택합니다.
리본에서 수식 탭을 클릭한 다음 이름 정의를 클릭합니다. MyTable 이름을 입력하고 확인을 클릭합니다.
새 통합 문서를 C:\Book1.xlsx 저장한 다음 Excel을 종료합니다.
ADO를 사용하여 MyTable 테이블에 레코드를 추가하려면 다음 코드 예제와 유사한 코드를 사용합니다.
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Scott', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Jane', 'Dow')"
conn.Close
이러한 방식으로 테이블에 레코드를 추가하면 통합 문서의 서식이 유지됩니다. 이전 예제에서는 B 열에 추가된 새 필드의 서식이 올바른 맞춤으로 지정되었습니다. 행에 추가되는 각 레코드는 위의 행에서 형식을 차용합니다.
레코드가 워크시트의 셀 또는 셀에 추가되면 해당 셀의 모든 데이터를 덮어씁니다. 즉, 워크시트의 행은 새 레코드가 추가될 때 "푸시다운"되지 않습니다. 워크시트에서 데이터 레이아웃을 디자인할 때는 이 점을 염두에 두어야 합니다.
참고
ADO를 사용하거나 DAO를 사용하여 Excel 워크시트에서 데이터를 업데이트하는 방법은 Office 2003 SP2(서비스 팩 2)를 설치한 후 또는 Microsoft 기술 자료 문서 904018 포함된 Access 2002 업데이트를 설치한 후 Access 내의 Visual Basic for Application 환경에서 작동하지 않습니다. 이 메서드는 Word, Excel 및 Outlook과 같은 다른 Office 응용 프로그램의 Visual Basic for Application 환경에서 잘 작동합니다.
자세한 내용은 다음 문서를 참조하세요.
Office Access 2003 또는 Access 2002의 Excel 통합 문서에 연결된 테이블에서는 데이터를 변경, 추가 또는 삭제할 수 없습니다.
ADO를 사용하여 Excel 통합 문서에 액세스하는 방법에 대한 자세한 내용은 ASP에서 ADO를 사용하여 Excel 데이터를 쿼리하고 업데이트하는 방법을 참조하세요.
DDE를 사용하여 Excel로 데이터 전송
DDE는 Excel과 통신하고 데이터를 전송하기 위한 수단으로 자동화의 대안입니다. 그러나 Automation 및 COM이 등장하면서 DDE는 더 이상 다른 애플리케이션과 통신하는 데 선호되는 방법이 아니고 사용 가능한 다른 솔루션이 없는 경우에만 사용해야 합니다.
DDE를 사용하여 Excel로 데이터를 전송하려면 LinkPoke 메서드를 사용하여 특정 셀 범위로 데이터를 찌르거나 LinkExecute 메서드를 사용하여 Excel에서 실행할 명령을 보낼 수 있습니다.
다음 코드 예제에서는 워크시트의 셀에 데이터를 찌르고 명령을 실행할 수 있도록 Excel과 DDE 대화를 설정하는 방법을 보여 줍니다. 이 샘플을 사용하여 DDE 대화가 LinkTopic Excel에 성공적으로 설정되도록 합니다.MyBook.xls excel의 실행 중인 instance 이름이 MyBook.xls 통합 문서를 이미 열어야 합니다.
참고
Excel 2007을 사용하는 경우 새 .xlsx 파일 형식을 사용하여 통합 문서를 저장할 수 있습니다. 다음 코드 예제에서 파일 이름을 업데이트해야 합니다. 이 예제에서 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에 명령을 수행하도록 요청하는 경우 EXCEL XLM(매크로 언어)의 구문에서 Excel에 명령을 제공해야 합니다. XLM 설명서는 Excel 버전 97 이상에 포함되지 않습니다.
DDE는 Excel과 통신하는 데 권장되는 솔루션이 아닙니다. 자동화는 최고의 유연성을 제공하며 Excel에서 제공하는 새로운 기능에 더 많은 액세스 권한을 제공합니다.
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기