PDA

View Full Version : Need macro to check data from workbook to another workbook



Kaniguan1969
09-20-2014, 09:19 PM
Hi Expert,
I have a requierement to check, validate and add records from workbook to another workbook.

1. working file workbook with macro codes that perform all the process. This is my main excel file with buttons.
2. The incoming workbook that contains all incoming data given by a user 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 or compare 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.

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

attached is a sample data.

Kaniguan1969
09-21-2014, 07:29 PM
Anybody could help me on how to this in vba macro or its is possible vba macro can perform this process. thank you.

Aussiebear
09-21-2014, 09:54 PM
Do a search on Google for comparing two workbooks

Kaniguan1969
09-21-2014, 10:17 PM
Hi actually ihave to compare or check multiple data, let say 4 columns of data to another workbook. i have a hard time finding this kind of requirements to goggle. can you give me a sample as my reference. thanks.

Aussiebear
09-22-2014, 10:39 AM
There are hundreds of examples on the net, which deal with comparing workbooks, worksheets, or other ranges.


Sub Compare()

Dim wbkComp As Workbook
Dim wbkWith As Workbook
Dim wbkDiff As Workbook
Dim shtComp As Worksheet
Dim shtWith As Worksheet
Dim shtDiff As Worksheet
Dim lngCompRow As Long
Dim lngDiffRow As Long
Dim blnSame As Boolean
Dim intCol As Integer

Set wbkComp = Workbooks("Book1.xls")
Set wbkWith = Workbooks("Book2.xls")
Set wbkDiff = Workbooks.Add

For Each shtComp In wbkComp.Worksheets
Application.StatusBar = "Checking " & shtComp.Name
Set shtWith = wbkWith.Worksheets(shtComp.Name)
Set shtDiff = wbkDiff.Worksheets.Add
shtDiff.Name = "Diff " & shtComp.Name
lngCompRow = 1
lngDiffRow = 1
Do While shtComp.Cells(lngCompRow, 1) <> ""
blnSame = True
For intCol = 1 To 3
If shtComp.Cells(lngCompRow, intCol) <> shtWith.Cells(lngCompRow, intCol) Then
blnSame = False
Exit For
End If
Next
If Not blnSame Then
shtComp.Rows(lngCompRow).Copy shtDiff.Cells(lngDiffRow, 1)
lngDiffRow = lngDiffRow + 1
End If
lngCompRow = lngCompRow + 1
Loop
Next
Application.StatusBar = False
End Sub

Kaniguan1969
09-23-2014, 12:43 AM
Hi AussieBear,

I resolved already the first requirement. now my concern is how could i check the data from intransit
using 4 criteria to Raw worksheet if exist i have to check the the status if "Done" check the status from intransit to "Received"
else if the status is "Not yet" the status is IN-Transit"


here is my code:

Set wb = ThisWorkbook
Set ws = wb.Sheets("Intransit_")

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

Just want to shared the code i've use to compare the data.

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.TLCSSKU, 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.TLCSSKU, 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