+ Reply to Thread
Results 1 to 13 of 13

VBA doesn't return where expected

Hybrid View

  1. #1
    Registered User
    Join Date
    03-07-2013
    Location
    Long Beach, WA
    MS-Off Ver
    Excel 2013
    Posts
    87

    VBA doesn't return where expected

    I have a Summary workbook with a list of Data workbook filenames.
    The objective is for the Summary workbook to open the Data workbooks one at a time and have them
    1) Update their data or not
    2) Run Solver iterations or not
    3) Save
    4) Close and Return

    This has been working until it doesn't work. So, the reasons are a bit hard to track down.

    Right now I'm working on the "Close and Return" part.
    Here is the calling Summary workbook code (simplified a bit):
    Sub Updater()
    Range("D2").Select
       
        Dim cell        As Range
        Dim sFile       As String
        Dim flag        As Boolean
    
         Application.ScreenUpdating = False
    ' Cells in D2:count contain path.filename.xlsm list
        For Each cell In Range("D2", Cells(Rows.Count, "D").End(xlUp)).Cells
            
             sFile = cell.Value
             Workbooks.Open sFile
             ActiveWorkbook.Close SaveChanges:=True
    		 
    		 Do Until WorkbookIsOpen(sFile) = False
    		 Loop
            
        Next cell
    
         Application.ScreenUpdating = True
         Application.EnableEvents = True
    
    End Sub
    What happens is this:
    After the Workbook sFile is opened and all the steps it takes are done, it returns to the code above.
    I would expect it to return to ActiveWorkbook.Close .....
    But, instead it returns to Workbooks.Open sFile and, it appears, there is no value for sFile at that point.
    And, the next step goes to the top of the code shown here and the same/first file is opened again.

    Thanks...

  2. #2
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: VBA doesn't return where expected

    I would dimension some Workbook variables to help you keep track of which workbook is which for example


    Dim wbMaster as Workbook, wbUpdate as Workbook
    Set wbMaster = ThisWorkbook
    'in your loop
    Set wbUpdate = Wrokbooks.Open sFile
    Now it doesn't matter what the active workbook is you can close the newly opened workbook by...
    wbUpdate.Close True

    But to answer you question ... I'm not seeing how the code should not return to Workbooks.Open once you have gone through all the code for that sfile the loop will open a new one. Perhaps a larger code snippet or an example workbook would shed more light.

    cheers
    If your questions has been answered to your satisfaction please don't forget to do the following:

    Add Reputation ... and ... Mark Thread as Solved

    Thanks,

    Ma 10:8b Freely you have received; freely give.

  3. #3
    Registered User
    Join Date
    03-07-2013
    Location
    Long Beach, WA
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: VBA doesn't return where expected

    cplettner: Thanks!
    Set wbUpdate = Wrokbooks.Open sFile
    or, really,
    Set wbUpdate = Workbooks.Open sFile
    with the typo fixed, won't compile. It highlights sFile and says "Syntax error".
    Ah! I fixed it with:
     Set wbUpdate = Workbooks.Open (sFile)
    But the same thing happens.
    I notice that the Locals window in the VBE switches to a new set of variables when the 2nd workbook is opened and, when it returns, the Locals window is completely empty. Thus, it appears, no value for sFile, wbUpdate, etc.
    Last edited by fred3; 08-06-2014 at 11:39 AM.

  4. #4
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: VBA doesn't return where expected

    Try this

    Set wbUpdate = Workbooks.Open(Filename:=sFile)

  5. #5
    Registered User
    Join Date
    03-07-2013
    Location
    Long Beach, WA
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: VBA doesn't return where expected

    OK. Here is a set of example workbooks that fail.
    Open the Summary_Test Workbook and select the Update Macro EDIT and step through it..
    When it switches to Data1.xlsm, you will have to switch back to the VBE.
    You will see the Data1.xlsm variables now...
    When the Data1.xlsm workbook is closed, the VBE will switch back to the Summary_Test Workbook and there will be NO variables shown and it will fail to properly loop.
    If you comment out the workbook closing statement in Data1.xlsm, then the loop in Summary_Test.xlsm will work.

    BUT, one way or another, Data1.xlsm and all the subsequent files have to be closed when they are done being run one at a time in sequence.

    Thanks!
    Attached Files Attached Files

  6. #6
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: VBA doesn't return where expected

    Ok I think I understand now. The reason the loop doesn't work as expected is because the opened workbooks get closed outside of the code (e.i. in the code of the data files)

    I would run the code that is in each of the data files from the summary workbook (assuming the code is the same). If it is not...then we'll have to come up with a different way.

  7. #7
    Registered User
    Join Date
    03-07-2013
    Location
    Long Beach, WA
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: VBA doesn't return where expected

    I'm not sure how to run the code from the summary workbook. The code in each of the data files is the same.
    The data files are supposed to be dual purpose in the sense that they can be run on their own and all of their code pieces should be there for that purpose.
    Then, in order to update all of the data files and summarize the results, the Summary file needs to control that process.
    I suppose that there could be "identical" modules for each purpose. Modules in the data files that run from the data files and modules in the Summary file which run ON the data files... ?
    What does the latter look like?

    How about closing the data files from the Summary file? That seems to be an easy way around this issue.

  8. #8
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: VBA doesn't return where expected

    See if this works for you...I've commented the code, but if you have any additional questions let me know.

    Summary_Test.xlsm

  9. #9
    Registered User
    Join Date
    03-07-2013
    Location
    Long Beach, WA
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: VBA doesn't return where expected

    cplettner: Thanks so much! This is a very different construct and it appears(i.e. appeared) to be working.
    Last edited by fred3; 08-08-2014 at 01:58 PM. Reason: had a typo causing errors

  10. #10
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: VBA doesn't return where expected

    I'm glad you found your error. Typos are notorious for stop code dead in its tracks. if you question has been resolved please make sure to mark thread as resolved and reputation is always appreciated.

  11. #11
    Registered User
    Join Date
    03-07-2013
    Location
    Long Beach, WA
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: VBA doesn't return where expected

    Well, the typo was only in the latest code and not in the original that had problems. Your help was a big deal in getting on with this project!

  12. #12
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: VBA doesn't return where expected

    So...it's still not working?

  13. #13
    Registered User
    Join Date
    03-07-2013
    Location
    Long Beach, WA
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: VBA doesn't return where expected

    Yes, it's working. The note about the typo was in the "edit" of my message at 10:45.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Class use doesn't work as expected
    By seattle911 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-21-2014, 12:22 PM
  2. Formula doesn't work as expected????
    By muish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2013, 04:30 AM
  3. Trimmed mean doesn't give expected result
    By Winton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-03-2012, 06:31 AM
  4. IF calculation doesn't return expected results
    By rdoty in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2010, 05:07 PM
  5. Recorded macro doesn't seem to provide expected result
    By Jeroen1000 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-21-2009, 06:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1