Forum Discussion

ColinYoung's avatar
ColinYoung
Copper Contributor
Aug 11, 2023

Compare, copy and paste from an external worksheet.

  I'm trying to perform a search on an external worksheet then copy the lines with matching data to paste in the original worksheet.  The code I've come up with is a bit of a mess as I've tried mult...
excel
Macros and VBA
ColinYoung's avatar
ColinYoung
Copper Contributor
Aug 14, 2023

peiyezhu   I've tried debug and it returned the names stored in Workbook 1 - where the search is generated.  No matter how I set it up to search in Workbook 2, it goes to Wb1.  I thought that this should be relatively straight forward.  I can't work out what's going wrong! 

 

I haven't attached an image as it only lists the names I've identified as coming from Wb1 and, as it's based on live data, I can't share the information.

peiyezhu's avatar
peiyezhu
Bronze Contributor
Aug 14, 2023
Any errors reported?

denug.print .Sheet1.Cells(i, 1)
stop
can you share a screenshot on the breakpoint?
  • ColinYoung's avatar
    ColinYoung
    Copper Contributor
    Aug 16, 2023

    peiyezhu mathetes 

     

    Thanks for all your help.  I binned the original code and solved the issue with SQL.  Now on to the next problem...!  In case it'll help anyone else, here's my solution.

    Private Sub cmbSearch_Click()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim SSearch As String
    
    SSearch = (txtSearch.Text)
      
    Sheets("DailySearch").Range("A1:Z100").ClearContents
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=\\DUCH-3900\Environment\ENV-SYS2\RSAFETY\SCHOOL CROSSING PATROL GROUP\SCP OPS Logs\2023 LOGS\TestData\Daily Log 2023.xlsx;" & _
        "Extended Properties='Excel 12.0 Xml;HDR=No';"
    
        
    cn.Open
    
    Set rs = New ADODB.Recordset
      
        rs.ActiveConnection = cn
        rs.Source = "SELECT * FROM [Jan$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [Feb$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [Mar$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [April$] WHERE [F3] = '" & SSearch & "'"
        rs.Source = "SELECT * FROM [May$] WHERE [F3] = '" & SSearch & "'"
        
        'rs.Source = "SELECT * FROM [June$] WHERE [F3] = '" & SSearch & "'"
        'rs.Source = "SELECT * FROM [July$] WHERE [F3] = '" & SSearch & "'"
        'rs.Source = "SELECT * FROM [Aug$] WHERE [F3] = '" & SSearch & "'"
        'rs.Source = "SELECT * FROM [Sept$] WHERE [F3] = '" & SSearch & "'"
        'rs.Source = "SELECT * FROM [Oct$] WHERE [F3] = '" & SSearch & "'"
        'rs.Source = "SELECT * FROM [Nov$] WHERE [F3] = '" & SSearch & "'"
        'rs.Source = "SELECT * FROM [Dec$] WHERE [F3] = '" & SSearch & "'"

        rs.Open
        
        Sheet7.Range("A1").CopyFromRecordset rs
        Sheet7.Range("A1").CurrentRegion.EntireColumn.AutoFit
   
    rs.Close
    cn.Close
       
    DLogDisplay.ColumnCount = 18
    DLogDisplay.RowSource = "DailySearch!B1: AD100"

End Sub

Resources