Not saving on close

I have done a break and it gose through correctly or if I manually close the file it works correctly. Here is the code:

Private Sub CommandButton4_Click()
    Response = MsgBox("Are you creating a new Bond?", vbYesNoCancel)
    If Response = vbYes Then
        newbond
    End If
    If Response = vbNo Then
        existingbond
    End If
    Workbooks("Bondsdbs.xls").Activate
    ActiveWorkbook.Close
End Sub

Hi uTab,

For your first procedure you posted, try the following.  I've cleaned it up a bit for you (got rid of the unnecessary selections), put in some With blocks and added the screen updating properties to stop the screen from flashing:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim newWb As Workbook
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
    With Sheet1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    Sheet2.Range("B1:FY1").NumberFormat = "0.0000"
    Sheet3.Range("B1:BI1").NumberFormat = "0.0000"
   
    ThisWorkbook.Save
    Set newWb = Workbooks.Add
    With newWb
        ThisWorkbook.Sheets("Sheet1").Copy .Sheets(1)
        .SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With
    Set newWb = Workbooks.Add
    With newWb
        ThisWorkbook.Sheets("Sheet2").Copy .Sheets(1)
        .SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With
    Set newWb = Workbooks.Add
    With newWb
        ThisWorkbook.Sheets("Sheet3").Copy .Sheets(1)
        .SaveAs Filename:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With
   
    ThisWorkbook.Saved = True      'This statement avoids an Excel warning message about saving the file before closing
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    Shell "c:\tmp\import.bat"
End Sub

I think that this should do it for you. The second sub you posted doesn't have any code to save the workbooks, unless NewBond and ExisitngBond are procedures called.  I think that the part that was doing you in was the attempt to save a sheet to a CSV directly.  I've changed it to add a new workbook, copy the sheet there, then save that file as CSV.  It's not the cleanest or most efficient method, but it should work.

Let us know,

Ken

Ken,

With your code above I get the following error:

Run-time error '9';
Subscript out of range

When I debug this is the highlighted code

ThisWorkbook.Sheets("Sheet1").copy .Sheets(1)

Thanks

Hi uTab,

My mistake.  I mistakenly put that in referecing a sheet name instead of the codename.  Try this instead:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
Dim newWb As Workbook
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
    With Sheet1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    Sheet2.Range("B1:FY1").NumberFormat = "0.0000"
    Sheet3.Range("B1:BI1").NumberFormat = "0.0000"
   
    ThisWorkbook.Save
    Set newWb = Workbooks.Add
    With newWb
        Sheet1.Copy .Sheets(1)
        .SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With
    Set newWb = Workbooks.Add
    With newWb
        Sheet2.Copy .Sheets(1)
        .SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With
    Set newWb = Workbooks.Add
    With newWb
        Sheet3.Copy .Sheets(1)
        .SaveAs Filename:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With
   
    ThisWorkbook.Saved = True      'This statement avoids an Excel warning message about saving the file before closing
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    Shell "c:\tmp\import.bat"
End Sub

It is still not saving the information to the file or the cvs file unless closed manually, help!

This is driving me nuts

What if you make sure that the file doesn't already exist before you save to it?

Try changing the With newWb sections to the following:

With newWb
        Sheet1.Copy .Sheets(1)
        Kill "C:\tmp\bondinfo.csv"
        .SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
End With

With newWb
        Sheet2.Copy .Sheets(1)
        Kill "C:\tmp\rateinfo.csv"
        .SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
End With

With newWb
        Sheet3.Copy .Sheets(1)
        Kill "C:\tmp\rateinfo.csv"
        .SaveAs Filename:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
End With

Let me know,

Ken

It ran ok for me when I tested this from book 1

Workbooks("Bondsdbs.xls").Activate
ActiveWorkbook.Close

What happens if you try this instead?

   Dim ws As Workbook
    Set wb = Workbooks("Bondsdbs.xls")
    wb.Close

Cheers

Dave

I've seen this problem before. It may be the same.

When you take out the display alerts parts, it will ask you if you want to overwrite the existing file (if it exists). The box will have "No" as the default button. Now here's the catch - when you use DisplayAlerts = False it will actually open the "Do you want to overwrite existing file" box, and will select the default button - No.so the file isn't saved.

The only work around for this (apart from an API call to change the default button, which we never worked out), is to Kill all the old files first, so it has no need to worry about overwriting. so taking cleaned up code and adding the Kills we get this. I hope it works.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    With Sheet1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    Sheet2.Range("B1:FY1").NumberFormat = "0.0000"
    Sheet3.Range("B1:BI1").NumberFormat = "0.0000"
    ThisWorkbook.Save
   
    On Error Resume Next
        Kill "c:\tmp\bondinfo.csv"
        Kill "c:\tmp\rateinfo.csv"
        Kill "c:\tmp\dictinfo.csv"
    On Error GoTo 0
   
    Sheet1.SaveAs FileName:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
    Sheet2.SaveAs FileName:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
    Sheet3.SaveAs FileName:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
    ThisWorkbook.Saved = True      'This statement avoids an Excel warning message about saving the file before closing
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
    Shell "c:\tmp\import.bat"

End Sub

If it doesn't work then try taking out the DisplayAlerts = False and seeing if any other message boxes come up.

Cheers,
MalicUK.

No still doesn't work. With the last suggestion it will not create the csv files.

OK, try this amend:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    With Sheet1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    Sheet2.Range("B1:FY1").NumberFormat = "0.0000"
    Sheet3.Range("B1:BI1").NumberFormat = "0.0000"
    ThisWorkbook.Save
   
    On Error Resume Next
        Kill "c:\tmp\bondinfo.csv"
        Kill "c:\tmp\rateinfo.csv"
        Kill "c:\tmp\dictinfo.csv"
    On Error GoTo 0
   
    Sheet1.Copy
    ActiveWorkbook.SaveAs FileName:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
    ActiveWorkbook.Close
    Sheet2.Copy
    ActiveWorkbook.SaveAs FileName:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
    ActiveWorkbook.Close
    Sheet3.Copy
    ActiveWorkbook.SaveAs FileName:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
    ActiveWorkbook.Close

    ThisWorkbook.Saved = True      'This statement avoids an Excel warning message about saving the file before closing
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
    Shell "c:\tmp\import.bat"

End Sub

It still does not create the csv files.  I am totally stumped.

Here's the root of the problem/glitch:

If you try to close,open or activate an Excel sheet from a macro the automatic macros like Close, Open and Activate aren't called automatically.

And more important the solution:

add the line
ActiveWorkbook.RunAutoMacro(xlAutoClose)
before the line
ActiveWorkbook.Close
in the first workbook (the last line of the CommandButton4_Click macro)

hope this helps!

I tried adding the line ActiveWorkbook.RunAutoMacro(xlAutoClose)
but I get the error

Run-time error '438'
Object doesn't support this property or method.

The following code doesn't give the error:

With ActiveWorkbook
      .RunAutoMacros xlAutoClose
      .Close
End With

It still will not save. I am just not sure how to fix this one.

Hi uTab,

I tested this (all versions of my code above consolidated) and got it to perform as expected in Excel 2003, with the exception of your last line, as I don't have a bat file, and am not sure what it does.  I don't think that there is anything in here that is incompatible with prior versions, but what version of Excel are you using?

Have you tried stepping through the code line by line?  You should see the file deleted, then saved in each case from this...

Private Sub Workbook_BeforeClose(Cancel as Boolean)
Dim newWb As Workbook
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
    With Sheet1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    Sheet2.Range("B1:FY1").NumberFormat = "0.0000"
    Sheet3.Range("B1:BI1").NumberFormat = "0.0000"
   
    ThisWorkbook.Save
    Set newWb = Workbooks.Add
    With newWb
        Sheet1.Copy .Sheets(1)
        On error resume next
        Kill "C:\tmp\bondinfo.csv"
        On error goto 0
        .SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With

    Set newWb = Workbooks.Add
    With newWb
        Sheet2.Copy .Sheets(1)
        On error resume next
        Kill "C:\tmp\rateinfo.csv"
        On error goto 0
        .SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With

    Set newWb = Workbooks.Add
    With newWb
        Sheet3.Copy .Sheets(1)
        On error resume next
        Kill "C:\tmp\dictinfo.csv"
        On error goto 0
        .SaveAs Filename:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With    
 
    ThisWorkbook.Saved = True      'This statement avoids an Excel warning message about saving the file before closing
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    Shell "c:\tmp\import.bat"
End Sub

Where is the Workbook_BeforeClose?

It should be in the codemodule under ThisWorkbook.
You can check this by opening Visual Basic, clicking (once) on ThisWorkbook in the Project tree (probably in the top left corner of the window) and pressing F7. You should now be able to find your code.
Also check if the words Workbook and BeforeClose are (separate) in the two comboboxes at the top of the codepane.

OK, in your first workbook which opens and closes the one with the BeforeClose event in it, put the following line just before the close statement. Don't forget to change the myClosingWorkbook.xls bit to the filename that is BEING CLOSED.

Application.Run "myClosingWorkbook.xls!Workbook_BeforeClose"

Then in the workbook which is BEING CLOSED, change the Private in the first line of the sub to Public:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Should become:

Public Sub Workbook_BeforeClose(Cancel As Boolean)

Now give it a try.

I get the error
Application.Run "Bondsdbs.xls!Workbook_BeforeClose" can not be found

uTab,

Let's get it out of the Workbook_Close event to see if that makes a difference.  Try this:

In the workbook you want closed, put the following in a STANDARD module:

Public Sub CopyAndClose()
Dim newWb As Workbook
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
    With Sheet1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    Sheet2.Range("B1:FY1").NumberFormat = "0.0000"
    Sheet3.Range("B1:BI1").NumberFormat = "0.0000"
   
    ThisWorkbook.Save
    Set newWb = Workbooks.Add
    With newWb
        Sheet1.Copy .Sheets(1)
        On error resume next
        Kill "C:\tmp\bondinfo.csv"
        On error goto 0
        .SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With

    Set newWb = Workbooks.Add
    With newWb
        Sheet2.Copy .Sheets(1)
        On error resume next
        Kill "C:\tmp\rateinfo.csv"
        On error goto 0
        .SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With

    Set newWb = Workbooks.Add
    With newWb
        Sheet3.Copy .Sheets(1)
        On error resume next
        Kill "C:\tmp\dictinfo.csv"
        On error goto 0
        .SaveAs Filename:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
        .Close
    End With    
 
    With ThisWorkbook
        .Saved = True      'This statement avoids an Excel warning message about saving the file before closing
        .Close Savechanges:=false
    End with
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    Shell "c:\tmp\import.bat"
End Sub

And in the workbook you want to close from, try using this:

Application.Run "Bondsdbs.xls!CopyAndClose"

Let us know,

Ken

Here is the code from the first work book and here is the error I am getting.

I get the error
Application.Run "Bondsdbs.xls!CopyAndClose" can not be found


Private Sub CommandButton4_Click()
    Response = MsgBox("Are you creating a new Bond?", vbYesNoCancel)
    If Response = vbYes Then
        newbond
    End If
    If Response = vbNo Then
        existingbond
    End If
    Workbooks("Bondsdbs.xls").Activate
    Application.Run "Bondsdbs.xls!CopyAndClose"
    ActiveWorkbook.Close
End Sub

OK, go back to putting the following macro in a normal module in the Bondsdbs.xls workbook.

Private Sub Auto_Close()
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    With Sheet1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    Sheet2.Range("B1:FY1").NumberFormat = "0.0000"
    Sheet3.Range("B1:BI1").NumberFormat = "0.0000"
    ThisWorkbook.Save
   
    On Error Resume Next
        Kill "c:\tmp\bondinfo.csv"
        Kill "c:\tmp\rateinfo.csv"
        Kill "c:\tmp\dictinfo.csv"
    On Error GoTo 0
   
    Sheet1.Copy
    ActiveWorkbook.SaveAs FileName:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
    ActiveWorkbook.Close
    Sheet2.Copy
    ActiveWorkbook.SaveAs FileName:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
    ActiveWorkbook.Close
    Sheet3.Copy
    ActiveWorkbook.SaveAs FileName:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
    ActiveWorkbook.Close

    ThisWorkbook.Saved = True      'This statement avoids an Excel warning message about saving the file before closing
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
    Shell "c:\tmp\import.bat"

End Sub


Then try the following code in your button click sub:

Private Sub CommandButton4_Click()
Dim wb As Workbook
    Response = MsgBox("Are you creating a new Bond?", vbYesNoCancel)
    If Response = vbYes Then
        newbond
    End If
    If Response = vbNo Then
        existingbond
    End If
    Set wb = Workbooks("Bondsdbs.xls")
    wb.RunAutoMacros xlAutoClose
    wb.Close
End Sub

Not 100% it will work what with all the other problems we've had, but worth a try.

What is weird is that it is running the macro. But not saving the workbook or creating the csv files.  Here is the exact code that is not working.  I am not getting an error and I san step through all of the code exept the following:
ThisWorkbook.Save
     On Error Resume Next
        Kill "c:\tmp\bondinfo.csv"
        Kill "c:\tmp\rateinfo.csv"
        Kill "c:\tmp\dictinfo.csv"
    On Error GoTo 0
   
    Sheet1.copy
    ActiveWorkbook.SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
    ActiveWorkbook.Close
    Sheet2.copy
    ActiveWorkbook.SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
    ActiveWorkbook.Close
    Sheet3.copy
    ActiveWorkbook.SaveAs Filename:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
    ActiveWorkbook.Close

What, does it just skip those lines of code??

Yep, I can't figure it out.

sorry for not responding earlier, I will have a look at the code today.

Just checking, but when you comment out the first "Application.DisplayAlerts = False", and then run the code, do you get any warning messages popping up??

No, it is just not creating the CSV files.

This is REALLY wierd. Especially since I have tested this and it all worked fine for me. I'm guessing that there is a bug in your Excel settings somewhere. Try re-registering excel to se eif it gets rid of it:

Start -> Run -> Excel.exe /unregserver
Start -> Run -> Excel.exe /regserver

I want to make sure we have the right code. Here is what I have to this point from all of the changes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim newWb As Workbook
    With Application
        '.DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
    With Sheet1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    Sheet2.Range("B1:FY1").NumberFormat = "0.0000"
    Sheet3.Range("B1:BI1").NumberFormat = "0.0000"
    ThisWorkbook.Save
    Sheet1.SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
    Sheet2.SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
    Sheet3.SaveAs Filename:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
    ThisWorkbook.Saved = True      'This statement avoids an Excel warning message about saving the file before closing
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    Shell "c:\tmp\import.bat"
End Sub

Thanks

Just for the record, I dropped your code into a brand new book, and closed the file.

It created the three files in c:\tmp without any issue.

The only change I made was to comment out the Shell "c:\tmp\import.bat" at the end, as I don't have that .bat file.  I even tried running it again, with dispalyalerts set to false, and it overwrote the files with no problem (or prompting) as well.

Have you tried MalicUK's suggestion of reregistering Excel?

I am able to run all of the code just fine but when I close the wb from another wb it will not create the files. Yep, I tried his suggestion.  

OK, first of all, event macros don't run when a book is opened/closed from code. That is a given. So far we have tried various workarounds to this, none have worked, but they should, so maybe we want to play with them again.

1. Have you declared Sheet1, Sheet2 and Sheet3 somewhere? If not then you are using bad naming conventions. I doubt this is the problem, but at the here is the code how I would do it.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim newWb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set ws1 = ThisWorkbook.Sheets("SheetName1")
Set ws2 = ThisWorkbook.Sheets("SheetName2")
Set ws3 = ThisWorkbook.Sheets("SheetName3")

    With Application
        '.DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
    With ws1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    ws2.Range("B1:FY1").NumberFormat = "0.0000"
    ws3.Range("B1:BI1").NumberFormat = "0.0000"
    ThisWorkbook.Save

On Error Resume Next
    Kill "C:\tmp\bondinfo.csv"
    Kill "C:\tmp\rateinfo.csv"
    Kill "C:\tmp\dictinfo.csv"
On Error Goto 0

    ws1.SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
    ws2.SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
    ws3.SaveAs Filename:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
    ThisWorkbook.Saved = True      'This statement avoids an Excel warning message about saving the file before closing
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    Shell "c:\tmp\import.bat"
End Sub

2. In the code that opens and closes the bondsdbs.xls workbook, before you close it try adding the line BEFORE the line which closes the workbook:

Application.Run "bondsdbs.xls!ThisWorkbook.Workbook_BeforeClose"

ALSO you need to change the first line of the Workbook_BeforeClose code FROM:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
TO:
Public Sub Workbook_BeforeClose(Cancel As Boolean)

Now give it a go. If it doesn't work then go on to 3.

3. Time to think outside the box. What we have to do now is move all the code into the workbook which is doing the closing and opening. I will call this workbook Control.xls. I will presume that the code currently looks like this:

Sub RunBondsdbs()
'...
Dim wb As Workbook
Set wb = Workbooks.Open("C:\myPath\bondsdbs.xls", False)
'...
wb.Close 'And then here you would expect the Workbook_Close macro to run.
'...
End Sub

If it doesn't look like this then  please post the code so we can look at it.

So what the code should look like now is:

Sub RunBondsdbs()
'...
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set wb = Workbooks.Open("C:\myPath\bondsdbs.xls", False) '<<Make sure the file path is correct here
Set ws1 = wb.Sheets("SheetName1")
Set ws2 = wb.Sheets("SheetName2")
Set ws3 = wb.Sheets("SheetName3")

    With Application
        '.DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
    With ws1
        .Range("ar1:av1").NumberFormat = "0.0000"
        .Range("G1:I1").NumberFormat = "0"
        .Range("BA1:BA1").NumberFormat = "0"
        .Range("AE1:AE1").NumberFormat = "0"
        .Range("P1:AE1").NumberFormat = "0"
        .Range("AG1:AG1").NumberFormat = "0.0000"
        .Range("AR1:AV1").NumberFormat = "0.0000"
        .Range("AX1:AX1").NumberFormat = "0.0000"
        .Range("AH1:AQ1").NumberFormat = "0"
    End With
    ws2.Range("B1:FY1").NumberFormat = "0.0000"
    ws3.Range("B1:BI1").NumberFormat = "0.0000"
    wb.Save

On Error Resume Next
    Kill "C:\tmp\bondinfo.csv"
    Kill "C:\tmp\rateinfo.csv"
    Kill "C:\tmp\dictinfo.csv"
On Error Goto 0

    ws1.SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
    ws2.SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
    ws3.SaveAs Filename:="C:\tmp\dictinfo.csv", FileFormat:=xlCSVMSDOS
    wb.Saved = True      'This statement avoids an Excel warning message about saving the file before closing
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    wb.Close
    Shell "c:\tmp\import.bat"
'...
End Sub

Lets just see if this works!!

With number 2 I get the error 443 argumant not optional on the following code Application.Run "bondsdbs.xls!ThisWorkbook.Workbook_BeforeClose

The weird thing is that we are getting the macro to run just not all of the macro.  

Here is the code for number 3

Private Sub saveButton_Click()
    Response = MsgBox("Are you creating a new row?", vbYesNoCancel)
    If Response = vbYes Then
        newbond
    End If
    If Response = vbNo Then
        existingbond
    End If
    Workbooks("Bondsdbs.xls").Activate
    Application.Run "bondsdbs.xls!ThisWorkbook.Workbook_BeforeClose"
    With ActiveWorkbook
        .RunAutoMacros xlAutoClose
         .Close
    End With
 End Sub

Thanks for sticking with it.  I took out it killing the CSV files and changed to this line Application.Run "bondsdbs.xls!ThisWorkbook.Workbook_BeforeClose", False and all is working well. You were a great help!!

uTab

Np problem, glad we got it sorted! And thanks for the grade :)