Insert the records in excel sheet

[veer]

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

[daniel aristidou]

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?

[kadghar]

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:

dim obj1 as object
set obj1 = createobject("excel.application")
obj1.workbooks.open "c:\myfile.xls"
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

[veer]

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?

[veer]

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:

dim obj1 as object
set obj1 = createobject("excel.application")
obj1.workbooks.open "c:\myfile.xls"
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

[kadghar]

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:

dim i as long
i=1
while obj1.cells(i,1).value <> ""
    i=i+1
wend
obj1.cells(i,1).value = "This is the first empty cell"

HTH

[veer]

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:

dim i as long
i=1
while obj1.cells(i,1).value <> ""
    i=i+1
wend
obj1.cells(i,1).value = "This is the first empty cell"

HTH

[veer]

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:

dim i as long
i=1
while obj1.cells(i,1).value <> ""
    i=i+1
wend
obj1.cells(i,1).value = "This is the first empty cell"

HTH

[kadghar]

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:

dim a as string
dim i as integer
with obj1
    a = .cells(1,1).value
    for i = 1 to 5
        if .cells(i,2).value = a then .cells(i,3) = "YES"
    next
end with

HTH

[veer]

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:

dim a as string
dim i as integer
with obj1
    a = .cells(1,1).value
    for i = 1 to 5
        if .cells(i,2).value = a then .cells(i,3) = "YES"
    next
end with

HTH

[kadghar]

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.

[veer]

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.

[poolboi]

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~

 
    use Win32::OLE;
 
        # use existing instance if Excel is already running
        eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
        die "Excel not installed" if $@;
        unless (defined $ex) {
            $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
                    or die "Oops, cannot start Excel";
        }
 
 
        # get a new workbook
        $book = $ex->Workbooks->Add;
    $sheet = $book->Worksheets(1);
 
        # write a 2 rows by 3 columns range
    $y = 2;
    $x = 1;
        $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)'],
                                           [$date, $total_in, $succ_in,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]];
 
 
    $sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'],
                      [$max_pk_msg,$pk_msg]];
 
 
    foreach(@parameters)
    {
    $sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}];
    $sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}];
    }
 
 
 
         # print "XyzzyPerl"
        $array = $sheet->Range("A1:I1")->{Value};
        for (@$array) {
            for (@$_) {
                print defined($_) ? "$_|" : "<undef>|";
            }
            print "\n";
        }
 
        # save and exit
 
        $book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls") ;
        undef $book;
        undef $ex;
 

[kadghar]

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.

[poolboi]

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