Microsoft Excel...I HATE EXCEL!!!!

[Lloyd]

Does anyone know how to create a Excel application and workbook from scratch and how to delete it when done through VB? Any help or links or anything will greatly be appreciated.

Thanks guys and gals

Lloyd

[KenX]

' First define
Const xlExcel5 = 39
Dim XLS As Object ' for the application
Dim XLSheet As Object ' for the worksheet
Dim intRow As Integer ' row
Dim intCol As Integer ' column

' use the CreateObject for the application
Set XLS = CreateObject("Excel.Application")
' To add a workbook
XLS.Workbooks.Add

' To add a Worksheet
XLS.Worksheets.Add

' To rename the just-added sheet
XLS.ActiveSheet.Name = "mySheet"
Set XLSheet = XLS.Worksheets("mySheet")

intRow = 1
intCol = 2
' Set Column Names
XLSheet.Cells(1, 1) = "Company ID"
XLSheet.Cells(intRow, intCol) = "Employee Number"
XLSheet.Cells(intRow, 3) = Text1.value
XLSheet.Cells(1, Text1.value + 2) = "Job Code"

' You can format from VB...for example:
XLSheet.Columns("C:C").NumberFormat = "dd-mm-yyyy"
XLSheet.Rows("1:1").Font.Bold = True
XLSheet.Columns("A:I").EntireColumn.AutoFit

' To save as...
XLSheet.SaveAs Filename:="C:\MyDir\MyXLS.xls", FileFormat:=xlExcel5

' DO NOT forget to quit the excel app and also to release the objects...
XLSheet.Application.Quit
' Release the Excel object variables
Set XLSheet = Nothing
Set XLS = Nothing

'***NOW, I am not sure what you meant by "delete it when done through VB", well, I guess if you want the Excel application to
quit - I have indicated it. But if you want
VB to delete the Excel file, use the "Kill"
function of VB. But then again, if you don't
want VB to save the Excel book, skip the step of saving it add this line before quitting the application ("XLSheet.Application.Quit"):

XLSheet.Parent.Saved = True
(so that you force the XLS application to think that you have saved your work... )

hope this clears up things and hopefully, won't hate working with excel in VB anymore...

[Al Smith]

KenX,
I use virtually the same code to create Excel applications with the exception of the define. Const xlExcel5 = 39.

What does it do?
Thanks,
Al.

[KenX]

xlExcel5 = 39...
that's the constant for saving an excel file into a format Excel 95 format...

Vb uses the same constants used by Excel...
Thus, to save a workbook in other formats...
Excel2 = 16
Excel3 = 29
etc...

[Lloyd]

Thanks for the help KenX!! I never liked Excel too much. I don't know why. Probably because I don't use it that much. Thanks!!!!!!

Lloyd