VBA - Unable to close open workbook

[njkollauf]

Hello all,

My VBA Code allows the user to choose a workbook to open. First they select the wb from the directory and then that wb is opened. the issue comes when the code tries to close the wb that was just opened. There becomes an error on the red text line. Any idea why this may be? The code is simply calling a string that contains the entire wb path and file name.

Dim intChoice As Integer
Dim strPath As String

MsgBox "Next, please choose the generated calendar that you would like to compare to."
'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
'get the file path selected by the user
strPath = Application.FileDialog( _
msoFileDialogOpen).SelectedItems(1)
Cells(1, 14) = strPath
Else
strPath = 1
End If

'test if workbook exists
If Dir(strPath) <> "" Then
Workbooks.Open Filename:= _
strPath
Range("A2:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks(wb).Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Workbooks(strPath).Close
Else
MsgBox "Comparable generated calendar for this close type does not exist."
End If

 

[AlphaFrog]

Try this...

    [color=green]'test if workbook exists[/color]
    [color=darkblue]If[/color] Dir(strPath) <> "" [color=darkblue]Then[/color]
        Workbooks.Open Filename:=strPath
        Range("A2:L2", Range("A2:L2").End(xlDown)).Copy
        Workbooks(wb).ActiveSheet.Range("A2").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = [color=darkblue]False[/color]
        ActiveWorkbook.Close SaveChanges:=[color=darkblue]False[/color]
    [color=darkblue]Else[/color]
        MsgBox "Comparable generated calendar for this close type does not exist."
    [color=darkblue]End[/color] [color=darkblue]If[/color]

 

[njkollauf]

Try this...

    [COLOR=green]'test if workbook exists[/COLOR]
    [COLOR=darkblue]If[/COLOR] Dir(strPath) <> "" [COLOR=darkblue]Then[/COLOR]
        Workbooks.Open Filename:=strPath
        Range("A2:L2", Range("A2:L2").End(xlDown)).Copy
        Workbooks(wb).ActiveSheet.Range("A2").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
        ActiveWorkbook.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "Comparable generated calendar for this close type does not exist."
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

Works like a dream. Thanks for the code shortening tricks too!

 

[AlphaFrog]

You're welcome. Thanks for the feedback.