Thread: Macro runs query that exports to Excel, now how to open additional Excel Workbooks?

[djrickel]

A macro behind a button runs a query that exports a few fields to Excel that are then linked to a couple of monster Workbooks. The button works fine but I cannot figure out how to ALSO do the following at the same time...

I need :
1) to suppress the message that says the Excel file already exists (which it does but I just want it overriden each time)
2) to prevent the same Excel file from automatically opening.
3) to automatically open the two monster Excel Workbooks linked to the exported data. Call them Plan1 and Plan2.

Thank you.

 

[1DMF]

1) to suppress the message that says the Excel file already exists (which it does but I just want it overriden each time)

If using the transfer spreadsheet method, what I do first is kill (delete) the source XLS file , then there isn't one to overwrite.

'Delete old file  
KillFile sFile

'Transfer details to spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "selMyQuery", sFile, True

by the way this is my killfile sub

Sub KillFile(ByVal sFilename As String)
    On Error Resume Next
    'delete file ignoring any error (like no file there to delete)
    Kill sFilename
End Sub

3) to automatically open the two monster Excel Workbooks linked to the exported data. Call them Plan1 and Plan2.

to open a file I use followhyperlink

FollowHyperlink (sFile)

2) to prevent the same Excel file from automatically opening.

Not sure why this is happening if using the transfer spreadsheet method, it doesn't do that in my apps?



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[SkipVought]

If I understand that your objective is to get 2 Excel exports from MS Access into 2 gi-normous Excel files, Plan1 & Plan 2, I'd simply export into 2 OTHER files and then, IN Plan1 & Plan2, ONE TIME, add a QueryTable in each, via MS Query, to IMPORT the data from the 2 Excel exports, and set the REFRESH ON OPEN properties of each query, so when Plan1 opens, the query will be refreshed that is linked to the Excel workbook that was exported from Access and similarly for the Plan2 workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]