Create Excel file with VBA in Access

[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

 

[Larry Daugherty]

These newsgroups are for people to post an issue in a thread and for
others to make suggestions or provide solutions in that same thread.

Spontaneous publication of pearls of Access (or any other) lore are
not welcome.

HTH

 

[Larry Linson]

And, if you "found" them, you should credit the source.

Larry Linson
Microsoft Access MVP

 

[Michael]

Sorry for this posting then...

Did not mean to disturb the discussion group!?!

I did not take credit for the code! Where did you see that? That is why I
used the word “Foundâ€!

This discussion group helped me in the past, and I was looking for a
complete example to create an Excel file from Access 2 days ago, but I did
not find it here this time. I saw other posting looking for the same thing.

I thought to share what I have just found, and a posting like this might
help some other people, since I did not see a complete example like this.

I do not brag. But I you want to know the source of the code, here it is:

The 2nd code : “Sub TestExcel2†can be found here:
http://www.msdner.com/dev-archive/42/10-64-421649.shtm
http://www.dotnet247.com/247reference/msgs/53/265840.aspx

The 1st code however “Sub Test_Excelâ€, I wrote from understanding the 2nd
code.

Sorry for the trouble…

Michael

 

[Michael]

The 2nd code : “Sub TestExcel2†the original source:
http://msdn2.microsoft.com/en-us/library/7t9k08y5(VS.71).aspx

(the other were not the original source)

Michael

 

[Ed from AZ]

Well, *I* am glad this was posted!

The "other posting" might have been mine
(http://groups.google.com/group/microsoft.public.access/browse_thread/
thread/eee39e33699dbe1c?hl=en#).

I know in other NG (Word and Excel, both user and VBA), posting like
this are often appreciated. And at least it's now in the NG archives,
so when someone else goes searching, the answer is there -since it may
be a while before a thread is responded to, and I for one tend to keep
searching and poking rather than just waiting. My continued searching
while waiting has given me this code, which I just might need.

Thank you, Michael.

(And if you have anything to add to my query expression frustrations,
please feel free to visit my thread linked above.)

Ed

 

[EvertDeJongh]

And, if you "found" them, you should credit the source.

Larry Linson
Microsoft Access MVP

Are you serious? This guy helped me with that code. Thanx Michael.
Do you have nothing else to do?
Cheers.

 

[mcescher]

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

Michael,

Here's one more tip. Everybody else in the newsgroup can ignore
this....

Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryYourQuery", dbOpenDynaset)
xlsReport.Range("A5").CopyFromRecordset rs

This will paste your query results into Excel, just add your own
headings. Viola!!!

Chris M.