Opening an instance of MS Excel using MS Access VBA

[yourmomentofzen]

Hi all-

I hope I'm posting on the right Usenet group and if I am not, i
thoroughly appologize.

I've been reading some postings on opening an instance of Excel through
Access VBA however seem to still be confused on its implementation. I
am still pretty unfamiliar with using ADO and had some help even
getting the code I have so far. Eventually I do want the code to be
able to access queries in Access, but right now at least just need to
get Excel running when a user clicks a button on my interface. Here's
what I have so far and its probably pretty butchered:

Private Sub ERL_Click()
On Error GoTo Err_Full_Click
Call OutputToExcel(XLRain) 'I'd eventually like to call a query
with this if this code is even right
Exit_Full_Click:
Exit Sub
Err_Full_Click:
MsgBox ERR.Description
Resume Exit_Full_Click
End Sub

Private Function OutputToExcel(oADORec As ADODB.Recordset) As Boolean

On Error GoTo cmdExcel_Err

OutputToExcel = False

Dim oXLApp As Excel.Application
Dim oXLWBook As Excel.Workbook
Dim oXLWSheet As Excel.Worksheet

Set oXLApp = New Excel.Application
Set oXLWBook = oXLApp.Workbooks.Add
Set oXLWSheet = oXLWBook.Worksheets.Add

oXLWSheet.SaveAs "test.xls"
oXLApp.Quit
Set oXLWSheet = Nothing
Set oXLWBook = Nothing
Set oXLApp = Nothing

OutputToExcel = True

Exit Function

cmdExcel_Err:
MsgBox ERR.Description & vbCrLf & ERR.Number & vbCrLf & ERR.Source

End Function



Thanks in advance.

 

[Stefan Hoffmann]

hi,

Eventually I do want the code to be
able to access queries in Access, but right now at least just need to
get Excel running when a user clicks a button on my interface.

What do you mean: "just need get Excel running"?

Your code uses early binding, so you need a reference set to the Excel
library.


Dim ap As Excel.Application
Set ap = New Excel.Application

ap.Visible = True
Set ap = Nothing

should work.

If you like to copy only data to an Excel sheet use Jet:

Dim SQL As String

SQL = "SELECT * " & _
"INTO [Excel 8.0;Database=PathAndNameOfSheet].[SheetName] " & _
"FROM query"
CurrentDb.Execute SQL

"PathAndNameOfYourSheet" is can be an existing sheet, data will be
overwritten, or a new one.


mfG
--> stefan <--

 

[Marshall Barton]

Stefan Hoffmann wrote:
[snip]

If you like to copy only data to an Excel sheet use Jet:

Dim SQL As String

SQL = "SELECT * " & _
"INTO [Excel 8.0;Database=PathAndNameOfSheet].[SheetName] " & _
"FROM query"
CurrentDb.Execute SQL

"PathAndNameOfYourSheet" is can be an existing sheet, data will be
overwritten, or a new one.

As the result of a law suit several years ago, there was a
change that makes Excel workbooks read only from access.
The "only" way to modify Excel data is to use automation.