VBA open workbook, exits code

Question

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.

`Application.DisplayAlerts = False` what kind of alerts you were getting? — Abhinav Rawat, Sep 07 '17 at 07:05
`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, Sep 14 '17 at 08:19
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; — , Sep 14 '17 at 08:24
Make sure you don't have `on error resume next` and the the [error handling mode](https://stackoverflow.com/q/12687105/11683) is set to Break in class module. If that does not change anything, try `Workbooks.Open` on a dummy blank file instead of the real one. — GSerg, Sep 14 '17 at 08:31
I also tried in a fresh clean workbook with just workbooks.open("Exact Path") no error handling; still the same problem. Works with new instance but not with the same — , Sep 14 '17 at 08:35
A few of them; company related... Also Workbooks.Open method works normally only for .xls files but only .xlsm it is creating the problem — , Sep 14 '17 at 08:40
Try disabling all non-standard addins and see what is the security and trust settings for opening files with macros. Try making a macro-enabled document trusted and see if that opens. — GSerg, Sep 14 '17 at 08:44
I disabled all the addins... I cannot change the acro-settings due to user policy; but the macros cannot execute in the directory it is placed in. I also created a dummy file; it is not able to open it — , Sep 14 '17 at 09:57

score 3 · Answer 1 · answered Sep 07 '17 at 07:12

3

Replace:

Workbook.open('path')

With

Workbooks.Open("PathName")

answered Sep 07 '17 at 07:12

Shai Rado

33,032
6
29
51

I'm sorry the syntax error was a typing mistake in the question; But as I said earlier it opens the file and then exits the code – Sep 08 '17 at 07:24
@user1589759 then your error comes from somewhere else, post the rest of your code – Shai Rado Sep 08 '17 at 07:26

Teasel · Answer 2 · 2017-09-07T12:28:57.977

1

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.

edited Sep 07 '17 at 12:28

answered Sep 07 '17 at 12:22

Teasel

1,330
4
18
25

score 0 · Answer 3 · answered Sep 07 '17 at 07:24

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!"

VBA open workbook, exits code

3 Answers3

Linked

Related