How to create excel sheet in Visual Basic 6.0 and send it as attachment

[neelam]

Hi,

Am using Visual BNasic 6.0,i have to generate an excel sheet when the user clicks on a button and send this file as an attachment to the receiver....... can i overwrite the same file again or will have to create a new file every time ??....is this possible???.... .........orelse is there any other way............ ......I have created the excel sheet but get an error when i sent it as attachment..... ....plz help me in the same.....its really very urgent......... .........

Thanking in Anticipation
Neelam

[pellem]

It depends on how the file is created, but you can certainly open it, make changes, then save and close it, rather than re-creating it each time.

Also, how are you emailing it - Outlook VBA automation?

[neelam]

Excel Sheet

Thanx for the reply....i cudnt get the excel sheet creation...i wanted to kill the previous file and recreate a new one by the same name..is it possible.cudnt get help from anywhere so had to create a PDF file..that was easy..........Y a i am using my Outlook Express to sent tht efile as attachment.

[peirob2006]

This is a little late I think...but you are able to delete the existing file and create a new one afterward....

kill("C:\path\file.xls")

This should work...replace "C:\path\file.x ls" with the path of the excel file you want to delete

Thanx for the reply....i cudnt get the excel sheet creation...i wanted to kill the previous file and recreate a new one by the same name..is it possible.cudnt get help from anywhere so had to create a PDF file..that was easy..........Y a i am using my Outlook Express to sent tht efile as attachment.

[rabbani]

creating excel or text file are very easy but what i understand is each time either you append data on an existing file or kill & re-generate the following
syntax may help you

Open "C:\Test.xl s" for append as #1
print #1, "String" & your variable
close #1
for sending as an attachement then you have to Reference the MAPI controls
and have to have a function to send mail with this controls

Regards

[deepu12]

You can try out this code

Dim AppXls As Excel.Applicati on
Dim ObjWb As Excel.Workbook


Set AppXls = CreateObject("E xcel.Applicatio n")
Set ObjWb = AppXls.Workbook s.Add

ObjWb.Worksheet s.Item("Sheet1" ).Range(“A1”).V alue = “1”
‘ Range is The Cell Address
ObjWb.SaveAs ("C:\TestCreate .Xls")

ObjWb.Close (SaveChanges = False)

[abhiab54]

Hi deepu12

You can try out this code

Dim AppXls As Excel.Applicati on
Dim ObjWb As Excel.Workbook


Set AppXls = CreateObject("E xcel.Applicatio n")
Set ObjWb = AppXls.Workbook s.Add

ObjWb.Worksheet s.Item("Sheet1" ).Range(“A1”).V alue = “1”
‘ Range is The Cell Address
ObjWb.SaveAs ("C:\TestCreate .Xls")

ObjWb.Close (SaveChanges = False)

while compiling above code it is giving error at
ObjWb.Worksheet s.Item("Sheet1" ).Range(“A1”).V alue = “1”
that application defined or object defined error.
I have added the referance of Microsoft Excel library 9.0

[km2k]

This happens because the workbooks collection is initially empty. See the updated example :

Dim AppXls As Excel.Applicati on
Dim ObjWb As Excel.Workbook
Dim ObjWs As Excel.Worksheet

Set AppXls = CreateObject("E xcel.Applicatio n")
Set ObjWb = AppXls.Workbook s.Add

Set ObjWs = ObjWb.Worksheet s.Add
ObjWs.Range("A1 ").Value = "1"

ObjWb.SaveAs ("C:\TestCreate .xls")

ObjWb.Close (SaveChanges = False)

[veer]

hello expert
i am looking for the solution of generating excel sheet in vb6.0 and found this code but when i run this code it produces the error on line
"Set ObjWb = AppXls.Workbook s.Add "
the error is " Method Add of object workbook faild"
please send some idea

This happens because the workbooks collection is initially empty. See the updated example :

Dim AppXls As Excel.Applicati on
Dim ObjWb As Excel.Workbook
Dim ObjWs As Excel.Worksheet

Set AppXls = CreateObject("E xcel.Applicatio n")
Set ObjWb = AppXls.Workbook s.Add

Set ObjWs = ObjWb.Worksheet s.Add
ObjWs.Range("A1 ").Value = "1"

ObjWb.SaveAs ("C:\TestCreate .xls")

ObjWb.Close (SaveChanges = False)