[RESOLVED] How to add data to an existing Excel & save wid same name??

[Abhilash007]

hey guys!
I have been working on a code to add certain data to a template excel file. The problem i have now is that i am not able to save the appended excel file with the same name as the original template file. When I try to do dat, the code overwrites the template file.

Is there any way to save a template excel file after adding some data with its original name without overwriting the original data in it.

I am currently using obook.SaveAs command. (where obook is the workbook object)


please advise

cheers
Abhilash

[RobDog888]

Thats because it soundds like you are opening the template and not creating a new document based upon the template.

Something like this will do it.

oApp.Documents.Add "C:\MyTemp.dot", False, wdNewBlankDocument, True

[Al42]

You can't have 2 files of the same name and type in the same folder. It's something like having 2 documents written on the same side of the same piece of paper - the concept is wrong.

[RobDog888]

That will be prevented as when you create a new document based upon a template it will name it - "MyCustomTempate1.doc", "MyCustomTemplate2.doc", etc. never overwritting the dot file since that is the way templates work when documents are created based upon one.

[Abhilash007]

Hey!

I can understand wad you say but the thing is i just want to add data to a present excel file and save it with the same name. I do not require the original template file, i only need the final appended file..

At the moment when i try to save the final with the same name as its original name, then i get a message asking me to replace original file.

If i replce original, all the data in the original template will be erased and only the newly added data is shown.

I hope you can understand my problem

PLease advise

cheers
Abhilash

[RobDog888]

So then your not adding data to a workbook generated from a custom template?

" to add certain data to a template excel file."

[Abhilash007]

It is a Custom template in the sense that i have created it. When i created it, i did input some data which are constant. on top that constant data i have to write a code which calculates certain data and adds onto the excel file. now after the code adds the data, i want to save the excel file with the same filename. that is i just want to add data to a file and not make any copy.


cheers
Abhilash

[RobDog888]

Ok, but is this an actual template file with an extension of xlt? or a standard workbook with an extension of xls?

[Abhilash007]

It is a standard workbook with ext .xls

[RobDog888]

Ah, ok then we are now talking about the same thing lol.

Does the .Save work better for you then?

[Abhilash007]

Hey Rob!

i tried .Save also... this is my test code..

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oExcel As New Object
        Dim oBook As Object
        Dim oSheet As Object

        
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Open("C:\Unicode.xls")         
        oSheet = oBook.ActiveSheet
        oSheet.Range("C4").Value = "Order ID"
        oSheet.Range("C5").Value = "Amount"
        oSheet.Range("C6").Value = "Tax"


        oBook.Save("C:\Unicode.xls")      'The Error occurs here
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
        MessageBox.Show("xls saved")
    End Sub
End Class

It now shows a new error at the line ' oBook.Save("C:\Unicode.xls") '

Number of parameters specified does not match the expected number.

[Al42]

Try oBook.Save

oBook.Save("C:\Unicode.xls") is Save As, not Save.

[RobDog888]

As per my suggestion, yes .Save is the way to do it and since its .Save you dont need to specify any args as there are none.

oBook.Save()


Now you are switchng back as you said you just needed to save it as the file is open so .Save is what you want. Now if you are requesting it to be renamed again as well then you have to go back and use .SaveAs which will give you the overwrite prompt.

[Abhilash007]

hey..

yep.. dat has solved my prob... thx a lot


cheers
Abhilash