Excel formatting
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 |
Excel formatting
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 |
All times are GMT +1. The time now is 07:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com