I have an excel table which has old and new values for Vendors

So my excel file looks like this- It actually has about 5000+ values:

Old Vendor ID New Vendor ID
ADPINC00 ADPEAS00'
THC TYCHEA
UNHOSE UNIHOS
USS UNISTA


I have to run SQL scripts on my database which will update the value of the
Old Vendor ID with the new Vendor ID
on 5 tables and delete the old vendor ID from one table =Actual e.g below:

update apdoc set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aptran set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aphist set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update apadjust set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update gltran set Id = 'ADPINC00' where Id = 'ADPEAS00' and module = 'GL';
delete from aphist where vendid = 'ADPEAS00'


I would be really grateful if someone could suggest a mechanism to create
the scripts as above from my excel file

Thank you

S Commar

Dim LastRow As Long
Dim SQLString As String
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

SQLString = "update apdoc set vendid = '" & .Cells(i, "B").Value
& "' " & _
"where vendid = '" & .Cells(i, "B").Value & "';" & _
"update aptran set vendid = '" & .Cells(i,
"B").Value & "' " & _
"where vendid = '" & .Cells(i, "B").Value & "';" & _
"update aphist set vendid = '" & .Cells(i,
"B").Value & "' " & _
"where vendid = '" & .Cells(i, "B").Value & "';" & _
"update apadjust set vendid = '" & .Cells(i,
"B").Value & "' " & _
"where vendid = '" & .Cells(i, "B").Value & "';" & _
"update gltran set Id = '" & .Cells(i, "B").Value &
"' " & _
"where vendid = '" & .Cells(i, "B").Value & "';" & _
"delete from aphist where vendid = " & .Cells(i,
"B").Value & "'"
'process it
Next i
End With


--
__________________________________
HTH

Bob

"Sam Commar" wrote in message
...
I have an excel table which has old and new values for Vendors

So my excel file looks like this- It actually has about 5000+ values:

Old Vendor ID New Vendor ID
ADPINC00 ADPEAS00'
THC TYCHEA
UNHOSE UNIHOS
USS UNISTA


I have to run SQL scripts on my database which will update the value of
the Old Vendor ID with the new Vendor ID
on 5 tables and delete the old vendor ID from one table =Actual e.g
below:

update apdoc set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aptran set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aphist set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update apadjust set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update gltran set Id = 'ADPINC00' where Id = 'ADPEAS00' and module = 'GL';
delete from aphist where vendid = 'ADPEAS00'


I would be really grateful if someone could suggest a mechanism to create
the scripts as above from my excel file

Thank you

S Commar