2 questions about exporting to Excel

[Shiner83]

Hello, I need to export data to Excel from Access and then open the Excel sheet. I have been able to find a code that works well enough, but I have 2 problems remaining that I can't solve.

1. If I try to open the excel sheet and it is already opened, I can't and Access freeze. I was able to find a way around the problem by having a function that determines if Excel is already running; if it is, a message box appears and ask the user to manually close the sheet and then I use exit sub. I would prefer if Access could check if the file test.xls is open, and if it is, automaticly close it. Is it possible?

2. I don't want the users to be able to save the excel file, but I need to export data to it. If I make the file read-only I can't export my data. Any way I can easily solve the problem? I'm not familiar at all with vba coding in Excel so please I would need detailed informations if I must code in Excel


Here's the code I'm using:


Private Sub CmdExport_Click()

On Error GoTo ViewExcelError

Dim xlApp As Excel.Application
Dim xlBook As Excel.workBook
Dim xlSheet As Excel.workSheet
Dim DB As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQl As String


If fIsAppRunning("Excel") Then
MsgBox "Excel is already opened. Please close it and restart the command"
Exit Sub
End If

Set DB = CurrentDb
Set qdf = DB.QueryDefs("ReqAuto")

strSQl = "SELECT NoAuto, Mun, DateTrans, Year(DateTrans) As AnTrans, Month(DateTrans) As MoisTrans, " & _
"UVois, CUtil, AnConst, AnApp, PrixD, PrixJ, ÉvalMun, Fbat_J, SupTer " & _
"FROM ReqTotale;"

' Create an Excel workbook file based on the
' object specified in the second argument.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"ReqAuto", "C:\Access\test.xls", True
' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")


' Open the spreadsheet to which you exported the data.
Set xlBook = xlApp.Workbooks.Open("C:\Access\test.xls")
Set xlSheet = xlBook.Worksheets("ReqAuto")
xlApp.visible = True

'print sheet
'xlSheet.PrintOut
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
'xlApp.Quit

Set xlApp = Nothing
ExitViewExcel:
Exit Sub

ViewExcelError:
MsgBox Err.Description
Resume ExitViewExcel


End Sub

 

[PHV]

I would prefer if Access could check if the file test.xls is open, and if it is, automaticly close it.
In access, take a look at the GetObject function
In excel, take a look at the Workbooks collection

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shiner83]

Thanks for the pointer, I was able to do what I wanted for my first question. However there is some things I don't understand.

First, here's the code I use:

If fIsAppRunning("Excel") Then
Set MyXL = GetObject("C:\Access\table.xls")
If Err.Number = 0 Then
MyXL.Application.visible = True
MyXL.Application.Quit
Set MyXL = Nothing
'MsgBox "Restarting"
End If
Err.Clear
End If

On the GetObject, if I write the name of the file I want closed (test.xls) I get an error, but if I write the name of another of my file, like table.xls, the test.xls close correctly. Weird.

Also, when I do my code on step-by-step, everything works fine; I close excel, then create my SQL statement, export it to test.xls then finally open test.xls. However, when I start it normally, Access freeze at the export part and I have to ctl-alt-del to stop Excel. Any idea why it's happening? If I put a message box just after the deletion of the table, everything works fine, so I'm confused about what is the problem.


Also, I've found out how to exit my application without the saving prompt; just need to add
MyXL.Application.DisplayAlerts = False
before deleting it
However there's still the problem that the user might change important things on the excel sheet and manually save it; any way to solve this?