VBA open workbook, exits code

0
votes

I try to open a workbook of the type '.xlsm' using 

Private Function readFiles(ByVal lv_path As String, ByRef lx_wrkShDes As Worksheet)
    'On Error GoTo ErrorHandling
    Dim lx_objectExcel As New Excel.Application
    Dim lx_wrkBkSrc As Workbook
    Dim lx_wrkShSrc As Worksheet
    Dim lx_shrPathObj As Object
    Dim lv_shrPath As String

    Set lx_shrPathObj = CreateObject("scripting.filesystemobject")
    lv_shrPath = Replace(lx_shrPathObj.GetFile(lv_path).ShortPath, mv_longFilePathHelper, "")
    Set lx_wrkBkSrc = Workbooks.Open(Filename:=lv_shrPath, ReadOnly:=True)
    'Using lx_objectExcel.Workbooks.Open WORKS but not Workbooks.Open 
    'lx_objectExcel.Workbooks.Open(Filename:=lv_shrPath, ReadOnly:=True)
    If Not Library.DoesSheetExist(lx_wrkBkSrc, mv_workSheetName) Then
        GoTo ErrorHandling
    End If
    Set lx_wrkShSrc = lx_wrkBkSrc.Sheets(mv_workSheetName)


    'Rest of the function

End Function

It opens the workbook and exits the VBA code immediately. I tried this https://support.microsoft.com/en-us/help/555263, but same results.

It does not exit if I use new instance of Excel using

lx_objectExcel.Workbook.open('path')

I do not want to use a new instance as paste special is not suitable with new instance and opening 100s of Workbooks this way consumes lot of time.

3
vbaexcel
Application.DisplayAlerts = False what kind of alerts you were getting? – Abhinav Rawat
Workbook.Open should be Workbooks.Open – YowE3K
I edited the question – user8003138
Excel.Application.Workbooks.Open does not use a new instance of Excel. It is the same as Workbooks.Open. lx_objectExcel.Workbooks.Open does use a new instance of Excel, only because lx_objectExcel is As New Excel.Application. If you do not want a new instance of Excel, do not create a new instance and remove lx_objectExcel altogether. See if that alone solves your problem. – GSerg
Sorry it should have been "lx_objectExcel.Workbooks.Open" not the "Excel.Application.Workbooks.Open ", The code runs only with "lx_objectExcel.Workbooks.Open", but not with "Workbooks.Open". i.e. new instance it runs but the ame it exits the code; – user8003138

3 Answers

3
votes

Replace:

Workbook.open('path')

With 

Workbooks.Open("PathName")
1
votes

Referring to the Workbook.Open method, the correct syntax for your action is the following

Workbooks.Open("WorkbookPath")

The following code totally works for me

Dim targetWorkbook As Workbook
Set targetWorkbook = Workbooks.Open("YourWorkbookPath")

You are not using it properly. If this still doesn't work, make sure to send the correct path for your file.

0
votes

tried this code with 2 xlsm files (MASTER and SLAVE) and it works fine form me. Office 2013

Dim sText As String
Dim objWB As Excel.Workbook
sText = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
Set objWB = Application.Workbooks.Open(ThisWorkbook.Path & "\SLAVE.XLSM")
objWB.Worksheets("Sheet1").Range("A1").Value = sText
MsgBox "DONE!"