Excel 2010 with Access 2010...

[Starchildren3317]

Good Morning community,

I have a couple of questions about using Excel 2010 with Access 2010. I have an application that I have written in access with a sql server 2008 back end.

My first question has to do with exporting an access table into excel.
I can do this with docmd.transferspreadsheet, however, it appears that this command requires an exact name and path of the excel sheet. What I would like to do is export from my access table to excel but have the save as dialog box pop up so that a user can choose where to save the file and what to name the file.

Anyone know how I can do this in Access 2010?

My second question is similiar to the first.
When I export to an excel 2010 spreadsheet and then try to export again to the same sheet, it doesnt seem to write over what was previously there - rather it isn't exporting at all if there was all ready data in the sheet.

Any ideas what I can do in Access 2010? I have a feeling it has to do with the security on the excel sheet that is created when doing a transfererspreadsheet.

Here is the code snip that I use for exporting to excel:

DoCmd.TransferSpreadsheet acExport, asSpreadsheetTypeExcel12Xml, LoadTableName, xlFile, True

Thanks in advance for the help!

 

[Guus2005]

You could do it the other way around: open an excel object and export the data into it.
This is the code i use.

Public Sub CreateSpreadsheetFromRS(rst As Recordset, blnVisible As Boolean, Optional blnHeader As Boolean = True)
'Recordset exporteren naar excel.

    Dim appExcel  As Excel.Application
    Dim wbExcel   As Workbook
    Dim wsExcel   As Worksheet
    Dim qdf       As QueryDef
    Dim intRij    As Integer
    Dim intVelden As Integer
    Dim intTeller As Integer
        
    If Not rst.EOF Then
        Set appExcel = New Excel.Application
        With appExcel
            .Visible = blnVisible
            Set wbExcel = .Workbooks.Add
            Set wsExcel = wbExcel.Worksheets(1)
        End With
    Else
        MsgBox "Geen records gevonden voor " & rst.name, vbExclamation, GetAppTitle()
        Exit Sub
    End If
    
    intVelden = rst.Fields.Count - 1
        
    intRij = 0
        
    If blnHeader Then 'Default worden de veldnamen geprint
        'Eerst de veldnamen
        intRij = intRij + 1
        For intTeller = 0 To intVelden
            wsExcel.Cells(intRij, intTeller + 1) = rst.Fields(intTeller).name
        Next intTeller
    End If
                
    Do While Not rst.EOF
        intRij = intRij + 1
        For intTeller = 0 To intVelden
            wsExcel.Cells(intRij, intTeller + 1) = rst.Fields(intTeller)
        Next intTeller
        rst.MoveNext
    Loop
        
    wsExcel.Columns.AutoFit
    wsExcel.Rows.AutoFit
    appExcel.Visible = True
    appExcel.WindowState = xlMinimized
    
    Set rst = Nothing
    Set qdf = Nothing

End Sub

Enjoy!