Closing a "At statup, open all files in:" file

Question

I currently have a folder set up on my C drive called "XL Startup". This is referenced to open all files in this folder when Excel is started.

The files that exist within this folder is called "mymacros.xlsm" & "CopyMacro.xlsm". These are files that hold macros and hide in the background like so..

Private Sub Workbook_Open()
    Me.Activate
    ActiveWindow.Visible = False
End Sub

mymacros.xlsm will be updated via a macro in CopyMacro.xlsm. This will make sure mymacros.xlsm stays up to date. However, when I call mymacros.xlsm to close, I get an error saying: Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus. How could I go along of getting this to work?

Code within "CopyMacro.xlsm":

Sub Copy_One_File()

If Dir("C:\XL Startup", vbDirectory) = "" Then
MsgBox "Please create a folder named 'XL Startup' at C:\"
Else

    'Close Current Opened Macro
    Workbooks("C:\XL Startup\mymacros.xlsm").Close SaveChanges:=False 'ERROR HERE

    'Copy File
    FileCopy "S:\newversion\mymacros.xlsm", "C:\XL Startup\mymacros.xlsm"

    'Re-open Macro
    Workbooks.Open "C:\XL Startup\mymacros.xlsm"

    MsgBox "msgbox file copied"
End If

End Sub

@D.O. Yes, its declared in the "At startup, open all files in:" setting. So as soon as excel is opened, it opens mymacros.xlsm, and CopyMacros.xlsm — Brad, Feb 09 '17 at 19:18
What XL version? I can't replicate on XL 2013 Win8.1. (Maybe there are form controls on your sheets that I don't have.) **However,** try adding `Workbooks("C:\XL Startup\mymacros.xlsm").Windows(1).Visible=True` before the `Workbooks().Close` call. — cxw, Feb 13 '17 at 13:43
You may also want to consider changing `mymacros.xlsm` to an Excel add-in (`.xlam`) and updating offline, when Excel isn't running. Add-ins don't have an open window by default, so you don't have to worry about hiding them. — cxw, Feb 13 '17 at 13:45
`Workbooks("C:\XL Startup\mymacros.xlsm").Close` is prolly the cause, Workbooks() doesn't accept paths to files that are open, instead use reference to the file name `Workbooks("mymacros.xlsm").Close` — Han Soalone, Feb 13 '17 at 13:54
@HanSoalone You are a life saver to a simple fix, Nice catch! I greatly appreciate it! If you can place your comment in a form of an answer, i'd be more than happy to reward you! — Brad, Feb 13 '17 at 14:07

score 5 · Accepted Answer · answered Feb 13 '17 at 14:04

You are trying to refer to the Workbook via path, but Workbooks() collection only accepts index so you cannot use a path to workbook ->

Workbooks("C:\XL Startup\mymacros.xlsm").Close SaveChanges:=False

But you can refer to the mymacros.xlsm index ->

Workbooks("mymacros.xlsm").Close SaveChanges:=False

Check the MSDS for Workbooks() collection reference

Use Workbooks(index), where index is the workbook name or index number

Closing a "At statup, open all files in:" file

1 Answers1