0

I would like to open a workbook via VBA, which is opened from another Excel application. i.e

  1. open your macro file
  2. open excel.exe(2) again
  3. open the destination file via the excel.exe(2)
  4. run macro

If the destination file is just clicked normally with the first application there is no problem in opening the destination file, but through the second application (i.e you see 2 Excel application in the task manager) I get error 9 while opening, and I guess saving is not possible.

Is there a workaround?

        lb_reportWBOpen = Library.isWorkBookOpen(lx_pathReportFile)
        If lb_reportWBOpen Then
            Set lx_wrkBkReport = Workbooks(Library.getFilenameFromPath(lx_pathReportFile))
        Else
            Set lx_wrkBkReport = Workbooks.Open(lx_pathReportFile)
        End If
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 2
    You will be able to open the workbook (as read-only) when it is being used by another Excel instance, but you won't be able to save it (but you can do a SaveAs). – YowE3K Jun 13 '17 at 08:17
  • But even for open I get error 9, only if it is opened by another instance. –  Jun 13 '17 at 08:27
  • But be aware that when you open the workbook (used by another person) as read-only you will receive the last saved state. E.g. person one opens the workbook does some changes over 2 hours **without** saving you will receive the last saved state only (which is 2 hours old). – Pᴇʜ Jun 13 '17 at 08:28
  • use `Workbooks.Open(FileName:=lx_pathReportFile, ReadOnly:=True)` to open it read-only see [Workbooks.Open Method](https://msdn.microsoft.com/en-us/library/office/ff194819.aspx). – Pᴇʜ Jun 13 '17 at 08:30
  • You are probably getting an error 9 because your code (`Library.isWorkBookOpen`) is saying that the file is open (in the other instance of Excel), and your posted code is assuming that the file is open in this instance of Excel. But without seeing your code, it is hard to tell. – YowE3K Jun 13 '17 at 08:30
  • Ah, yes now I get it... It is not able to know if workbook is open this instance or another... Thank you –  Jun 13 '17 at 08:40
  • I'm sorry but is there a way to know which instance the workbook is running in? I was using this approach "https://stackoverflow.com/a/9373914/8003138" –  Jun 13 '17 at 08:44

0 Answers0