Create Excel file with VBA

[Michael]

Here are 2 examples that I have found to create an Excel file with VBA.

It is great for exporting data to Excel with the format that you want.


Michael


'--------------------------------------------------------------

Sub Test_Excel()
Dim MyXL As Object 'Excel Application Object
Dim XL_File As String
Dim SheetName As String
'Dim xlBook As Excel.Workbook

XL_File = "C:\Excel_Documents\test.xls"
SheetName = "New Sheet Name"

'Create the Excel Application Object.
Set MyXL = CreateObject("Excel.Application")

'Create new Excel Workbook
MyXL.Workbooks.Add

'Create the Excel Workbook Object, and open existing Excel Workbook
'Set xlBook = xlApp.Workbooks.Open(XL_File)

MyXL.Worksheets(1).Name = SheetName

MyXL.Worksheets(SheetName).range("A1") = "This is a test!!!!"


'Show the Excel sheet in Excel Window.
'MyXL.Application.Visible = True

'Save the Excel File
MyXL.Worksheets(1).SaveAs (XL_File)

'Close the Workbook or else XL_File will still be open and available for
read Only!
'Or MyXL.Quit could be used instead
'MyXL.Workbooks(1).Close

'Close the Excel Window and / or Application in background
'or else XL_File will still be open and available for read Only!
MyXL.Quit

Set MyXL = Nothing
End Sub


'--------------------------------------------------------------


Sub TestExcel2()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
' Place some text in the second row of the sheet.
xlSheet.Cells(2, 2) = "This is column B row 2"
' Show the sheet.
xlSheet.Application.Visible = True
' Save the sheet to C:\Test.xls directory.
xlSheet.SaveAs ("C:\Test.xls")
' Optionally, you can call xlApp.Quit to close the work sheet.
End Sub