Closing Workbooks

[auujxa2 via AccessMonster.com]

Currently, I have 2 loops. One to import all workbooks in a folder, and a
loop within that to import all worksheets within those books.

But when the workbooks close, I get a message box saying the book I just
closed in now available to read-write or cancel. How do I make that box not
appear. The files aren't read only. Here is the loop code.

Dim dbs As Database, rst As Recordset
Dim myPath As String
Dim myCount As Variant
Dim myFile As String
Dim SQL As String
Dim wkb As Excel.Workbook
Dim sht As Excel.WorkSheet
Dim xl As Excel.Application

myCount = 0
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Files")
Set xl = Excel.Application
xl.Visible = True

With rst
Do While Not rst.EOF
myCount = myCount + 1
myPath = rst.Fields("FPath")
myFile = rst.Fields("FName")
Set wkb = xl.Workbooks.Open(myPath & myFile, , False)
With wkb
For Each sht In .Worksheets
SysCmd acSysCmdSetStatus, "Importing sheet " & sht.Name & " from
file " & myFile
DoCmd.TransferSpreadsheet acImport, 8, "MasterTable", myPath &
myFile, False, "T7:AG56"
SQL = "UPDATE MasterTable"
SQL = SQL & " SET F1 = '" & myFile & "'"
SQL = SQL & " WHERE F1 Is Null"
dbs.Execute SQL
SQL = "UPDATE MasterTable"
SQL = SQL & " SET F2 = '" & sht.Name & "'"
SQL = SQL & " WHERE F2 Is Null"
dbs.Execute SQL
Next
'''''''''''''''''''''''''HERE IS MY
PROBLEM'''''''''''''''''''''xl.Workbooks.Close
End With
rst.MoveNext
Loop
rst.Close
End With

 

[Paolo]

Hi,
to switch on and off excel's warning messages use displayalerts.
in you case

xl.DisplayAlerts = False
xl.Workbooks.Close
xl.DisplayAlerts = true

HTH Paolo

 

[auujxa2 via AccessMonster.com]

thanks!!

Hi,
to switch on and off excel's warning messages use displayalerts.
in you case

xl.DisplayAlerts = False
xl.Workbooks.Close
xl.DisplayAlerts = true

HTH Paolo

Currently, I have 2 loops. One to import all workbooks in a folder, and a
loop within that to import all worksheets within those books.

[quoted text clipped - 46 lines]

rst.Close
End With