Opening Access & Closing Excel

Question

Hi,

I'm running a macro which opens an Access database and then i want it to save the Excel workbook and set the focus on the Access database i've jsut opened.

The code below is what i've got:

Code:

Sub PullData_Amend_2()
    Set tk = ActiveWorkbook
    Sheets("Download").Select
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    endRow = Range("A65536").End(xlUp).Row - 1
    Range("F2").Resize(endRow, 1).FormulaR1C1 = "=RC[-1]&""_""&RC[5]"
    Range("F2").Resize(endRow, 1).Copy
    Range("F2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    MsgBox "Data is now correctly formatted."
    'open PQQ Database and close Excel
    Set acApp = New Access.Application
    acApp.Visible = True
    acApp.OpenCurrentDatabase ("S:\XXXXXXXX\Database.accdb")
    tk.Save
    Application.quit
End Sub

Now the problem is at the end, the macro opens the database ok, then makes it visible. It then saves the workbook and closes Excel - but also closes Access too.

How can i keep Access open at the end??

Many thanks

James

Answer 1

Probable answer:

Hello James,

If the data you want to put in Excel is a report, then use Access's Export option.  This will export the data to Excel with the name of the Report you designed in Access.  You can add suitable code to do so...

DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, "C:\documents and settings\desktop\RptSampleDataBase.xls"


and have other ways of working with Export.