locked
Vba macro to check records from working file to other workbooks RRS feed

  • Question

  • Hi,

    I have a requierements to check, validate  and add records from workbook to another workbook.

    here is the concept or process. i have 3 workbooks

    1.  working file workbook with macro codes that perform all the process.

    2. called incoming workbook that contains all incoming data given by group that handle the incoming materials its a fresh data. 3. status summary workbook this is coming from other group that contains records monitor if it is already transacted in  the system. the status column that contains Done and Not yet.

    My requirements is

    1. how could i the check the data from workingfile workbook if that particular tracking#, model, total and labels is exist in Status summary workbook. if exist i have to validate if the status is "Not yet" have to change the status at working file workbook as INTRANSIT" while if the status is "Done" change it with "RECIEVED".

    2. the incoming data will be dump to working file workbook at the last empty row. before that i have to check if the tracking number is already exist to working file, if exist nothing will happen but if not yet exist i have to dump those tracking no and others details to working file workbook.

    3. the same process with number 1 to check the records if exist and validate the status. if done or not yet.

    Hoping the requirements is clear and well defined. Thank you very much.

    Btw, just created a sample data for these 3 workbook into one workbook under 3 worksheet as reference.

    attached is a sample data.

    https://onedrive.live.com/redir?resid=9FB40B7B92892EF1!117&authkey=!AASN_BlxFaBP_PQ&ithint=file%2cxlsx


    • Edited by Lenoj Saturday, September 20, 2014 3:23 AM
    Saturday, September 20, 2014 3:16 AM

Answers

  • Hi,

    >>how could i the check the data from workingfile workbook if that particular tracking#, model, total and labels is exist in Status summary workbook.

    i'm trying to modify your codes becuase it has only the tracking # as criteria in searching the records from other workbook.<<

    To achieve the goal, we need to filter tracking#, model, total and labels columns in the code to pick out the matched record.

    But as you posted in the last reply, I think using ADODB connection to run a SQL statement may be more effective. We can use a SQL query to get the status from the matched record in Sheet "Status Summary", then we can edit the Status column in Sheet "WorkingFile".

    I edit the code as followed. But note that, to run the code, we have to edit the column "Tracking No." to "Tracking No" and add the reference of Microsoft ActiveX Data Object x.x Library.

    Sub Evaluate()
        Dim wsWork As Worksheet
        Dim wsSource As Worksheet
        
        Dim r As Long
        Dim tracking As String
        Dim model As String
        Dim qty As String
        Dim labels As String
        Dim status As Integer
        
        Set wsWork = ActiveWorkbook.Sheets("WorkingFile")
        Set wsSource = ActiveWorkbook.Sheets("Status Summary")
        
        r = wsWork.Range("A" & wsWork.Rows.Count).End(xlUp).Row
        
        For i = 2 To r
                'Get Tranking #
                tracking = wsWork.Cells(i, 1).Value
                labels = wsWork.Cells(i, 2).Value
                qty = wsWork.Cells(i, 3).Value
                model = wsWork.Cells(i, 5).Value
                status = GetStatus(tracking, model, qty, labels)
                If status > 0 Then
                    wsWork.Cells(i, 6).Value = "RECEIVED"
                Else
                    wsWork.Cells(i, 6).Value = "INTRANSIT"
                End If
                
        Next
        
    End Sub
    
    
    Function GetStatus(trackingNo As String, model As String, qty As String, label As String) As Integer
    Dim i As Integer
    
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    cn.Open strCon
    
    sqlstr = "SELECT * "
    sqlstr = sqlstr & " FROM [Status Summary$] e "
    sqlstr = sqlstr & " WHERE e.[Tracking No]='" & trackingNo & "'"
    sqlstr = sqlstr & " AND e.Model='" & model & "'"
    sqlstr = sqlstr & " AND e.Total=" & qty
    sqlstr = sqlstr & " AND e.Labels='" & label & "'"
    sqlstr = sqlstr & " AND e.[STATUS]='Not yet'"
    
    rs.Open sqlstr, cn
    
    If rs.EOF Then i = 1
    
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    GetStatus = i
    
    End Function


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Lenoj Wednesday, September 24, 2014 6:09 AM
    Tuesday, September 23, 2014 12:02 PM
  • Hi,

    >>It's okay if the data to process in workingfile is all INTRANSIT only?<<

    If you just want to look up all "INTRANSIT" lines, you can use AutoFilter method to filter all "Status" as "Intransit" in the "WorkingFile" worksheet and then loop through every unhidden row. It is same as the code I used in my original reply in thread Check/Evaluate column value if exist from other workbook in Excel VBA. You can refer to that and edit the code above to achieve the goal.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Lenoj Thursday, September 25, 2014 9:01 AM
    Thursday, September 25, 2014 2:38 AM

All replies

  • any idea guys on how to do this in vba macro or is this can be doable in vba macro. thanks.

    Monday, September 22, 2014 2:20 AM
  • Hi,

    According to your description, I find that you have post the detail requirements in the threads below some days ago, and some community members of this forum have shared they knowledge and explained the technical points for you. I think you can write an entire macro code based on the suggestions and sample code in the threads below.

    Requirement 1:

    Check/Evaluate column value if exist from other workbook in Excel VBA.

    Requirement 2:

    Excel VBA insert copied records to last not empty rows

    By the way, this is a free technical forum, all the community members are glad to help with all technical questions. But I think your requirement has much business logic instead of technical points. If you get any technical issues during developing VBA macro, please feel free to let us to know.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 22, 2014 7:43 AM
  • Hi Luna,

    Thank your for understanding. I tried your solution and it has only 1 data to compare ( tracking#) but i need to compare multiple data, i thnk 4 columns (Tracking Number, Model, labels and qty) to check to another workbook. thanks.



    • Edited by Lenoj Monday, September 22, 2014 8:47 AM
    Monday, September 22, 2014 8:46 AM
  • Hi Luna,

    Here is my solution to evaluate or check data from workbook to another workbook. he process is if the records from data source is not found from main workbook, the data is added. one of the requirement is already resolved. i'm trying to modify your codes becuase it has only the tracking # as criteria in searching the records from other workbook. what if i will add 3 more criteria in your solution, may i ask your help on how to do this. thanks.

        Dim con As Object: Set con = CreateObject("ADODB.Connection")
        Dim rec As Object: Set rec = CreateObject("ADODB.Recordset")
        Dim datasource As String
        datasource = ThisWorkbook.FullName
    
        Dim sconnect As String
        sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & datasource & ";" & _
                    "Extended Properties=""Excel 12.0;HDR=YES"";"
        con.Open sconnect: DoEvents
    
        Dim sqlstr As String
    
        sqlstr = "SELECT e.TrackingNo, e.SKU, e.QTY, e.ETA "
        sqlstr = sqlstr & "FROM [TempTable$] e "
        sqlstr = sqlstr & "INNER JOIN [Intransit_$] u "
        sqlstr = sqlstr & "ON e.TrackingNo <> u.TrackingNo "
        sqlstr = sqlstr & "GROUP BY e.TrackingNo, e.SKU, e.QTY, e.ETA"
    
        rec.Open sqlstr, con, 3, 1: DoEvents
    
    '   copy data to intransit
        Dim lrow As Long
        With Sheet4
            .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rec
        End With

    Your solution

       targetfile = mypath & latestFile
        Set wbSource = Workbooks.Open(targetfile)
        Set wsSource = wbSource.Sheets("raw")       'Source Data
        
        wsSource.AutoFilterMode = False
        
        lrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
        r = wsSource.UsedRange.Rows.Count
        
        For i = 2 To r
            If wsSource.Rows(i).Hidden = False Then
                'Get Tranking #
                tracking = wsSource.Cells(i, 1).Value
                model = wsSource.Cells(i, 3).Value
                sku = wsSource.Cells(i, 4).Value
                qty = wsSource.Cells(i, 5).Value
                
                'Find matched Tracking #, model sku and qty in Source sheet
                Set rng = ws.UsedRange.Find(What:=tracking, LookAt:=xlWhole)
                If Not rng Is Nothing Then
    
                   'Tracking #, sku, model, qty is found  
                   trxn = Trim(rng.Offset(0, 2).Value)
                 
                   'If the System status is Done, set the status of workingfile as "RECEIVED",
                   'If status is not yet, set the status of workingfile as "INTRaNSIT"
                   'If trxn = "Done" Then ws.Cells(i, 6).Value = "RECEIVED"
                End If
            End If
        Next

    Tuesday, September 23, 2014 7:03 AM
  • Hi,

    >>how could i the check the data from workingfile workbook if that particular tracking#, model, total and labels is exist in Status summary workbook.

    i'm trying to modify your codes becuase it has only the tracking # as criteria in searching the records from other workbook.<<

    To achieve the goal, we need to filter tracking#, model, total and labels columns in the code to pick out the matched record.

    But as you posted in the last reply, I think using ADODB connection to run a SQL statement may be more effective. We can use a SQL query to get the status from the matched record in Sheet "Status Summary", then we can edit the Status column in Sheet "WorkingFile".

    I edit the code as followed. But note that, to run the code, we have to edit the column "Tracking No." to "Tracking No" and add the reference of Microsoft ActiveX Data Object x.x Library.

    Sub Evaluate()
        Dim wsWork As Worksheet
        Dim wsSource As Worksheet
        
        Dim r As Long
        Dim tracking As String
        Dim model As String
        Dim qty As String
        Dim labels As String
        Dim status As Integer
        
        Set wsWork = ActiveWorkbook.Sheets("WorkingFile")
        Set wsSource = ActiveWorkbook.Sheets("Status Summary")
        
        r = wsWork.Range("A" & wsWork.Rows.Count).End(xlUp).Row
        
        For i = 2 To r
                'Get Tranking #
                tracking = wsWork.Cells(i, 1).Value
                labels = wsWork.Cells(i, 2).Value
                qty = wsWork.Cells(i, 3).Value
                model = wsWork.Cells(i, 5).Value
                status = GetStatus(tracking, model, qty, labels)
                If status > 0 Then
                    wsWork.Cells(i, 6).Value = "RECEIVED"
                Else
                    wsWork.Cells(i, 6).Value = "INTRANSIT"
                End If
                
        Next
        
    End Sub
    
    
    Function GetStatus(trackingNo As String, model As String, qty As String, label As String) As Integer
    Dim i As Integer
    
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    cn.Open strCon
    
    sqlstr = "SELECT * "
    sqlstr = sqlstr & " FROM [Status Summary$] e "
    sqlstr = sqlstr & " WHERE e.[Tracking No]='" & trackingNo & "'"
    sqlstr = sqlstr & " AND e.Model='" & model & "'"
    sqlstr = sqlstr & " AND e.Total=" & qty
    sqlstr = sqlstr & " AND e.Labels='" & label & "'"
    sqlstr = sqlstr & " AND e.[STATUS]='Not yet'"
    
    rs.Open sqlstr, cn
    
    If rs.EOF Then i = 1
    
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    GetStatus = i
    
    End Function


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Lenoj Wednesday, September 24, 2014 6:09 AM
    Tuesday, September 23, 2014 12:02 PM
  • Thank you very much Luna Zhang.
    Wednesday, September 24, 2014 6:09 AM
  • Hi Luna, It's okay if the data to process in workingfile is all INTRANSIT only? I tried the codes and it start to process from the first records.

    Wednesday, September 24, 2014 6:31 AM
  • Hi,

    >>It's okay if the data to process in workingfile is all INTRANSIT only?<<

    If you just want to look up all "INTRANSIT" lines, you can use AutoFilter method to filter all "Status" as "Intransit" in the "WorkingFile" worksheet and then loop through every unhidden row. It is same as the code I used in my original reply in thread Check/Evaluate column value if exist from other workbook in Excel VBA. You can refer to that and edit the code above to achieve the goal.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Lenoj Thursday, September 25, 2014 9:01 AM
    Thursday, September 25, 2014 2:38 AM
  • Hi Luna Zhang,

    Running the last vba codes you have given, i notice it takes time to process. the records that i have in working file is 10,000 records when i filtered the records with "IN-TRANSIT" it has only 1,000 while my temptable has a total records of 1983.   

    while running the codes when i checked the desired result i'm looking for is performed. basically it gives the right result. the issue is the time.

    Btw, i tried your other codes Check/Evaluate column value if exist from other workbook in Excel VBA. so far it is working, my issue concern is it has only 1 criteria used which is the trackingNo. May i ask your help to modify the codes to add additional criteria let say  "labels" and  "qty"..

    THank you very much for your continous support. I gain a lot new ideas from  youir codes. thank you for sharing.

    Thursday, September 25, 2014 9:25 AM
  • >>my issue concern is it has only 1 criteria used which is the trackingNo. May i ask your help to modify the codes to add additional criteria let say  "labels" and "qty".

    I think you can use auto filter to edit the code by yourself. You need to add filter 4 times to find the matched status to instead of using Find method in Luna's code.

    Thursday, September 25, 2014 9:39 AM
  • Hi,

    I made modification on Luna's code. Ween running the code i got an error, it says "Wrong number of arguments or invalid property assignment" . it point to Evaluate function. May i know what is wrong with the codes, any idea guys. thanks.

    Below is vba code.

    Sub UpdateStatus()
        Dim wb As Workbook
        Dim ws As Worksheet, wsSearchin As Worksheet
        
        Set wb = ThisWorkbook
        Set ws = wb.Sheets("Intransit_")
        Set wsSearchin = wb.Sheets("CoresStatus")
        
        If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
        ws.UsedRange.AutoFilter Field:=6, Criteria1:="IN-TRANSIT"
         
        'Change the Index and SearchIn ranges here..    Index = "CoresStatus!D2:D5000" contains "date" and "TBA"
        SearchIn = "CoresStatus!B2:B5000&" & _
                   "CoresStatus!F2:F5000&" & _
                   "CoresStatus!D2:D5000"
        For r = 2 To ws.Cells(Rows.Count, "A").End(xlUp).row
            If ws.Rows(r).Hidden = False Then
                SearchFor = Join(Array(ws.Cells(r, "A"), ws.Cells(r, "B"), ws.Cells(r, "C")), "")
                
                'got an error in this portion
                status = Evaluate("INDEX(" & Index & ",MATCH(" & """" & SearchFor & """" & ",INDEX(" & SearchIn & ",),0))")
            
                If IsError(status) Then
                    If status = CVErr(xlErrNA) Then ws.Cells(r, "F") = "IN-TRANSIT"
                ElseIf status = "Not Yet" Then
                    Cells(r, "F") = "IN-TRANSIT"
                Else
                    Cells(r, "F") = "RECEIVED"
                End If
            End If
        Next
    End Sub


    • Edited by Lenoj Wednesday, October 1, 2014 1:21 AM
    Wednesday, October 1, 2014 1:19 AM