Auto open, save and close excel sheets

[cunnie]

Hi,

USING: Office 2000 professional

AIM: output a crosstab query from access to excel (and open it in excel), then save and close the excel file automatically (then open C:sccnew.xls from automatically)

TRIED: Using access's OutputTo macro and then placing the folowing code in the ThisWorkbook module of the excell file;

Private Sub Workbook_Open()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

RESULT: When access outputs the query to the file it erases the modules and macros within the file. Opening the excel file manually to run the code allows the code to run as desired.

I can't think of another way to attack this problem any suggestions you can make will be greatly apprieciated.

cunnie

 

[dk]

AIM: output a crosstab query from access to excel (and open it in excel), then save and close the excel file automatically (then open C:sccnew.xls from automatically)


Hi,

If you export the query, why do you need to then open it, save it and close it - that doesn't seem to serve any purpose.

How about using something like this? You'll need to change the query name and export file name to whatever you need.

Sub Export()
Dim oXLApp As Object

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCrosstab", "C:tempmy crosstab.xls", True

Set oXLApp = CreateObject("Excel.Application")
oXLApp.workbooks.Open "C:sccnew.xls"
oXLApp.Visible = True

End Sub

 

[cunnie]

The reson I wanted to open then save and close the files was that when I had the outputTo query in place, the file it output to wouldn't was linked to another excel sheet. The only way (that i found) to update the content of the xls file that was linked to the output file, was to open and save the the spreadsheet.
Hopefully that won't be the case with your suggested code. I havn't had chance to test it completley yet.

I may need to add another line in order to output the content of a form, also to an excel file. Can this be done by simply changing the parts in inverted commas from this line:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Crosstab", "C:SCCcontracts.xls", True

or is there a part of the syntax for specifiying to use a form for the input?

Thanks alot for your helpful and speedy reply