access to excel

If you have opened an Access application throght automation and the object variable is called accapp you could use code silmilar to this:

accapp.DoCmd.DoMenuItem 'use option export to an extern database and pass the name of an Excel woorbook, table name and so on.
Hope it helps

Option Compare Database
Option Explicit

'------------------------------------------------------------
' Export_To_Excel - VBA
'
'------------------------------------------------------------
Function Export_To_Excel()
On Error GoTo Export_To_Excel_Err

    DoCmd.TransferSpreadsheet acExport, 8, "MyTable", "MyExcelFile.XLS", False, ""


Export_To_Excel_Exit:
    Exit Function

Export_To_Excel_Err:
    MsgBox Error$
    Resume Export_To_Excel_Exit

End Function

Ups!

I forgot to mention that access is not installed on computer where this app. will run, but excel is present.

Sorry!

I found this and works just fine!

   Dim xlApp As Excel.Application
   Dim xlbook As Excel.Workbook
   Dim xlSheet As Excel.Worksheet
   Dim rs As Recordset
   Dim Counter As Integer


   Set xlApp = Excel.Application
   xlApp.Visible = True
   Set xlbook = xlApp.Workbooks.Open("c:\junk.xls")
   
   sql_caluse = "SELECT * FROM my_table "
    Set rs = DataEnvironment1.Connection1.Execute(sql_clause)
    rs.MoveFirst
   
   Set xlSheet = xlbook.Worksheets("Sheet1")
   Do Until rs.EOF
      Counter = Counter + 1
      With xlSheet
         .Cells(Counter, 1).Value = rs("Field1")
         .Cells(Counter, 2).Value = rs("Field2")
         .Cells(Counter, 3).Value = rs("Field3")
      End With
      rs.MoveNext
   Loop

Hi Mitja100,

If Excel is not installed on the machine, then I'm not sure that your method will work propperly.

Try using the OutputTo method instead:

DoCmd.OutputTo acOutputTable, "TableName", acFormatXLS, "MyTable.xls", True

Hope this helps,

Nosterdamus

Sorry, didn't notice Excel is present and Access is NOT...

Please ignore my last comment.

Nosterdamus

Create a ADODB recordset containing your data and pass your recordset to this sub that will generate a CSV file (that Excel recognize natively):

Public Sub CopyRsToCSV(ByVal prstData As ADODB.Recordset, Optional ByVal pstrFileName As String)
Dim intFileNumber As Integer
Dim lngLoop As Long
Dim strX As String

    If Len(Trim$(pstrFileName)) = 0 Then pstrFileName = App.Path & "\zzRst.csv"
   
    'Open data file data to file
    intFileNumber = FreeFile   ' Get unused file
    Open pstrFileName For Output As #intFileNumber
    'Write data to file
    With prstData
        strX = ""
        For lngLoop = 0 To .Fields.Count - 1
            strX = strX & .Fields(lngLoop).Name & ", "
        Next lngLoop
        Print #intFileNumber, strX
        If .RecordCount > 0 Then .MoveFirst
        Do Until .EOF
            strX = ""
            For lngLoop = 0 To .Fields.Count - 1
                strX = strX & .Fields(lngLoop).Value & ", "
            Next lngLoop
            Print #intFileNumber, strX
            .MoveNext
        Loop
    End With
    'Close
    Close #intFileNumber
End Sub

It uses DAo engine to get the records:

Option Explicit
'set a reference to microsoft DAO 3.51 or 3.6 object library
'set areference to excel 8 object library
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet

Sub SaveXlsFile(sFileName As String)
xl.ActiveWorkbook.SaveAs sFileName
End Sub
Private Sub Form_Load()
Set xl = New Excel.Application
' open excel
With xl
    .Visible = True
    Set wb = .Workbooks.Add
    Set sh = .ActiveWorkbook.ActiveSheet
End With

'open database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Microsoft Visual Studio\VB98\nwind.mdb", False, False)
' populates recordset
Set rs = db.OpenRecordset("Clientes", dbOpenTable)
' show records on activesheet
sh.Range("a1").CopyFromRecordset rs

SaveXlsFile "c:\yourchoice.xls"
xl.Quit
Set xl = Nothing
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Mitja100, any progress on this?

For Mitja100

It's time to resolve this question, so I will leave a recommendation in Community Support Topic Area that this question is:
- PAQ'd and points refunded

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Thanks!

Nosterdamus