Need help opening/closing Excel files

[djrickel]

Hi all,

I open six Excel files, allow them to update, then import them back into Access--please don't question why--it works and does what I need.

1) They need to open without being visible but they are
2) They need to close without any messages but they don't

When they open, a message will come up saying that they are available for read-write...why? They do transfer back to the Access tables ok, so they don't need to be saved but won't close quietly. If you click yes to save, they go to "save as" which suggest they are read-only. Why?

I'm soooo confused...thanks for your help.


Private Sub Command527_Click()


' Deletes the two previous plan tables since they could be from a previous client
' or because the variables may have changed for the current client.
DoCmd.DeleteObject acTable, "tblPlan1"
DoCmd.DeleteObject acTable, "tblPlan2"


' Exports data to Excel to create plans and charts
DoCmd.RunMacro "mcrExportToExcel"


' Opens the necessary Excel files in the background for import & linking


Dim xlsApp As Excel.Application
Dim xlsBook1 As Excel.workBook
Set xlsBook1 = Workbooks.Open("C:\FS\DataFromAccess.xls", False, False)


Set xlsApp = xlsBook1.Parent
xlsApp.Visible = False


Dim xlsBook2 As Excel.workBook
Set xlsBook2 = Workbooks.Open("C:\FS\Plan1.xls", False, False)


Set xlsApp = xlsBook2.Parent
xlsApp.Visible = False


Dim xlsBook3 As Excel.workBook
Set xlsBook3 = Workbooks.Open("C:\FS\Plan2.xls", False, False)


Set xlsApp = xlsBook3.Parent
xlsApp.Visible = False


Dim xlsBook4 As Excel.workBook
Set xlsBook4 = Workbooks.Open("C:\FS\FSChart1.xlsx", False, False)


Set xlsApp = xlsBook4.Parent
xlsApp.Visible = False


Dim xlsBook5 As Excel.workBook
Set xlsBook5 = Workbooks.Open("C:\FS\FSChart2.xlsx", False, False)


Set xlsApp = xlsBook5.Parent
xlsApp.Visible = False


Dim xlsBook6 As Excel.workBook
Set xlsBook6 = Workbooks.Open("C:\FS\FSChart3.xlsx", False, False)


Set xlsApp = xlsBook6.Parent
xlsApp.Visible = False


' Imports the Excel data to tables Plan 1 and Plan 2
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblPlan1", "C:\FS\Plan1.xls", , "Plan 1!A1:AF71"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblPlan2", "C:\FS\Plan2.xls", , "Plan 2!A1:AF71"


' Deletes error tables that show up due to an
' incompatability issue that I have yet to figure out!
DoCmd.DeleteObject acTable, "Plan 1$A1:AF71_ImportErrors"
DoCmd.DeleteObject acTable, "Plan 2$A1:AF71_ImportErrors"


' Closes and Saves the same Excel files
Workbooks("FSChart3.xlsx").Close SaveChanges:=True
Workbooks("FSChart2.xlsx").Close SaveChanges:=True
Workbooks("FSChart1.xlsx").Close SaveChanges:=True
Workbooks("Plan2.xls").Close SaveChanges:=True
Workbooks("Plan1.xls").Close SaveChanges:=True
Workbooks("DataFromAccess.xls").Close SaveChanges:=True


End Sub

[ipisors]

A number of things that need to be corrected with this code before you will be controlling the objects fully, in the way that you SHOULD be controlling the objects.

1. set the xlsBook# variables by explicitly using the Application variable's Workbooks.Open method - not just leaving it up to chance. I.e:
Set xlsBook1 = xlsApp.Workbooks.Open("C:\FS\DataFromAccess.xls", False, False)
2. Prior to that, set the xlsApp by coding:
Set xlsApp = New Excel.Application
Do not set the xlsApp by grabbing the workbook's parent after the fact - that's useless, and is like finding the scalpel after the surgery's over.
3. set the .Visible property of the xlsApp immediately after doing #2, which you'll do before you open ANY workbooks.
4. Before opening ANY workbooks, also set the xlsApp's .DisplayAlerts property to False.
5. Read MSDN about the correct place to specify Read-Only for the Workbook Open method, and specify that explicitly.