none
Having to reboot computer in order to run macros RRS feed

  • Question

  • I'm new to writing macros, so maybe there is something I'm doing wrong. or maybe there is something wrong with my software.

    I have a report I'm building that involves 3 files - 2 data files (.xlsx) and the report builder file (.xlsm).  The Report Builder files has connections to the 2 data files and it has a query in it that does a join between the two files.

    The Report Builder file also has macros that run.  The weird thing is that I have to save, close the .xlsm file and reboot my computer then reopen the file and run the macros before they will run.  If I don't the macro doesn't run when I click the Run button. Actually, it seems to run for a split second as I can see the text "running" in MS VBA, but then it stops. This is so frustrating.

    Is there something I'm missing? Can one not go from queries to macros?


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.


    • Edited by run4it Wednesday, February 26, 2020 10:40 PM
    Wednesday, February 26, 2020 10:36 PM

All replies

  • Hi,

    I suppose they cannot give/suggest an answer, unless you provide your code.
    It would be the best for you to share your the report builder file (.xlsm) via cloud storage such as OneDrive, Dropbox, etc.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Thursday, February 27, 2020 12:21 AM
  • the macro doesn't run when I click the Run button. Actually, it seems to run for a split second as I can see the text "running" in MS VBA, but then it stops. This is so frustrating.

    Is there something I'm missing? Can one not go from queries to macros?



    It sounds like the VBA Editor's natural tendency to suck loudly when your code encounters errors.  You should check the VBA Editor when this happens and see if the STOP button does anything.  You know up in the top toolbar where there are the PLAY/PAUSE/STOP buttons?  You might have to stop something that never really finished the first time you ran the macro in order to run it a second time.
    Thursday, February 27, 2020 12:25 AM
  • To: run4it
    re: code won't run

    If you use the code line... Application.EnableEvents = False then
    at every exit point in the code you must use Application.EnableEvents = True

    When Excel starts EnableEvents is set to True.
    Check out your code. (I can't)



    The free Excel workbook "Professional_Compare" is now available at MediaFire.
    (compares every cell Or each row against all rows (two worksheets) - choice of compare type)
    Now includes a Clean Data option.
    Download (no ads) from:  http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents


    Friday, February 28, 2020 12:26 AM
  • Below is the first macro I run after running the query in Excel.  It's pretty simple actually and works fine - after I reboot my computer.  A co-worker though that there might be some memory issue. But I'm new to this and don't know how to troubleshoot for that. 

    Sub CollegeNames()
    
    Dim ws As Worksheet
    Dim x As Long
    Dim fndList As Variant
    Dim rplcList As Variant
    
     
    'get rid of instructure.com in preparation for the find/replace below.
    ActiveSheet.Range("E:E").Replace ".instructure.com", ""
    
    
    fndList = Array("bates", "bc", "bigbend", "btc", "canvas.highline.edu", "canvas.northseattle.edu", "cascadia", "cbc", "ccs", "centralia", "clarkcollege", "cptc", "edcc", "everettcc", "ghc", "egator.greenriver.edu", "lcc", "lwtech", "olympic", "pencol", "pierce", "piercemil", "rtc", "sbctc", "seattlecentral", "shoreline", "skagit", "southseattle", "spscc", "tacomacc", "wcc", "wvc", "wwcc", "yvcc")
    rplcList = Array("Bates", "Bellevue", "Big Bend", "Bellingham", "Highline", "North Seattle", "Cascadia", "Columbia Basin", "Spokane", "Centralia", "Clark", "Clover Park", "Edmonds", "Everett", "Grays Harbor", "Green River", "Lower Columbia", "Lake Washington", "Olympic", "Peninsula", "Pierce", "Pierce JBLM", "Renton", "SBCTC", "Seattle Central", "Shoreline", "Skagit", "South Seattle", "South Puget Sound", "Tacoma", "Whatcom", "Wenatchee Valley", "Walla Walla", "Yakima Valley")
    
    
    'Loop through each item in Array lists
      For x = LBound(fndList) To UBound(fndList)
        'Loop through each worksheet in ActiveWorkbook
          For Each ws In ActiveWorkbook.Worksheets
           ws.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
              LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True, _
              SearchFormat:=False, ReplaceFormat:=False
          Next ws
    
      Next x
    
    
    End Sub
    I wonder if there is a way I might make my loop more efficient.  I only have one sheet and the names that I am trying to clean up are in column E.

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.


    • Edited by run4it Friday, February 28, 2020 7:07 PM
    Friday, February 28, 2020 7:06 PM
  • To:  run4it
    re:  Excel won't run

    Your co-worker might be right.

    I see two possible changes you could make...
    1.  Doing any operation on an entire column should be avoided (1mm rows).
         Even limiting E:E to E1:E100000 or so, would be better.
    2.  Same for entire worksheets...
         Replace ws.Cells with ws.UsedRange.Cells

    Friday, February 28, 2020 7:43 PM
  • Doesn't the UsedRange property only reference the rows that have data? I have changed it to only look in column E, but I still have to reboot.

    With ActiveSheet.UsedRange.Columns("E")
    For x = LBound(fndList) To UBound(fndList)
           .Replace What:=fndList(x), Replacement:=rplcList(x), _
              LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True, _
              SearchFormat:=False, ReplaceFormat:=False
          Next
    End With
    


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Friday, February 28, 2020 8:27 PM
  • To: runrit
    re:  recent code change

    That new code...
      "With
    ActiveSheet.UsedRange.Columns("E")"
    should have helped."
    I have no more suggestions.

    Friday, February 28, 2020 8:48 PM
  • I have a new clue.  I actually determined that some of the code is running withouth having to reboot.  This is the line that is not rebooting. This is the first line of code after my Dim block

    ActiveSheet.UsedRange.Columns("E").Replace ".instructure.com", ""


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.


    • Edited by run4it Friday, February 28, 2020 9:55 PM
    Friday, February 28, 2020 9:54 PM
  • To:  run4it
    re:  still a problem

    Ok, I've got another suggestion.
    Find the last cell on the data sheet your code is massaging.
    On the keyboard use Ctrl + End.
    If the cell found is way below and or to the right of the actual last cell with data then that needs to be corrected.

    Clearing or deleting the blank cells, usually doesn't work.
    You need to select the entire row just below the actual last cell with data.
    Next, press the Ctrl + Shift + Down Arrow to select the rest of the rows.
    Then right-click the row header and choose delete from the popup menu.
    That physically removes the rows and replaces them.

    Repeat for the columns.

    Friday, February 28, 2020 11:04 PM
  • I"m afraid that had no effect.  It's almost as if it doesn't know the ActiveSheet unless there is a fresh reboot. Is there any code I can put in at the start of my macro that will ensure the sheet is "active?" There is only one sheet.

    Edit- I put this in to check if it knew the active sheet and it does: MsgBox ActiveSheet.Name 


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.


    • Edited by run4it Friday, February 28, 2020 11:43 PM
    Friday, February 28, 2020 11:37 PM
  • To be honest I'm not even sure if ADO is a thing anymore, with VBA and MS Office, but it used to be that you could open an Excel Workbook using ADO and the same JET engine you use to manipulate MS Access databases.  Then you could do this kind of manipulation via regular SQL Queries, treating the Workbook as a schema and each workbook as a table.

    I have no idea where it would be or if I would really still have it now, but I may be able to find an old code project (and by old I mean VBA from Excel 03 or 07 at the newest) if you aren't already familiar with database stuff.  ADO is a lot friendlier than dinking around with Excel's kludgy interface.

    Friday, February 28, 2020 11:54 PM
  • To:  run4it
    re:  active sheet

    If there is only one sheet, why does your code loop thru the ActiveWorkbook.Worksheets?

    As for avoiding using ActiveSheet (a good idea), use the name of the workbook and worksheet...
      Application.Workbooks("enter name").Worksheets("enter name")
    -or-
    If there is only one worksheet then this can be used...
      Application.Workbooks("enter name").Worksheets(1)

    Friday, February 28, 2020 11:58 PM
  • I have edited my code so it only runs through the specific column on the single worksheet.  I still have the same issue.  I put my files out on a shared drive where I work so someone else could test in order to rule out an issue with the software on my computer.  This person experienced the same issue.  This is making development extraordinaly painful to have to keep rebooting.

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Tuesday, March 3, 2020 11:54 PM
  • I have edited my code so it only runs through the specific column on the single worksheet.  I still have the same issue.  I put my files out on a shared drive where I work so someone else could test in order to rule out an issue with the software on my computer.  This person experienced the same issue.  This is making development extraordinaly painful to have to keep rebooting.

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Very early in your thread I suggested that you use the VBA Editor's debugging tools.  What have been the results of any real attempt you made to read up on and employ those built-in options?

    Most recently I suggested that you try using ADO in VBA to open your Workbook as virtual database and employing SQL queries instead of klunking around with VBA's interface to Workbooks and Worksheets.  What have been the results of any real attempt you made to read up on and experiment with that built-in option?

    Is this thing on?  I'm starting to wonder if I'm hallucinating my posts to the thread altogether.

    Wednesday, March 4, 2020 12:14 AM
  • Stepping though in the VBA Editor reveals nothing.  If I do it right after a restart of my computer the code works. If I step through after refreshing the data in my spreadsheet without restaring my computer nothing happens on that line of code with the .Replace.  the next lines of code run fine, but the one line of code only runs after a reboot.

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Wednesday, March 4, 2020 9:27 PM
  • I came up with a different way of doing it that is working without a reboot. I am forcing the selection of the column rather than just naming it.  I don't understand why this is necessary, but it works.

    ActiveSheet.Range("E:E").Select
    Selection.Replace What:=".instructure.com", Replacement:="", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Wednesday, March 4, 2020 10:26 PM
  • I came up with a different way of doing it that is working without a reboot. I am forcing the selection of the column rather than just naming it.  I don't understand why this is necessary, but it works.

    ActiveSheet.Range("E:E").Select
    Selection.Replace What:=".instructure.com", Replacement:="", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Weird.  Sounds like something you might have found while stepping through with debugging tools.

    I used to have a signature on MSDN that read:  "It never hurts to try.  In the worst-case scenario, you'll learn something."  GMTA.

    Wednesday, March 4, 2020 11:30 PM