0

I want to use the Workbooks object (for example) as below:

Debug.Print Workbooks("C:\temp\file1.xlsm").path

while the file1 workbook is closed.

But I'm encountering a Subscript out of range Run-time error.

What is wrong and how can I work with the Workbook object without needing to open the appropriate workbook?

TylerH
  • 20,799
  • 66
  • 75
  • 101
mgae2m
  • 1,134
  • 1
  • 14
  • 41
  • 1
    Not entirely sure, but to work with `Workbook` object and access it's properties and methods you need to open it anyway. – AntiDrondert Oct 02 '17 at 09:28
  • So, please offer a better suggestion than using `Workbook` object. An alternative object instead of `Workbooks` object for accessing a Workbook `.Copy`, `.path` and `.FulName` properties and methods quietly – mgae2m Oct 02 '17 at 09:30
  • Don't get what you want. You just want to write `C:\temp\` ? If you know the full name of a file, there a lots of ways to get the folder, see for example https://stackoverflow.com/a/5375236/7599798 – FunThomas Oct 02 '17 at 09:34
  • You already have the path. Do you mean to test for its existence? Otherwise, as above, you need to have the object open in order to access its property i.e. path. – QHarr Oct 02 '17 at 09:35
  • I need have access the `Workbooks` object property and methods, as shown in above comment, for various applications. – mgae2m Oct 02 '17 at 09:36
  • @QHarr, Of course. I have the path. and "test.xlsm" is existed. I need access its `Workbooks` object properties and methods, without open the workbook, quietly. – mgae2m Oct 02 '17 at 09:46
  • 1
    According to MSDN the `Workbooks` collection will only allow access to workbooks currently opened in Excel (see the this link: https://msdn.microsoft.com/VBA/Excel-VBA/articles/workbooks-object-excel ). Thus I think there is no way to do what you want in VBA (without actually opening the file in excel). – bassfader Oct 02 '17 at 09:56
  • Related [question](https://stackoverflow.com/questions/579797/open-excel-file-for-reading-with-vba-without-display). You still need to **open** it. – AntiDrondert Oct 02 '17 at 10:01
  • Because of this unfortunate limitation, I'm so sorry. – mgae2m Oct 02 '17 at 10:04
  • 1
    It's possible to read/write to an Excel file without opening if you use ADO. See http://www.rondebruin.nl/win/s3/win024.htm for an example. – Socii Oct 02 '17 at 10:04
  • @Socii: That still opens the workbook - you just don't see it. You cannot read a book without opening it, and you cannot read a file without opening it. If you mean "don't *display* it" - that's a different story. – FunThomas Oct 02 '17 at 10:21
  • I found the solution is in ADO that Socii guide. – mgae2m Oct 02 '17 at 10:47
  • @FunThomas - ADO does not require the workbook to be opened. – SJR Oct 02 '17 at 11:02
  • Depends what you define as "open". It's doesn't show the excel file, it has no GUI, but for sure it opens the file. That's basically how you read/write data from/to a file. – FunThomas Oct 02 '17 at 11:14
  • ADO approach solution for this question is appreciated. – mgae2m Oct 02 '17 at 12:12
  • 1
    correct me if i am wrong, but i do not think that ADO allows the use of the Workbooks object as you stated in your question. there is still no way to do `Debug.Print Workbooks("file1.xlsm").path` – jsotola Oct 02 '17 at 18:58
  • @jsotola, That's right. Following the above discussion, I came to the conclusion that there is no way to use `Workbooks` object in this matter, so I think at least reaching above methods and properties with alternates, as ADO for `Workbooks.Open` and more... – mgae2m Oct 02 '17 at 19:08
  • Which is major needed, reach the Workbook content without open it. `Workbooks("C:\file1.xlsm").path` is in hand (This was only an example about using `Workbooks` object), when we use the path for calling Workbook. – mgae2m Oct 02 '17 at 19:19

0 Answers0