[¼ö°­±â°£] 2010. 11. 1 ~ 2011. 10. 31
½Åû¹®ÀÇ : À¥Å¸ÀÓ±³À°¼¾ÅÍ 02-3477-8300, ¿î¿µ°ü¸® : ¿ÀÇǽºÆ©ÅÍ 070-7098-2554

±³À°°úÁ¤ ³ªÀÇÇнÀ ¿ÀÇǽºÆÁ °Ë»ö ¹«·á°­ÁÂ
Home ·Î±×ÀΠȸ¿ø°¡ÀÔ °í°´¼¾ÅÍ »çÀÌÆ®¸Ê
¿ÀÇǽºÆ©ÅÍ ±â¾÷ASP¼­ºñ½º > ¹«·á°­ÁÂ
¿¢¼¿ 2000 µû¶óÇϱâ
Ȩ·¯´× ¿¢¼¿ 2000°­ÁÂ
À繫 ÇÔ¼ö °­ÁÂ
¿¢¼¿ 2002 »õ·Î¿î ±â´É
Ȳ±â¼º´ÔÀÇ VBA°­ÁÂ
°û½ÂÁÖ´ÔÀÇ VBAÀ̾߱â
Home > ¹«·á°­Á > ¿¢¼¿
¿¢¼¿, °û½ÂÁÖ´ÔÀÇ ¿ÀÆ©°ø±¸ÇÔ Á¦ÀÛÀ¸·Î ¹è¿öº¸´Â VBA À̾߱â, Excel

6. Áߺ¹ µ¥ÀÌÅÍ Ã³¸® -¥²

ÀÚ·á´Ù¿î·Îµå : ot006.xls 

¾È³çÇϼ¼¿ä. µÎ ´Þµ¿¾È ¿©¸§ ¹æÇÐÀ» º¸³»°í °¡À»À» ¸Â¾Æ ¿ÀÆ©°ø±¸ÇÔÀ» ´Ù½Ã ½ÃÀÛÇÏ´Â °û½ÂÁÖÀÔ´Ï´Ù. ¿À·£¸¸¿¡ ¾²·Á´Ï±î ¸Å¿ì ¾¦¾²·´±º¿ä. Áö³­ ¹ø¿¡µµ Ä÷³ ¿¬Àç°¡ ´Ê¾ú´Âµ¥, À̹ø¿¡µµ »ó´çÈ÷ ´Ê¾ú½À´Ï´Ù.

À̹ø ½Ã°£¿¡´Â Áߺ¹µ¥ÀÌÅ͸¦ ó¸®ÇÏ´Â ¼¼ ¹ø° ¿¬Àç Áß ¸¶Áö¸· ºÎºÐÀÔ´Ï´Ù.
À̹ø Áߺ¹µ¥ÀÌÅ͸¦ ¿ä¾àÇÏÀÚ¸é, ¿öÅ©½ÃÆ®ÀÇ ¸ñ·ÏÀ» ÇϳªÀÇ µ¥ÀÌÅͺ£À̽º Å×À̺í·Î ÀúÀåÇÏ°í »ç¿ëÀÚ°¡ ÁöÁ¤ÇÑ °£´ÜÇÑ Á¶°Ç¿¡ µû¶ó ´Ù½Ã Äõ¸®ÇÏ¿© »õ ¿öÅ©½ÃÆ®¸¦ ¸¸µé¾î °á°ú¸¦ ³»º¸³»´Â °ÍÀÌ ¸ñÀûÀÔ´Ï´Ù. ±×·¡¼­ Æû¿¡¼­´Â ¸ñ·ÏÀÇ ¿µ¿ª(Å×À̺íÀÌ µÉ ºÎºÐÀÌÁÒ)À» ¼±ÅÃÇÏ°í , ¾î´À Çʵ忡¼­ ¾î¶² °ªÀ» °¡Áø µ¥ÀÌÅ͸¸ º¹»çÇÒ °ÍÀÎÁö ¾Æ´Ï¸é À̸¦ »« ³ª¸ÓÁö¸¦ º¹»çÇÒ °ÍÀÎÁö ÁöÁ¤ÇÏ°Ô µË´Ï´Ù.

¿À´ÃÀº µ¥ÀÌÅͺ£À̽ºÆÄÀÏÀ» ¸¸µé°í, Å×À̺íÀ» ¸¸µì´Ï´Ù. ±×¸®°í ¿©±â¿¡¼­ ÁöÁ¤ÇÑ Á¶°Ç¿¡ µû¶ó Äõ¸®¸¦ ÇÏ¿© ¿öÅ©½ÃÆ®¿¡ »Ñ·ÁÁÖ´Â ÄÚµùÀ» º¸¿© ÁÙ °ÍÀÔ´Ï´Ù. ¸ÕÀú ¼Ò½º¸¦ º¸¿©µå¸®ÁÒ.

Module1
Option Explicit

Public Sub CreateDatabase(rngData As Range)
   Dim i As Byte
   Dim catCatalog As New Catalog
   Dim tblTable As ADOX.Table
   Dim fldName As String

   If Len(Dir(Application.DefaultFilePath & "\ÀÓ½Ã.mdb")) <> 0 Then
      Kill Application.DefaultFilePath & "\ÀÓ½Ã.mdb"
   End If
   catCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &     Application.DefaultFilePath & "\ÀÓ½Ã.mdb;"

   Set tblTable = New Table

   rngData.Replace What:=".", Replacement:="-"

   With tblTable
      .Name = "ÀÓ½Ã"
      For i = 1 To rngData.Columns.Count
         fldName = CStr(rngData.Item(1, i))
         .Columns.Append fldName
      Next
   End With
   catCatalog.Tables.Append tblTable

   Set tblTable = Nothing
   Set catCatalog = Nothing
End Sub

Public Sub CreateRecordset(rngData As Range)
   Dim i As Byte
   Dim r As Long
   Dim strSQL As String
   Dim cnnConnection As ADODB.Connection

   Set cnnConnection = New ADODB.Connection
   cnnConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.DefaultFilePath & "\ÀÓ½Ã.mdb"

   With cnnConnection
      For r = 2 To rngData.Rows.Count
         strSQL = "INSERT INTO Àӽà VALUES("
         For i = 1 To rngData.Columns.Count
             strSQL = strSQL & "'" & rngData.Cells(r, i) & "',"
         Next
         strSQL = Left(strSQL, Len(strSQL) - 1) & ")"
         .Execute strSQL
     Next r
    .Close
    End With

   Set cnnConnection = Nothing
End Sub

Public Sub FilterRecordset(strFld As String, strValue As String)
   Dim cnnConnection As ADODB.Connection
   Dim rstRecordSet As ADODB.Recordset
   Dim fldField As ADODB.Field
   Dim strSQL As String
   Dim strRecordset As String
   Dim strFiltering As String
   Dim r As Long
   Dim c As Byte
   Dim i As Byte

   Set cnnConnection = New ADODB.Connection
   cnnConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.DefaultFilePath & "\ÀÓ½Ã.mdb"

   Set rstRecordSet = New ADODB.Recordset
   With rstRecordSet
      .ActiveConnection = cnnConnection
      .CursorLocation = adUseClient
      .CursorType = adOpenDynamic
      strSQL = "SELECT * FROM ÀÓ½Ã"
      .Open strSQL

      If UserForm1.opCopy = True Then
         strFiltering = strFld & "='" & strValue & "'"
      ElseIf UserForm1.opReverse = True Then
         strFiltering = strFld & "<>'" & strValue & "'"
     End If

      ActiveWorkbook.Worksheets.Add

      .Filter = strFiltering
      Debug.Print "Filtered Recordset>>>>>"

      'Write the field's names on the target
      c = 1
      For Each fldField In .Fields
         ActiveSheet.Cells(1, c) = fldField.Name
         c = c + 1
      Next

      r = 2
      Do Until .EOF
         c = 1
         For Each fldField In .Fields
            ActiveSheet.Cells(r, c) = fldField.Value
            c = c + 1
         Next
         r = r + 1
         .MoveNext
      Loop
   End With

   rstRecordSet.Close
   cnnConnection.Close

   Set rstRecordSet = Nothing
   Set cnnConnection = Nothing
End Sub

¸ÕÀú ³ª¿À´Â °ÍÀÌ µ¥ÀÌÅͺ£À̽ºÆÄÀÏÀ» ¸¸µå´Â CreateDatabase()ÇÁ·Î½ÃÁ®ÀÔ´Ï´Ù. µ¥ÀÌÅͺ£À̽ºÀÚü¿¡ ´ëÇÑ Á¤ÀÇ (µ¥ÀÌÅͺ£À̽º »ý¼º, Å×À̺ê»ý¼º, Çʵå»ý¼ºµîµî) ¸¦ À§Çؼ­´Â Áö³­ ½Ã°£¿¡ °³Ã¼¸¦ ÂüÁ¶ÇÏ´Â ±×¸²¿¡¼­ º¸¾ÒµíÀÌ "Microsoft ADO Ext. 2.5 for DDL and Security"¸¦ ÂüÁ¶ÇØ¾ß ÇÕ´Ï´Ù. 

CreateDatabase( )ÇÁ·Î½ÃÁ®´Â rngData¶ó´Â ¿öÅ©½ÃÆ® ¸ñ·Ï¿µ¿ªÀ» ³Ñ°Ü¹Þ¾Æ À̸¦ Å×À̺í·Î ¸¸µé °ÍÀÔ´Ï´Ù.
Public Sub CreateDatabase(rngData As Range)
   Dim i As Byte

Ä«Å»·Î±×¶õ µ¥ÀÌÅͺ£À̽º Á¤ÀǸ¦ À§ÇÑ °³Ã¼ÀÔ´Ï´Ù.
   Dim catCatalog As New Catalog

Å×À̺íÀ» ÀǹÌÇÏ´Â Table°³Ã¼¸¦ ¼±¾÷ÇÕ´Ï´Ù.
   Dim tblTable As ADOX.Table

   Dim fldName As String

ÀÌÀü¿¡ ¸¸µç mdbÆÄÀÏÀÌ ÀÖ´Ù¸é À̹ø ÀÛ¾÷À» À§ÇØ Áö¿ö¾ß ÇÕ´Ï´Ù. ±×·¡¼­ ÀÛ¾÷ÁßÀÎ Æú´õ¿¡¼­ ÆÄÀÏÀ¯¹«¸¦ °Ë»çÇÏ°í ÀÖÀ¸¸é Áö¿ó´Ï´Ù.
   If Len(Dir(Application.DefaultFilePath & "\ÀÓ½Ã.mdb")) <> 0 Then
      Kill Application.DefaultFilePath & "\ÀÓ½Ã.mdb"
   End If

Ä«Å»·Î±× °³Ã¼¸¦ »ý¼ºÇÏ´Â ºÎºÐÀÌÁÒ. À̶§ Provider=Microsoft.Jet.OLEDB.4.0´Â ¾×¼¼½º2000¿¡¼­ Áö¿øÇÏ´Â Çü½ÄÀÔ´Ï´Ù. 97¹öÀü¿¡¼­´Â ÀÌ·¸°Ô ¸¸µç mdbÆÄÀÏÀ» ÀÐÀ» ¼ö ¾ø½À´Ï´Ù. 97¹öÀü°ú ȣȯµÇ·Á¸é 4.0´ë½Å 3.5¸¦ »ç¿ëÇÕ´Ï´Ù.
   catCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.DefaultFilePath & "\ÀÓ½Ã.mdb;"

   Set tblTable = New Table

Å×À̺íÀÇ À̸§Àº "ÀÓ½Ã"¶ó°í ÁöÁ¤ÇÕ´Ï´Ù.
   With tblTable
      .Name = "ÀÓ½Ã"
¸ñ·ÏÀÇ ¿­¸¸Å­ Çʵ带 ¸¸µé¾î ÁÝ´Ï´Ù. À̶§ ÇʵåÀ̸§Àº rngData.Item(1, i)¿Í °°ÀÌ ¹üÀ§ÀÇ Ã¹¹ø° ÇàÀÇ °ªµéÀÌ µË´Ï´Ù.
      For i = 1 To rngData.Columns.Count
         fldName = CStr(rngData.Item(1, i))

Çʵ带 Ãß°¡ÇÏ´Â ºÎºÐÀÔ´Ï´Ù. 
         .Columns.Append fldName
      Next
  
End With
Ä«Å»·Î±×°³Ã¼¿¡ ¾Õ¼­ ¸¸µç Å×ÀÌºí °³Ã¼¸¦ ºÙÀÔ´Ï´Ù.
   catCatalog.Tables.Append tblTable

   Set tblTable = Nothing
   Set catCatalog = Nothing
End Sub

CreateRecordset() ÇÁ·Î½ÃÁ®´Â ¾Õ¼­ ¸¸µç ÀÓ½Ã.mdbÆÄÀÏÀÇ "ÀÓ½Ã"¶ó´Â Å×ÀÌºí¿¡ ·¹Äڵ带 Ãß°¡ÇÏ´Â ÀÏÀ» ÇÕ´Ï´Ù.
Public Sub CreateRecordset(rngData As Range)
   Dim i As Byte
   Dim r As Long
   Dim strSQL As String

µ¥ÀÌÅͺ£À̽º ¿¬°á°³Ã¼¸¦ ¼±¾ðÇÕ´Ï´Ù.
   Dim cnnConnection As ADODB.Connection

¼±¾ðÇÑ ¿¬°á°³Ã¼¿¡ ÀÓ½Ã.mdbÆÄÀÏÀ» ¿¬°áÇÏ´Â ºÎºÐÀÔ´Ï´Ù.
   Set cnnConnection = New ADODB.Connection
   cnnConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.DefaultFilePath & "\ÀÓ½Ã.mdb"

   With cnnConnection
      For r = 2 To rngData.Rows.Count

¿µ¿ªÀÇ Çุŭ ¹Ýº¹Çϸ鼭 INSERT SQL¹®À» ¸¸µì´Ï´Ù.
         strSQL = "INSERT INTO Àӽà VALUES("
         For i = 1 To rngData.Columns.Count
            strSQL = strSQL & "'" & rngData.Cells(r, i) & "',"
         Next
         strSQL = Left(strSQL, Len(strSQL) - 1) & ")"


¿Ï¼ºÇÑ SQL ¹®À» ½ÇÇàÇÕ´Ï´Ù.
         .Execute strSQL

      Next r
      .Close
   End With

   Set cnnConnection = Nothing
End Sub

¿©±â¿¡¼­´Â »ç¿ëÀÚ°¡ ÁöÁ¤ÇÑ Çʵ忡¼­ ÁöÁ¤ÇÑ °ªÀ» ã¾Æ ÁöÁ¤ÇÑ ¹æ¹ýÀ¸·Î µ¥ÀÌÅ͸¦ ¸¸µé¾îÁÝ´Ï´Ù.

Public Sub FilterRecordset(strFld As String, strValue As String)
   Dim cnnConnection As ADODB.Connection
   Dim rstRecordSet As ADODB.Recordset
   Dim fldField As ADODB.Field
   Dim strSQL As String
   Dim strRecordset As String
   Dim strFiltering As String
   Dim r As Long
   Dim c As Byte
   Dim i As Byte

   Set cnnConnection = New ADODB.Connection
   cnnConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.DefaultFilePath & "\ÀÓ½Ã.mdb"

   Set rstRecordSet = New ADODB.Recordset
   With rstRecordSet
      .ActiveConnection = cnnConnection
      .CursorLocation = adUseClient
      .CursorType = adOpenDynamic

¸ÕÀú Å×À̺í Àüü¸¦ Á¶È¸ÇÕ´Ï´Ù.
      strSQL = "SELECT * FROM ÀÓ½Ã"
     .Open strSQL

»ç¿ëÀÚ°¡ ¼±ÅÃÇÑ "Action" Áï Copy ¶Ç´Â Reverse Copy¿¡ µû¶ó ¾Õ¿¡¼­ Á¶È¸ÇÑ ·¹Äڵ忡 ÇÊÅ͸µ Á¶°ÇÀ» ¸¸µì´Ï´Ù.
      If UserForm1.opCopy = True Then
         strFiltering = strFld & "='" & strValue & "'"
      ElseIf UserForm1.opReverse = True Then
         strFiltering = strFld & "<>'" & strValue & "'"
      End If

ActiveWorkbook.Worksheets.Add

¾Õ¿¡¼­ ¸¸µç ÇÊÅ͸µÁ¶°Ç¿¡ µû¶ó ÇÊÅ͸µÀ» ½ÇÇàÇÕ´Ï´Ù.
      .Filter = strFiltering

      Debug.Print "Filtered Recordset>>>>>"

ÇʵåÀ̸§À» Ãß°¡ÇÑ ½ÃÆ®¿¡ Ãâ·ÂÇÕ´Ï´Ù.
      c = 1
      For Each fldField In .Fields
         ActiveSheet.Cells(1, c) = fldField.Name
      c = c + 1
      Next


·¹Äڵ尪À» Ãâ·ÂÇÕ´Ï´Ù.
      r = 2
      Do Until .EOF
         c = 1
         For Each fldField In .Fields
            ActiveSheet.Cells(r, c) = fldField.Value
            c = c + 1
         Next
         r = r + 1
        .MoveNext
     Loop

   End With

   rstRecordSet.Close
   cnnConnection.Close

Set rstRecordSet = Nothing
Set cnnConnection = Nothing
End Sub

ÀÌ»óÀ¸·Î Áߺ¹µ¥ÀÌÅ͸¦ °É·¯³»´Â µµ±¸¸¦ ¸¸µé¾î º¸¾Ò½À´Ï´Ù. ±×·¯³ª »ç½Ç ¿¢¼¿ÀÇ ±â´ÉÀ» »ç¿ëÇÏ´Â °Íº¸´Ù´Â ´À¸³´Ï´Ù. ¾Æ¸¶ Àüü Å×À̺íÀ» Á¶È¸ÇÏ´Â ºÎºÐ¿¡¼­ ½Ã°£ÀÌ °É¸®´Â °Í °°½À´Ï´Ù. ¾Æ¸¶ ¿¢¼¿ÀÇ ±â´ÉÀ» »ç¿ëÇϽô °ÍÀÌ ÁÁÀ» °ÍÀÔ´Ï´Ù. ´Ù¸¸ Á¦°¡ ±»ÀÌ ÀÌ·± °É ¸¸µç °ÍÀº ADO°³Ã¼ÀÇ »ç¿ëÀ» º¸¿©µå¸®°íÀÚ ÇÏ´Â ¿¹ÀÔ´Ï´Ù. ´ÙÀ½ ½Ã°£¿¡´Â ÈξÀ ½¬¿î ÁÖÁ¦·Î ¸¸³ªº¸°Ú½À´Ï´Ù.

±×·³ ¼ö°íÇϼ¼¿ä

¸ñÂ÷ | ÀÌÀü | ´ÙÀ½

¡¡

¿ÀÇǽºÆ©ÅÍ ±â¾÷ASP¼­ºñ½º 135-880 ¼­¿ï½Ã °­³²±¸ »ï¼ºµ¿ 157-3 ¿¤ÁöÆ®À©ÅÚ 2Â÷ 1603È£ ¢Ï070-7098-2554
Copyright ¨Ï 1999-2008 Officetutor.com All rights reserved
¡¡