Vba Stock in Workbook.open [Continues if I press F

Question

When I tried to open an excel file from my macro: I have a 1004 problem, and if I debug the code and press F5 to continue the file is opened without any problem, also If I debug the code line by line works.

Sub openFile2(ruta, fich, destino As Worksheet)

    Application.CutCopyMode = False

    Dim aux As String
    aux = ruta & "\" & fich
    ChDir ruta
    Workbooks.Open Filename:=fich       

End Sub

My values

Ruta

"C:\Users\Usuario\Desktop\Swap\@&&Informes&&@\Informes 2G\Input"

fich

"CNA_GsmRel_Z2_23052013.xls"

Also i tried with:

    aux = ruta & "\" & fich
    Workbooks.Open Filename:=aux

Answer 1

Workbooks.Open requires the complete path Filename so it should be Workbooks.Open(FileName:=aux) instead of Workbooks.Open(FileName:=fich)

Declaring workbook variable wkb gives you control over the workbook for further manipulation.

Save the workbook which houses the code before running the code.

Use below code which is more robust.

Sub sample()
    ruta = "C:\Users\Usuario\Desktop\Swap\@&&Informes&&@\Informes 2G\Input"
    fich = "CNA_GsmRel_Z2_23052013.xls"
    openFile2 ruta, fich, ThisWorkbook.Sheets(1)

End Sub

Sub openFile2(ruta, fich, destino As Worksheet)

    Dim aux As String
    aux = ruta & "\" & fich

    Dim wkb As Workbook

    If IsWorkBookOpen(aux) Then
        Set wkb = Workbooks(fich)
    Else
        Set wkb = Workbooks.Open(FileName:=aux)
    End If

End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0: IsWorkBookOpen = False
    Case 70: IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function