By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,594 Members | 2,062 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,594 IT Pros & Developers. It's quick & easy.

Insert the records in excel sheet

100+
P: 198
can any one help by providing the method inserting the records in excel file
because i created it all it works fine but i have no idea about how to insert new records in the existing excel file.
e.g
my excel sheet has filled first 20 rows and then
i want to insert the new records in the excel sheet starts from 21st row
is any idea about this, please send me
thanks
Nov 24 '07 #1
Share this Question
Share on Google+
14 Replies


daniel aristidou
100+
P: 491
can any one help by providing the method inserting the records in excel file
because i created it all it works fine but i have no idea about how to insert new records in the existing excel file.
How are you inserting the data into the excel sheet and which version of excel are you using? Also where is the data being taken from?
Nov 24 '07 #2

kadghar
Expert 100+
P: 1,295
can any one help by providing the method inserting the records in excel file
because i created it all it works fine but i have no idea about how to insert new records in the existing excel file.
e.g
my excel sheet has filled first 20 rows and then
i want to insert the new records in the excel sheet starts from 21st row
is any idea about this, please send me
thanks
hi, i think that you'll have to open the excel file and insert the rows you want..
it aint that hard:

Expand|Select|Wrap|Line Numbers
  1. dim obj1 as object
  2. set obj1 = createobject("excel.application")
  3. obj1.workbooks.open "c:\myfile.xls"
  4. obj1.cells(21,1).value = "This is row 21"
after you do this, i recommend you to make visible the obj1 and then close it. sometimes when you close it while not visible it generates some trash. and of course, save it so it'll keep the changes... use obj1.visible = true, obj1.activeworkbook.saveas, etc.. commands are quite intuitive

HTH
Nov 24 '07 #3

100+
P: 198
hello expert
i am inserting the data from sql server and my excel version is 97
i tell you what is about my programe
i have five field in my excel sheet and i want to insert the data in excel sheet daily through programing, firstly i open the file and insert data and then save it next time i also want to insert the data into the same sheet continue from the previous end row . If i manually change the row and column name then it works fine but i have no idea how i insert the data so that it automatically choose the last empty row and insert data please send me some coding
My programing is

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("C:\varinder.xls")
oExcel.UserControl = True
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A5").Value = "delhi"
oSheet.Range("B5").Value = "jammu"
Save the Workbook and Quit Excel
oBook.SaveAs "C:\varinder.xls"
oExcel.Quit
thanks


How are you inserting the data into the excel sheet and which version of excel are you using? Also where is the data being taken from?
Nov 26 '07 #4

100+
P: 198
hello expert
you send me the code which i have to do manually but i want a general formula so that every time when i execute the programe it automatically search the sheet find the empty row and insert the data

thanks


hi, i think that you'll have to open the excel file and insert the rows you want..
it aint that hard:

Expand|Select|Wrap|Line Numbers
  1. dim obj1 as object
  2. set obj1 = createobject("excel.application")
  3. obj1.workbooks.open "c:\myfile.xls"
  4. obj1.cells(21,1).value = "This is row 21"
after you do this, i recommend you to make visible the obj1 and then close it. sometimes when you close it while not visible it generates some trash. and of course, save it so it'll keep the changes... use obj1.visible = true, obj1.activeworkbook.saveas, etc.. commands are quite intuitive

HTH
Nov 26 '07 #5

kadghar
Expert 100+
P: 1,295
hello expert
you send me the code which i have to do manually but i want a general formula so that every time when i execute the programe it automatically search the sheet find the empty row and insert the data

thanks
i'd use a DO or a WHILE to search it, once you've created the Excel Application, instead of insert the data in the cell A21, you can do it this way:

Expand|Select|Wrap|Line Numbers
  1. dim i as long
  2. i=1
  3. while obj1.cells(i,1).value <> ""
  4.     i=i+1
  5. wend
  6. obj1.cells(i,1).value = "This is the first empty cell"
HTH
Nov 26 '07 #6

100+
P: 198
thanks very much expert it is working , if i need any help recording this project i will connect you latter
varinder


i'd use a DO or a WHILE to search it, once you've created the Excel Application, instead of insert the data in the cell A21, you can do it this way:

Expand|Select|Wrap|Line Numbers
  1. dim i as long
  2. i=1
  3. while obj1.cells(i,1).value <> ""
  4.     i=i+1
  5. wend
  6. obj1.cells(i,1).value = "This is the first empty cell"
HTH
Nov 26 '07 #7

100+
P: 198
hello expert
is there any method to repeate one column value again and again in vb with orher columns value changes during every execution
e.g
i have some operator code which i want to repeate and then compare these codes with the codes that i am accessing from sql table.
thanks



i'd use a DO or a WHILE to search it, once you've created the Excel Application, instead of insert the data in the cell A21, you can do it this way:

Expand|Select|Wrap|Line Numbers
  1. dim i as long
  2. i=1
  3. while obj1.cells(i,1).value <> ""
  4.     i=i+1
  5. wend
  6. obj1.cells(i,1).value = "This is the first empty cell"
HTH
Nov 26 '07 #8

kadghar
Expert 100+
P: 1,295
hello expert
is there any method to repeate one column value again and again in vb with orher columns value changes during every execution
e.g
i have some operator code which i want to repeate and then compare these codes with the codes that i am accessing from sql table.
thanks
yeap, when i use CELLS( I, J ) I is for rows, J is for columns. you can save some value into a string or a double and then use it as you want. for example if you want to compare cell A1 with each cell in B1:B5, and show if they're the same in C1:C5 just:

Expand|Select|Wrap|Line Numbers
  1. dim a as string
  2. dim i as integer
  3. with obj1
  4.     a = .cells(1,1).value
  5.     for i = 1 to 5
  6.         if .cells(i,2).value = a then .cells(i,3) = "YES"
  7.     next
  8. end with
HTH
Nov 26 '07 #9

100+
P: 198
hello expert
i used your code but i want the different result
e,g In string " a" i have the value 702 and i want to search this value in the whole Ist column and if matched the corresponding record i,e 12 must be shown in the next column of same row

please provide some method
thanks



yeap, when i use CELLS( I, J ) I is for rows, J is for columns. you can save some value into a string or a double and then use it as you want. for example if you want to compare cell A1 with each cell in B1:B5, and show if they're the same in C1:C5 just:

Expand|Select|Wrap|Line Numbers
  1. dim a as string
  2. dim i as integer
  3. with obj1
  4.     a = .cells(1,1).value
  5.     for i = 1 to 5
  6.         if .cells(i,2).value = a then .cells(i,3) = "YES"
  7.     next
  8. end with
HTH
Dec 12 '07 #10

kadghar
Expert 100+
P: 1,295
hello expert
i used your code but i want the different result
e,g In string " a" i have the value 702 and i want to search this value in the whole Ist column and if matched the corresponding record i,e 12 must be shown in the next column of same row

please provide some method
thanks
Well, i dont thik providing methods will be the best way, why dont you give it a try, that you're asking can be solved by slightly changing the code before, just change the range of the FOR, you might use END FOR, or even replace it with a DO.

Good luck there.
Dec 12 '07 #11

100+
P: 198
hello expert
i changed the range of for loop and it works
thanks




Well, i dont thik providing methods will be the best way, why dont you give it a try, that you're asking can be solved by slightly changing the code before, just change the range of the FOR, you might use END FOR, or even replace it with a DO.

Good luck there.
Dec 13 '07 #12

100+
P: 170
hm...sorrie both guys

hm..i came across this threadd and found it similar to my problem
but i'm using perl now and my data outputs to an excel file
the module i'm using is win32::OLE
if i use the same algorithms will it work?
cos i have no idea how i can open this file in perl..pls advise below is my code for ya to see thanks~

Expand|Select|Wrap|Line Numbers
  1.  
  2.     use Win32::OLE;
  3.  
  4.         # use existing instance if Excel is already running
  5.         eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
  6.         die "Excel not installed" if $@;
  7.         unless (defined $ex) {
  8.             $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
  9.                     or die "Oops, cannot start Excel";
  10.         }
  11.  
  12.  
  13.         # get a new workbook
  14.         $book = $ex->Workbooks->Add;
  15.     $sheet = $book->Worksheets(1);
  16.  
  17.         # write a 2 rows by 3 columns range
  18.     $y = 2;
  19.     $x = 1;
  20.         $sheet->Range("A$x:J$y")->{Value} = [['Date','Total (IN)','Succ (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ (OUT)','MO(IN)','MO(OUT)'],
  21.                                            [$date, $total_in, $succ_in,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]];
  22.  
  23.  
  24.     $sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'],
  25.                       [$max_pk_msg,$pk_msg]];
  26.  
  27.  
  28.     foreach(@parameters)
  29.     {
  30.     $sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}];
  31.     $sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}];
  32.     }
  33.  
  34.  
  35.  
  36.          # print "XyzzyPerl"
  37.         $array = $sheet->Range("A1:I1")->{Value};
  38.         for (@$array) {
  39.             for (@$_) {
  40.                 print defined($_) ? "$_|" : "<undef>|";
  41.             }
  42.             print "\n";
  43.         }
  44.  
  45.         # save and exit
  46.  
  47.         $book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls") ;
  48.         undef $book;
  49.         undef $ex;
  50.  
Jan 29 '08 #13

kadghar
Expert 100+
P: 1,295
hm...sorrie both guys

hm..i came across this threadd and found it similar to my problem
but i'm using perl now and my data outputs to an excel file
the module i'm using is win32::OLE
if i use the same algorithms will it work?
cos i have no idea how i can open this file in perl..pls advise below is my code for ya to see thanks~
I've no idea =(
I havent worked very much with perl, but why dont you post your question in the perl forum? Im sure they can provide a good answer.
Jan 29 '08 #14

100+
P: 170
hm...i've already posted it
but not very good response
basically i'm trying to like:
step 1: have a if ... else statement to determine if an excel file $file exist, and if
it doesn't we create a new file $file
step 2: if the file exist then i have to append $file like wat u did in this thread

hm...i'm alreadying doing part 1...but part 2 is abit tough
thanks anyway dude
Jan 29 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.