VBA Macros issue

[teja1234]

I wrote a macro script which will upload excel data into orcale database also it generate excel sheet in othet location and its working fine, but whenever i run the macro it will take all rows from excel sheet and updated database so i am getting same data again and again.
Please can any one help me how to update only changed rows from excel to database.

Below is my code:

Sub AppendOracleTable()

'Create and set login information variables
Dim logon As String
    Let logon = "scott"
Dim password As String
    Let password = "tiger"

'Set Variables for Upload
Dim Cust_id As String
Dim Cust_Name As String
Dim Product As String
Dim Order_No As String

Dim Wkb2 As Workbook

' Specify the location from which excel file loading, open the workbook you are copying from and activate it
Set Wkb2 = Workbooks.Open(Filename:="C:\Documents and Settings\admin\Desktop\Excel__Macro\Input_File.xls")
Wkb2.Activate

'Create and Set Session / Create Dynaset = Column Names
Dim OraSession As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Dim Host_name As String
Host_name = "orcl"
Dim OraDatabase As Object
Set OraDatabase = OraSession.OpenDatabase("" & Host_name & "", "" & logon & " / " & password & "", 0&)
Dim Oradynaset As Object
Set Oradynaset = OraDatabase.DBCreateDynaset("SELECT * FROM Cust_tab", 0&)
    
Range("A2").Select
    Do Until Selection.Value = ""
        Cust_id = Selection.Value
        Cust_Name = Selection.Offset(0, 1).Value
        Product = Selection.Offset(0, 2).Value
        Order_No = Selection.Offset(0, 3).Value
        Oradynaset.AddNew
        Oradynaset.Fields("Cust_id").Value = Cust_id
        Oradynaset.Fields("Cust_Name").Value = Cust_Name
        Oradynaset.Fields("Product").Value = Product
        Oradynaset.Fields("Order_No").Value = Order_No
        Oradynaset.Update
        Selection.Offset(1, 0).Select
    Loop
    Range("A1:D10").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    'Range("D15").Select
    Application.CutCopyMode = False
    
    ' Location where we need to save new Excel sheet
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\admin\Desktop\Excel__Macro\Output_File.xls", FileFormat:= _
        xlNormal, password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
    ActiveWindow.Close
End Sub

Thanks in Advance

Teja

[QVeen72]

Hi,

OK.. I will just give you the logic, and take care of coding...

In One Column, say column 20, Save Upload Status of the row..,
initially, it will be blank..
upload the data for if that column is blank.. and immediately make that Cell value = "T" or something...
next time when you Upload upload the data for if that column is blank..

In Excel, write macro for the rest of the columns, so that if any column is changed, then make the column 20's cell blank...


so the code would change to :

[code=vb]
Do Until Selection.Value = ""
If Trim(Selection. Offset(0, 20).Value ) ="" Then
Cust_id = Selection.Value
Cust_Name = Selection.Offse t(0, 1).Value
Product = Selection.Offse t(0, 2).Value
Order_No = Selection.Offse t(0, 3).Value
Oradynaset.AddN ew
Oradynaset.Fiel ds("Cust_id").V alue = Cust_id
Oradynaset.Fiel ds("Cust_Name") .Value = Cust_Name
Oradynaset.Fiel ds("Product").V alue = Product
Oradynaset.Fiel ds("Order_No"). Value = Order_No
Oradynaset.Upda te
End If
Selection.Offse t(0, 20).Value ="T"
Selection.Offse t(1, 0).Select
Loop
[/code]

Regards
Veena

[teja1234]

Hi,

OK.. I will just give you the logic, and take care of coding...

In One Column, say column 20, Save Upload Status of the row..,
initially, it will be blank..
upload the data for if that column is blank.. and immediately make that Cell value = "T" or something...
next time when you Upload upload the data for if that column is blank..

In Excel, write macro for the rest of the columns, so that if any column is changed, then make the column 20's cell blank...


so the code would change to :

[code=vb]
Do Until Selection.Value = ""
If Trim(Selection. Offset(0, 20).Value ) ="" Then
Cust_id = Selection.Value
Cust_Name = Selection.Offse t(0, 1).Value
Product = Selection.Offse t(0, 2).Value
Order_No = Selection.Offse t(0, 3).Value
Oradynaset.AddN ew
Oradynaset.Fiel ds("Cust_id").V alue = Cust_id
Oradynaset.Fiel ds("Cust_Name") .Value = Cust_Name
Oradynaset.Fiel ds("Product").V alue = Product
Oradynaset.Fiel ds("Order_No"). Value = Order_No
Oradynaset.Upda te
End If
Selection.Offse t(0, 20).Value ="T"
Selection.Offse t(1, 0).Select
Loop
[/code]

Regards
Veena

Hi Veena, Thanks for your reply
I tried your tips but its not working for me, When my excel sheet updated say with 2 rows when i run macro it has to updated only that two rows into database but its not updating. If i use my code it is updated whole data from excel sheet again.

Please any other Tips please share

Thanks
Teja

[QVeen72]

Hi,

What Macro have you written to update Cell 20....? Post it

[teja1234]

Veena,
I have a problem with updating the data from excel into oracle database, whenever i am running a macro each time it is updating whole sheet data, but i want to updated only the rows which ever added newly with respect to previous macro run.

I don't have ploblem with column updation.

So please give any idea this

Thanks
Teja

[QVeen72]

Hi,

READ MY PREV POST CAREFULLY...
Save Status of Update in Col-20,
Whenever u make changes in cols 1-10, Clear Col-20..
and next time when u upload data, check Col-20, Update only if it is blank.. and after update, set its value ="T"..

[teja1234]

Hi,

READ MY PREV POST CAREFULLY...
Save Status of Update in Col-20,
Whenever u make changes in cols 1-10, Clear Col-20..
and next time when u upload data, check Col-20, Update only if it is blank.. and after update, set its value ="T"..

Thanks Veena, its working fine as u said

Thanks once again

Best Regards
Teja

[teja1234]

Hi,

READ MY PREV POST CAREFULLY...
Save Status of Update in Col-20,
Whenever u make changes in cols 1-10, Clear Col-20..
and next time when u upload data, check Col-20, Update only if it is blank.. and after update, set its value ="T"..

Hi Veena, I have one more issue. I tried your tips and i was working fine for less the 10 rows but when i enter more the 10 row in excel sheet this macro is not updating excel data into orcale database can you please tell why it is not updating.

[QVeen72]

May be 11th row has Blank record.. as you are checking for Blank in your macro...

[teja1234]

May be 11th row has Blank record.. as you are checking for Blank in your macro...

No Veena, i don't have any blank cell in my excel sheet.

Any idea why it is not updating

Regards
Teja

[QVeen72]

Let me know how are you Clearing Col-20 of each row, when editted..?

[teja1234]

Let me know how are you Clearing Col-20 of each row, when editted..?

Hi Veena,

I am not sure what u r asking, i am using following code.

Sub AppendOracleTable()

'Create and set login information variables
Dim logon As String
    Let logon = "scott"
Dim password As String
    Let password = "tiger"

'Set Variables for Upload
Dim Emp_id As String
Dim Emp_Name As String
Dim Salary As String
Dim Dept As String

Dim Wkb2 As Workbook

' Specify the location from which excel file loading, open the workbook you are copying from and activate it
Set Wkb2 = Workbooks.Open(Filename:="C:\Documents and Settings\admin\Desktop\Aezaz\Test_macro\Macro_in.xls")
Wkb2.Activate

'Create and Set Session / Create Dynaset = Column Names
Dim OraSession As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Dim Host_name As String
Host_name = "orcl"
Dim OraDatabase As Object
Set OraDatabase = OraSession.OpenDatabase("" & Host_name & "", "" & logon & " / " & password & "", 0&)
Dim Oradynaset As Object
Set Oradynaset = OraDatabase.DBCreateDynaset("SELECT * FROM Employee", 0&)
    
Range("A2").Select
Do Until Selection.Value = ""
   If Trim(Selection.Offset(0, 20).Value) = "" Then
        Emp_id = Selection.Value
        Emp_Name = Selection.Offset(0, 1).Value
        Salary = Selection.Offset(0, 2).Value
        Dept = Selection.Offset(0, 3).Value
        Oradynaset.AddNew
        Oradynaset.Fields("Emp_id").Value = Emp_id
        Oradynaset.Fields("Emp_Name").Value = Emp_Name
        Oradynaset.Fields("Salary").Value = Salary
        Oradynaset.Fields("Dept").Value = Dept
        Oradynaset.Update
   End If
   Selection.Offset(0, 20).Value = "T"
   Selection.Offset(1, 0).Select
Loop
    Range("A1:D11").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    'Range("D15").Select
    Application.CutCopyMode = False
    
    ' Location where we need to save new Excel sheet
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\admin\Desktop\Aezaz\Test_macro\Macro_Out.xls", FileFormat:= _
        xlNormal, password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
    ActiveWindow.Close
End Sub

Please let me know am i wrong some where.

Thanks in Advance
Teja

[QVeen72]

Hi,

OK.. u are saving Update Status of Each row, in That row's Column-20..
so when you edit Column's 1 to 19 columns of that row, clear Col-20..
To do this, you need to write another macro..

And then in ur upload macro, check for Col-20 of each row, upload only if blank..

Any way, If u cant understand the logic.. leave it.

[teja1234]

Hi,

OK.. u are saving Update Status of Each row, in That row's Column-20..
so when you edit Column's 1 to 19 columns of that row, clear Col-20..
To do this, you need to write another macro..

And then in ur upload macro, check for Col-20 of each row, upload only if blank..

Any way, If u cant understand the logic.. leave it.

Thanks for quick reply,

If you can give me any idea or help me to get code for clearing cell when row 1 to 19 updated, it will be great help.

Thanks
Teja

[teja1234]

Hi Veena,

Kindly if you have any sample code to clear cell 20 whenever the row updated or changed. Please share with me.

Thanks in Advance
Teja