ÀÚµ¿È­¸¦ »ç¿ëÇÏ¿© ADO ·¹ÄÚµå ÁýÇÕ¿¡¼­ Excel·Î µ¥ÀÌÅ͸¦ Àü¼ÛÇÏ´Â ¹æ¹ý

¿¹Á¦¸¦ ¸¸µå´Â ´Ü°è

1. Visual BasicÀ» ½ÃÀÛÇÏ°í Ç¥ÁØ EXE ÇÁ·ÎÁ§Æ®¸¦ »õ·Î ¸¸µì´Ï´Ù. ±âº»ÀûÀ¸·Î Form1ÀÌ »ý¼ºµË´Ï´Ù.
2. Form1¿¡ CommandButtonÀ» Ãß°¡ÇÕ´Ï´Ù.
3. ÇÁ·ÎÁ§Æ® ¸Þ´º¿¡¼­ ÂüÁ¶¸¦ ´©¸¨´Ï´Ù. Microsoft ActiveX Data Objects 2.1 Library¿¡ ´ëÇÑ ÂüÁ¶¸¦ Ãß°¡ÇÕ´Ï´Ù.
4. Form1ÀÇ ÄÚµå ±¸¿ª¿¡ ´ÙÀ½ Äڵ带 ºÙ¿© ³Ö½À´Ï´Ù.

   Private Sub Command1_Click()
       Dim cnt As New ADODB.Connection
       Dim rst As New ADODB.Recordset
       
       Dim xlApp As Object
       Dim xlWb As Object
       Dim xlWs As Object
   
       
       Dim recArray As Variant
       
       Dim strDB As String
       Dim fldCount As Integer
       Dim recCount As Long
       Dim iCol As Integer
       Dim iRow As Integer
       
       ' Set the string to the path of your Northwind database
       strDB = "c:\program files\Microsoft office\office11\samples\Northwind.mdb"
     
       ' Open connection to the database
       cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & strDB & ";"
           
       ' Open recordset based on Orders table
       rst.Open "Select * From Orders", cnt
       
       ' Create an instance of Excel and add a workbook
       Set xlApp = CreateObject("Excel.Application")
       Set xlWb = xlApp.Workbooks.Add
       Set xlWs = xlWb.Worksheets("Sheet1")
     
       ' Display Excel and give user control of Excel's lifetime
       xlApp.Visible = True
       xlApp.UserControl = True
       
       ' Copy field names to the first row of the worksheet
       fldCount = rst.Fields.Count
       For iCol = 1 To fldCount
           xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
       Next
           
       ' Check version of Excel
       If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
           'EXCEL 2000 or 2002: Use CopyFromRecordset
            
           ' Copy the recordset to the worksheet, starting in cell A2
           xlWs.Cells(2, 1).CopyFromRecordset rst
           'Note: CopyFromRecordset will fail if the recordset
           'contains an OLE object field or array data such
           'as hierarchical recordsets
           
       Else
           'EXCEL 97 or earlier: Use GetRows then copy array to Excel
       
           ' Copy recordset to an array
           recArray = rst.GetRows
           'Note: GetRows returns a 0-based array where the first
           'dimension contains fields and the second dimension
           'contains records. We will transpose this array so that
           'the first dimension contains records, allowing the
           'data to appears properly when copied to Excel
           
           ' Determine number of records
   
           recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
           
   
           ' Check the array for contents that are not valid when
           ' copying the array to an Excel worksheet
           For iCol = 0 To fldCount - 1
               For iRow = 0 To recCount - 1
                   ' Take care of Date fields
                   If IsDate(recArray(iCol, iRow)) Then
                       recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                   ' Take care of OLE object fields or array fields
                   ElseIf IsArray(recArray(iCol, iRow)) Then
                       recArray(iCol, iRow) = "Array Field"
                   End If
               Next iRow 'next record
           Next iCol 'next field
               
           ' Transpose and Copy the array to the worksheet,
           ' starting in cell A2
           xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
               TransposeDim(recArray)  
       End If
   
       ' Auto-fit the column widths and row heights
       xlApp.Selection.CurrentRegion.Columns.AutoFit
       xlApp.Selection.CurrentRegion.Rows.AutoFit
   
       ' Close ADO objects
       rst.Close
       cnt.Close
       Set rst = Nothing
       Set cnt = Nothing
       
       ' Release Excel references
       Set xlWs = Nothing
       Set xlWb = Nothing
   
       Set xlApp = Nothing
   
   End Sub
   
   
   Function TransposeDim(v As Variant) As Variant
   ' Custom Function to Transpose a 0-based array (v)
       
       Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
       Dim tempArray As Variant
       
       Xupper = UBound(v, 2)
       Yupper = UBound(v, 1)
       
       ReDim tempArray(Xupper, Yupper)
       For X = 0 To Xupper
           For Y = 0 To Yupper
               tempArray(X, Y) = v(Y, X)
           Next Y
       Next X
       
       TransposeDim = tempArray
   
   End Function
   
   					

5. F5 Å°¸¦ ´­·¯ ÇÁ·ÎÁ§Æ®¸¦ ½ÇÇàÇÕ´Ï´Ù. Form1ÀÌ ³ªÅ¸³³´Ï´Ù.
6. Form1¿¡¼­ CommandButtonÀ» ´©¸£°í Orders Å×À̺íÀÇ ³»¿ëÀÌ ExcelÀÇ »õ ÅëÇÕ ¹®¼­¿¡ Ç¥½ÃµÇ´ÂÁö È®ÀÎÇÕ´Ï´Ù.

• ¹è¿­¿¡´Â °èÃþÀû ·¹ÄÚµå ÁýÇÕ °°Àº OLE °³Ã¼ Çʵ峪 ¹è¿­ µ¥ÀÌÅÍ°¡ Æ÷Ç﵃ ¼ö ¾ø½À´Ï´Ù. ÄÚµå ¿¹Á¦¿¡¼­´Â ÀÌ Á¶°ÇÀ» °Ë»çÇÏ°í "Array Field"¸¦ Ç¥½ÃÇÏ¿© Çʵ带 Excel¿¡ Ç¥½ÃÇÒ ¼ö ¾øÀ½À» »ç¿ëÀÚ¿¡°Ô ¾Ë¸³´Ï´Ù.
  
  
• ¹è¿­¿¡´Â ³¯Â¥°¡ 1900³â ÀÌÀüÀÎ ³¯Â¥ Çʵ尡 Æ÷Ç﵃ ¼ö ¾ø½À´Ï´Ù. ÇØ´ç Microsoft ±â¼ú ÀÚ·á ¹®¼­ ¸µÅ©´Â "ÂüÁ¶" ÀýÀ» ÂüÁ¶ÇϽʽÿÀ. ÄÚµå ¿¹Á¦¿¡¼­´Â ÀÌ ¹®Á¦°¡ ¹ß»ýÇÏÁö ¾Êµµ·Ï ³¯Â¥ Çʵ带 º¯Çü ¹®ÀÚ¿­·Î ¼­½Ä ÁöÁ¤ÇÕ´Ï´Ù.

   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				

• ¹è¿­¿¡´Â 255ÀÚ ÀÌ»óÀÇ ¿ä¼Ò°¡ Æ÷Ç﵃ ¼ö ¾ø½À´Ï´Ù.
• ¹è¿­¿¡´Â Null °ªÀÌ Æ÷Ç﵃ ¼ö ¾ø½À´Ï´Ù.
• ¹è¿­¿¡ ÀÖ´Â ¿ä¼Ò ¼ö´Â 5461À» ÃÊ°úÇÒ ¼ö ¾ø½À´Ï´Ù.

À§·Î °¡±â | Çǵå¹é º¸³»±â