Auto open, save and close excel sheets

cunnie

New Member
Joined
Oct 29, 2002
Messages
3
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942

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.

Code:
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

New Member
Joined
Oct 29, 2002
Messages
3
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 :biggrin:
 

Forum statistics

Threads
1,144,329
Messages
5,723,734
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top