Excel VBA to match and line up rows

by

The easiest way would probably be ADO. 

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * " _
       & "FROM [Sheet2$A1:J5] a " _
       & "LEFT JOIN [Sheet2$K1:N5] b " _
       & "ON a.F=b.k "

rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results

Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

More Related Contents:

  1. How to add a custom Ribbon tab using VBA?
  2. filter out multiple criteria using excel vba
  3. Closing Excel Application using VBA
  4. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  5. Cannot VBA write data to cells in Excel 2007/2010 within a function
  6. VBA equivalent to Excel’s mod function
  7. Excel: the Incredible Shrinking and Expanding Controls [closed]
  8. Excel Date Conversion from yyyymmdd to mm/dd/yyyy
  9. How to programmatically code an ‘undo’ function in Excel-Vba? [duplicate]
  10. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  11. Delete all data rows from an Excel table (apart from the first)
  12. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  13. Print function selecting wrong Type of Change option excel vba
  14. Format Cell Contents
  15. Excel formula checklist in employees
  16. How do I put double quotes in a string in vba?
  17. What is the difference between .text, .value, and .value2?
  18. How can I URL encode a string in Excel VBA?
  19. VBA rounding problem
  20. 2 ways for “ClearContents” on VBA Excel, but 1 work fine. Why? [duplicate]
  21. Excel 2013 VBA Clear All Filters macro
  22. Sum function in VBA
  23. Excel error 1004 “Unable to get …. property of WorksheetFunction class” appearing inconsistently
  24. How can I install/use “Scripting.FileSystemObject” in Excel 2010 for MAC?
  25. How to save Excel Workbook to Desktop regardless of user?
  26. How to add an attachment to an email using VBA in Excel
  27. Get Timezone Information in VBA (Excel)
  28. How to extract text within a string of text
  29. VBA: Modify chart data range
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment