Solved Export to Excel and leave the workbook open (1 Viewer)

[zelarra821]

Hello, people.

I have this code to export a form to Excel:

Private Sub CmdExportarExcel_Click()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strSql As String
Dim rsDatos As DAO.Recordset
 
'Abrimos Excel

Set xlApp = New Excel.Application
 
'Creamos un libro

Set xlBook = xlApp.Workbooks.Add

'Creamos una hoja

Set xlSheet = xlBook.Worksheets("Hoja1")
 
'Si hemos filtrado, obtenemos los datos del recordset
 
If FilterOn = True Then
                    
Set rsDatos = CurrentDb.OpenRecordset("SELECT Campaña, Subvencion, Precio, Observaciones FROM (" & _
Replace(Me.RecordSource, ";", "") & ") AS Consulta WHERE " & Me.Filter)
 
'En caso contrario, la tabla entera
 
Else

Set rsDatos = CurrentDb.OpenRecordset("SELECT Campaña, Subvencion, Precio, Observaciones FROM (" & _
Replace(Me.RecordSource, ";", "") & ") AS Consulta")
 
End If
 
'Exportamos los datos a Excel

xlSheet.Range("A1").Value = "Campaña"
xlSheet.Range("B1").Value = "Subvencion"
xlSheet.Range("C1").Value = "Precio"
xlSheet.Range("D1").Value = "Observaciones"
xlSheet.Range("A2").CopyFromRecordset rsDatos
 
'Cerramos el recordset

rsDatos.Close
 
'Cerramos el libro y la aplicación

xlBook.Close savechanges:=True
xlApp.Quit

'Reseteamos los objetos

Set xlSheet = Nothing
Set xlBook = Nothing

Set xlApp = Nothing

End Sub

However, I am not able to leave the book open. Right now the code closes the workbook and then Excel.

I have tried to obtain the path of the file to then open the path, but I could not obtain said path either.

Thanks.

 

[cheekybuddha]

Have you tried commenting out these two lines:

' ...
xlBook.Close savechanges:=True
xlApp.Quit
' ...

?

 

[zelarra821]

If I do that, the code doesn't work

 

[arnelgp]

maybe..

'xlBook.Close savechanges:=True
xlApp.Visible = True

 

[zelarra821]

Maybe this

xlApp.Visible = False

Set xlSheet = Nothing
Set xlBook = Nothing

xlApp.Visible = True

I suppose that if Excel is visible, Access can not continue the code and it gets an error. That's the reason I propose that. Only I don't know where I clean xlApp in end code.

Thanks to all.

 

[zelarra821]

Solved:

Private Sub CmdExportarExcel_Click()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strSql As String
Dim rsDatos As DAO.Recordset
 
'Abrimos Excel

Set xlApp = New Excel.Application
 
'Creamos un libro

Set xlBook = xlApp.Workbooks.Add

'Creamos una hoja

Set xlSheet = xlBook.Worksheets("Hoja1")
 
'Si hemos filtrado, obtenemos los datos del recordset
 
If FilterOn = True Then
                    
Set rsDatos = CurrentDb.OpenRecordset("SELECT Campaña, Apero, Precio, Observaciones FROM (" & _
Replace(Me.RecordSource, ";", "") & ") AS Consulta WHERE " & Me.Filter)
 
'En caso contrario, la tabla entera
 
Else

Set rsDatos = CurrentDb.OpenRecordset("SELECT Campaña, Apero, Precio, Observaciones FROM (" & _
Replace(Me.RecordSource, ";", "") & ") AS Consulta")
 
End If
 
'Exportamos los datos a Excel

xlSheet.Range("A1").Value = "Campaña"
xlSheet.Range("B1").Value = "Apero"
xlSheet.Range("C1").Value = "Precio"
xlSheet.Range("D1").Value = "Observaciones"
xlSheet.Range("A2").CopyFromRecordset rsDatos
 
'Cerramos el recordset

rsDatos.Close
 
'Mostramos el libro creado y reseteamos los objetos

xlApp.Visible = False

Set xlSheet = Nothing
Set xlBook = Nothing

xlApp.Visible = True

End Sub

By this way, Access don't ask the path in order to save the Excel file. Instead of it, Access open a Excel file with exported data and if you close Excel, you have to decide if you want to save the new workbook.

Thanks to all.