PDA

View Full Version : [SOLVED] Delete File In Use error



psctornado
02-18-2014, 02:48 PM
Hi All,

I was hoping someone here could help me out with this error. I currently have a workbook with the following save copy code :



Sub Save()
ActiveWorkbook.SaveCopyAs Environ("userprofile") & "\Documents\My Workbook\My Workbook.xls"
End Sub

The code in essence saves a copy to the Documents folder, then is the data source to complete a mail merge. The issue is after the code is run, and say I create a new workbook and run the save macro again, the error I get is that the My Workbook.xls file is in use.

Is there a way to 'unlock' or that file so that I can 'overwrite' the previously saved 'My Workbook.xls file?

It's almost as though it doesn't 'close' the connection from the save as macro, but I'm at a loss as to what I can do to remedy this issue. Any help would be greatly appreciated!!

:think:

p45cal
02-19-2014, 12:05 PM
No proble here with your cade as is, but if you're using it for mail merge, has it been closed after that operation? Is it code which does the mail merge?

psctornado
02-19-2014, 12:11 PM
Hi P45cal,

This code itself does not complete the merge, but establishes a the source for the merge. Are you saying perhaps the VBA code in the word doc should have a closed loop operation once the merge is complete?

p45cal
02-19-2014, 12:39 PM
Something like that. I'm not super familiar with Word vba code, but I'd expect that it does open the Excel file, so once the merge is complete, there should be a way of tidying up and closing out files in use.
What's the code in Word look like?

psctornado
02-19-2014, 12:51 PM
Here you go...



Private Sub Document_Open()
Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\My Workbook.xls"
With ActiveDocument
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:Database Loc", SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
Application.WindowState = wdWindowStateMaximize
Application.DisplayAlerts = wdAlertsAll
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName:=MMSource)
Set xlSheet = xlBook.Worksheets("Master2")
FName = xlSheet.Range("AW2").Value
ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\Desktop\Manual Lit\"
ActiveDocument.SaveAs FileName:=FName
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End With
End Sub


As you can see the merge references the My Document.xls file, specifically the Master 2 sheet and then names the word doc to a specific field in the Master 2 sheet.

I think it would make sense to close the source xls, but to be honest I'm at a loss as to how to do that. :doh:

p45cal
02-19-2014, 02:37 PM
after:

FName = xlSheet.Range("AW2").Value try adding:

xlbook.close False
xlApp.Quit
completely untested.