Exporting to Excel - All Worksheets Highlighted

[kristyspdx]

I have a bunch of tables in Access that I export to one Excel spreadsheet. Each table goes to a different worksheet/tab in the same workbook. Each time I do this and then open the Excel workbook all the tabs are automatically chosen/highlighted. So, if I change something in cell A1 of the first tab, every tab's A1 is changed. I have to first remember to "unchoose" the other tabs, which of course I can never remember. Is there a way to export to Excel so all the tabs of the workbook are not automatically chosen/highlighted?



Thanks!

[June7]

Show code used for the export.

[kristyspdx]

Option Compare Database
'------------------------------------------------------------
' Copy_Of_M_2__Export_Tables_to_Excel
'
'------------------------------------------------------------
Function Copy_Of_M_2__Export_Tables_to_Excel()
On Error GoTo Copy_Of_M_2__Export_Tables_to_Excel_Err
DoCmd.TransferSpreadsheet acExport, 10, "Table 4: Athletics", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table 5: Band", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table 6: Blair School of Music", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table 7: Campus Copy", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table 8: Card Office", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table 9: Career Center", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table10: Cashier's Office", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table11: Children's Hospital", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table12: Commencement Events", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table13: Dance Marathon", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table14: Department of Mathematics", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table15: Dining", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table16: Dyer Observatory", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table17: Events (Public Affairs)", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table18: Harland Check Order", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table19: ISIS", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table20: Kennedy Center", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table21: Library", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table22: Mission Fish", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table23: OCGA", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table24: OGSM", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table25: Osher Lifelong Learning", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table26: PTY", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table27: Recycle", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table28: Returned Check", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table29: Sarratt Art Studio", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table30: Sarratt Student Center", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table31: Student Loan Dept", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table32: Student Recreation", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table33: Technology Transfer", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table34: TV News", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table35: Vanderbilt Collaboration Project (Law School)", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table36: VU Press", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table37: VU in Venice", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table38: Division 95", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""
DoCmd.TransferSpreadsheet acExport, 10, "Table39: Unknown Department", "M:\U_Finance\Fin Applications and Operations\15_Bank Reconciliations\01_Production\FY 2013\01_First TN Non-real estate\OTC Deposit\Matched & Unmatched Transactions from Access", True, ""

Copy_Of_M_2__Export_Tables_to_Excel_Exit:
Exit Function
Copy_Of_M_2__Export_Tables_to_Excel_Err:
MsgBox Error$
Resume Copy_Of_M_2__Export_Tables_to_Excel_Exit
End Function

[June7]

Guess I should have just asked if you were using TransferSpreadsheet method.

As far as I know, in order for Access to do manipulations with workbook such as to set a particular worksheet as the ActiveSheet, it must be opened as an object in the procedure, like:
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("path\filename.xlsx")
oBook.Worksheets("sheet name").Activate
oExcel.Visible = True