Question

Not saving on close

Asked by: uTab

I have two excel files that work together the first on calls the second, transfers data to the second and then closes the second the second excel should save on close but it doesn't so the csv files are never created. I have pasted the code from the second excel file (the one that should be saved) below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    Sheet1.Activate
    ActiveSheet.Range("ar1:av1").Select
    Selection.NumberFormat = "0.0000"
    ActiveSheet.Range("G1:I1").Select
    Selection.NumberFormat = "0"
    ActiveSheet.Range("BA1:BA1").Select
    Selection.NumberFormat = "0"
    ActiveSheet.Range("AE1:AE1").Select
    Selection.NumberFormat = "0"
    ActiveSheet.Range("P1:AE1").Select
    Selection.NumberFormat = "0"
    ActiveSheet.Range("AG1:AG1").Select
    Selection.NumberFormat = "0.0000"
    ActiveSheet.Range("AR1:AV1").Select
    Selection.NumberFormat = "0.0000"
    ActiveSheet.Range("AX1:AX1").Select
    Selection.NumberFormat = "0.0000"
    ActiveSheet.Range("AH1:AQ1").Select
    Selection.NumberFormat = "0"
    Sheet2.Activate
    ActiveSheet.Range("B1:FY1").Select
    Selection.NumberFormat = "0.0000"
    Sheet3.Activate
    ActiveSheet.Range("B1:BI1").Select
    Selection.NumberFormat = "0.0000"
    ThisWorkbook.Save
    Sheet1.Activate
    ActiveSheet.SaveAs Filename:="C:\tmp\bondinfo.csv", FileFormat:=xlCSVMSDOS
    Sheet2.Activate
    ActiveSheet.SaveAs Filename:="C:\tmp\rateinfo.csv", FileFormat:=xlCSVMSDOS
    Sheet3.Activate
    ActiveSheet.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
    Shell "c:\tmp\import.bat"
End Sub

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2005-01-05 at 16:40:28ID21263391
Tags

excel

Topic

Microsoft Excel Spreadsheet Software

Participating Experts
4
Points
500
Comments
40

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. need to modify this code to save in a new workbook
    Id been trying to modify this section with no success to save all the csv files in 1 workbook. Right now it is saving to a worksheeet name. I would like to have a 3 csv files stay in 1 workbook instead. How would I change this? Set wbOrig = ActiveWorkbook For Each ws I...
  2. Workbook_Open() Save As Problem
    Okay, this is what I did. This works to some degree. The problem is it creates the .prn file before the hexstring conversion completes. Private colQueryTables As Collection Private Sub Workbook_Open() Dim qtbTemp As clsQueryTable, n As Integer Set colQueryTables = ...
  3. do not save workbook when closing it
    In relation to http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21852135.html, how do we close the active workbook without prompting me to save the workbook? Workbook should not be saved upon closing it. thanks.
  4. Saveas with fileformat:=xladdin...does not produce a…
    I have an excel workbook that contains VBA subroutines. After I edit the code I always go to File>SaveAs and save the .xls as an addin (.xla). I thought it would be handy to just have my BeforeSave event call a subroutine that would handle this automatically. The subrou...
  5. close a workbook
    Hiya, I have a workbook that opens because i call a macro from it. once the macro is finished, how do i close the workbook? thanks, m

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: brettdjPosted on 2005-01-05 at 18:09:57ID: 12968680

Hi,

Can you please post the code from the first file which I assume closes the second file and should trigger the code above.

If you debug the first code (either place a break point on the code above or step through the first code with F8) does the event above actually get triggered?

Cheers

Dave

 

by: uTabPosted on 2005-01-05 at 19:52:58ID: 12969094

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

 

by: kenpulsPosted on 2005-01-05 at 20:48:38ID: 12969346

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

 

by: uTabPosted on 2005-01-05 at 22:10:09ID: 12969630

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

 

by: kenpulsPosted on 2005-01-05 at 22:17:28ID: 12969654

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

 

by: uTabPosted on 2005-01-05 at 22:30:10ID: 12969698

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

This is driving me nuts

 

by: kenpulsPosted on 2005-01-05 at 22:41:48ID: 12969743

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

 

by: brettdjPosted on 2005-01-05 at 22:45:34ID: 12969758

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

 

by: MalicUKPosted on 2005-01-06 at 01:58:31ID: 12970511

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.

 

by: uTabPosted on 2005-01-06 at 08:00:32ID: 12973263

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

 

by: MalicUKPosted on 2005-01-06 at 08:13:11ID: 12973425

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

 

by: uTabPosted on 2005-01-06 at 11:15:07ID: 12975563

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

 

by: ErwinHoonakkerPosted on 2005-01-06 at 13:33:10ID: 12976958

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!

 

by: uTabPosted on 2005-01-06 at 13:47:35ID: 12977146

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.

 

by: ErwinHoonakkerPosted on 2005-01-06 at 14:07:26ID: 12977306

The following code doesn't give the error:

With ActiveWorkbook
      .RunAutoMacros xlAutoClose
      .Close
End With

 

by: uTabPosted on 2005-01-06 at 15:14:43ID: 12977903

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

 

by: kenpulsPosted on 2005-01-06 at 23:54:36ID: 12980729

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

 

by: ErwinHoonakkerPosted on 2005-01-07 at 00:42:41ID: 12980940

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.

 

by: MalicUKPosted on 2005-01-07 at 01:24:40ID: 12981125

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.

 

by: uTabPosted on 2005-01-07 at 11:16:05ID: 12986776

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

 

by: kenpulsPosted on 2005-01-07 at 11:30:22ID: 12986940

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

 

by: uTabPosted on 2005-01-10 at 10:17:58ID: 13005693

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

 

by: MalicUKPosted on 2005-01-11 at 01:43:32ID: 13011721

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.

 

by: uTabPosted on 2005-01-11 at 08:22:33ID: 13015189

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

 

by: MalicUKPosted on 2005-01-11 at 08:35:29ID: 13015372

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

 

by: uTabPosted on 2005-01-11 at 09:20:59ID: 13015924

Yep, I can't figure it out.

 

by: MalicUKPosted on 2005-01-17 at 01:05:30ID: 13061281

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

 

by: MalicUKPosted on 2005-01-17 at 02:53:25ID: 13061871

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??

 

by: uTabPosted on 2005-01-17 at 08:44:48ID: 13064708

No, it is just not creating the CSV files.

 

by: MalicUKPosted on 2005-01-17 at 08:46:52ID: 13064735

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

 

by: uTabPosted on 2005-01-17 at 09:44:51ID: 13065186

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

 

by: kenpulsPosted on 2005-01-17 at 10:20:24ID: 13065487

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?

 

by: uTabPosted on 2005-01-17 at 16:53:56ID: 13068644

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.  

 

by: MalicUKPosted on 2005-01-18 at 01:34:47ID: 13070491

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!!

 

by: uTabPosted on 2005-01-19 at 10:55:50ID: 13085874

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.  

 

by: uTabPosted on 2005-01-19 at 10:57:59ID: 13085890

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

 

by: MalicUKPosted on 2005-01-20 at 07:19:21ID: 13093686

Option2. Try changing the line:
Application.Run "bondsdbs.xls!ThisWorkbook.Workbook_BeforeClose"

TO:
Application.Run "bondsdbs.xls!ThisWorkbook.Workbook_BeforeClose", False

I think number 3 should be:

Private Sub saveButton_Click()

Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set wb = Workbooks("bondsdbs.xls")
Set ws1 = wb.Sheets("SheetName1")
Set ws2 = wb.Sheets("SheetName2")
Set ws3 = wb.Sheets("SheetName3")

    Response = MsgBox("Are you creating a new row?", vbYesNoCancel)
    If Response = vbYes Then
        newbond
    End If
    If Response = vbNo Then
        existingbond
    End If

    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

 

by: uTabPosted on 2005-01-20 at 10:21:50ID: 13095722

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

 

by: MalicUKPosted on 2005-01-21 at 01:19:30ID: 13101149

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

 

by: uTabPosted on 2005-01-21 at 07:20:46ID: 13103208

Welcome

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...