Create Excel Application dynamically in vb6

[cycleinmars]

hello guys ,
Just found the way to create a excel application using vb6 .

Dim AppXls As Excel.Application
Dim ObjWb As Excel.Workbook
Dim objws As Excel.Worksheet

Set AppXls = CreateObject("Excel.Application")
Set ObjWb = AppXls.Workbooks.Add
Dim a As String

a = "newexel.xls"
ObjWb.SaveAs (App.Path & "\" & a & "")
ObjWb.Close (SaveChanges = True)

well this is for starters like me.

[si_the_geek]

That's a reasonable start, but there are various things that aren't quite ideal, and of course several things missing (such as working with the sheet).

For info on the way things should be, and other things you can do, see my Excel automation tutorial (link in my signature).


By the way, you should generally not be saving files within App.Path , for information see the article Where should I store the files that my program uses/creates? from our Classic VB FAQs (in the FAQ forum)

[cycleinmars]

Thanks for your reply


I am not able to create a worksheet in the same file , can you please help,
let the code be simple,

Regards
praveen

[Hack]

I am not able to create a worksheet in the same file , can you please help, let the code be simple,

What happens when you try? Do you get an error? What code are you using?

[cycleinmars]

Hai,

when i use the following code

Set objws = ObjWb.Worksheets.Add
    objws.Name = "new"
    Set objws = Nothing

when i open the excel file i do not see any changes / any sheets added in the workbook


no error is displayed

regards

praveen

[Hack]

Try something like

ObjWB.Sheets.Add Before:=ObjWB.Sheets("Sheet2") 'if you do not care where the new sheet gets
ObjWB.ActiveSheet.Name = "VBForums"             'added then you can ignore the Before part 
                                                'of the code

[si_the_geek]

It works perfectly for me, here is the entire code I used (no references needed):

Private Sub Form_Load()

Dim AppXls As Object
Dim ObjWb As Object
Dim objws As Object

    Set AppXls = CreateObject("Excel.Application")
    Set ObjWb = AppXls.Workbooks.Add

    Set objws = ObjWb.Worksheets.Add
    objws.Name = "new"
    objws.cells(1, 1) = "hello"
    Set objws = Nothing

    Set ObjWb = Nothing
    AppXls.Visible = True
    Set AppXls = Nothing

End Sub

If you are still doing the .SaveAs , are you sure that the file doesn't exist when you run it?

Are you perhaps using "On Error Resume Next"?

[cycleinmars]

Hai,

thanks for your reply , i fixed the same,

i was saving the file before it created the worksheet
now i just put
objwb.saveas after creation of the worksheet, so it works perfect for me


however why is this code required

ObjWb.Close (SaveChanges = True)

what does savechanges = true does, is it useful?

regards

Praveen

[cycleinmars]

Hai,

I have created a worksheet "new" ,
but i also want to create "new1" sheet if new is present
how will i check whether the same worksheet is there or not in the workbook

regards

Praveen

[si_the_geek]

The way you have the code, it doesn't do what you think... it is using a variable you have created (presumably accidentally because you aren't using Option Explicit).

The way it should be is:

ObjWb.Close SaveChanges := True

I'm fairly sure my tutorial explains why you would use it.