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
16 2673
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 : -
Do Until Selection.Value = ""
-
If Trim(Selection.Offset(0, 20).Value ) ="" Then
-
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
-
End If
-
Selection.Offset(0, 20).Value ="T"
-
Selection.Offset(1, 0).Select
-
Loop
-
Regards
Veena
@QVeen72
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
Hi,
What Macro have you written to update Cell 20....? Post it
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
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"..
@QVeen72
Thanks Veena, its working fine as u said
Thanks once again
Best Regards
Teja
@QVeen72
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.
May be 11th row has Blank record.. as you are checking for Blank in your macro...
@QVeen72
No Veena, i don't have any blank cell in my excel sheet.
Any idea why it is not updating
Regards
Teja
Let me know how are you Clearing Col-20 of each row, when editted..?
@QVeen72
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
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.
@QVeen72
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
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
@QVeen72
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
edit reply report
Hi,
It is usually written in Worksheet_Change event..
You can google... Check This
Regards
Veena
Sign in to post your reply or Sign up for a free account.
Similar topics
by: mike420 |
last post by:
I think everyone who used Python will agree that its syntax is
the best thing going for it. It is very readable and easy
for everyone to learn. But, Python does not a have very good
macro...
|
by: michele.simionato |
last post by:
Paul Rubin wrote:
> How about macros? Some pretty horrible things have been done in C
> programs with the C preprocessor. But there's a movememnt afloat to
> add hygienic macros to Python. Got any...
|
by: seberino |
last post by:
I've been reading the beloved Paul Graham's "Hackers and Painters".
He claims he developed a web app at light speed using Lisp and lots
of macros.
It got me curious if Lisp
is inherently faster...
|
by: David Powell |
last post by:
Because my work area won't have an Access programmer next year, I've
been asked to rebuild their coded application as a set of modular
tools. The idea is that a non-programmer will be able to...
|
by: Chronologic |
last post by:
All,
I have an issue I would like some expert help on.
I understand, or so I believe, that C# does not support the concept of
a "compile time macro". At least not in the sense I'm looking...
|
by: William Ahern |
last post by:
So, I recently learned that Solaris doesn't, or doesn't seem, to provide
err.h. On some platforms err.h provides simple wrappers for error printing
to stderr. One of which, err(), has this...
|
by: Robert Seacord |
last post by:
When writing C99 code is a reasonable recommendation to use inline
functions instead of macros?
What sort of things is it still reasonable to do using macros? For
example, is it reasonable to...
|
by: aaragon |
last post by:
Hi everyone, I have a simple question. I'm trying to make a macro in
one file so I can use it in main.cpp. The idea is that I the user of
my code has simple to type the macro definition to replace...
|
by: Alexander Adam |
last post by:
Hi!
I've got an issue. I have more or less an internal class model for
which I want to write various bindings e.g. for Windows COM. Now I've
figured to make my internal classes based on...
|
by: Salad |
last post by:
If I click on the Macros button in the database window there is 1 macro
that exists...AutoExec.
If I click on the Modules button and enter the VB Editor window there's
the menu option Tools. ...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |