Closing Workbooks

  • Thread starter auujxa2 via AccessMonster.com
  • Start date
A

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
 
P

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import Help 0
HELP! - Import Loop 3
Loop 3
Opening an Excel File 0
Copy item from Outlook to folder via Excel 12
Function not working 3
Qualifier Must Be Collection 1
Export SQL 1

Top