CODEWITHSUNDEEP
vbaexcel
justkrys
justkrys

Reputation: 300

VBA close blank workbook

I found this question asked before, but it was never answered. I have some VBA code that formats different files and outputs them onto an Excel spreadsheet, and then saves copies to two different directories. However, the trouble I'm having is that it leaves an empty workbook open and I don't know how to close it. I have tried Workbooks.Close, and Application.Quit but neither worked. Any help would be greatly appreciated. Here is the part of my code that I'm referring to: 

sSaveAsFilePath = "\\Filesrv02\test\remit" + ".csv"
sSaveAsFilePath2 = "\\Filesrv02\backup\remit" + Format(Date, "mmddyy") + ".csv"

Application.DisplayAlerts = False
ChDir "\\Filesrv02\test"
ActiveWorkbook.SaveAs FileName:=sSaveAsFilePath, _
       FileFormat:=xlCSV, CreateBackup:=False
      'ActiveWorkbook.Close False

ChDir "\\Filesrv02\Backup"
       ActiveWorkbook.SaveAs FileName:=sSaveAsFilePath2, _
       FileFormat:=xlCSV, CreateBackup:=False
       ActiveWorkbook.Close True
Application.DisplayAlerts = False
MsgBox ("done")

End Sub

Upvotes: 0

Views: 778

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

Since you are only copying a sheet out of your workbook to save in different locations, you can just copy that sheet out of the workbook and save it to another location. This will leave your original workbook intact, and will not make a mysterious blank workbooks. Also, note how I qualified all the workbooks and worksheets with variables.

sSaveAsFilePath = "\\Filesrv02\test\remit" + ".csv"
sSaveAsFilePath2 = "\\Filesrv02\backup\remit" + Format(Date, "mmddyy") + ".csv"

Application.DisplayAlerts = False

Dim wb As Workbook, wbC As Workbook, ws As Worksheet

Set wb = ThisWorkbook
Set ws = ws.Sheets("mySheet") 'change name as needed

'create and save test version
ws.Copy

Set wbC = ActiveWorkbook

With wbC
    .SaveAs Filename:=sSaveAsFilePath, FileFormat:=xlCSV, CreateBackup:=False
    .Close
End With

'create and save backup version
ws.Copy

Set wbC = ActiveWorkbook

With wbC
    .SaveAs Filename:=sSaveAsFilePath2, FileFormat:=xlCSV, CreateBackup:=False
    .Close
End With

Application.DisplayAlerts = False

MsgBox ("done")

Upvotes: 1

Eric K.
Eric K.

Reputation: 834

Create a new workbook before save

    Application.Workbooks.Add
    ActiveWorkbook.SaveAs FileName:=sSaveAsFilePath, _
           FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close False

Upvotes: 0

Related Questions