CODEWITHSUNDEEP
vbaexcel
Steven John Lally
Steven John Lally

Reputation: 142

Vba reference workbook via Path

I am making a Macro is Excel 2016 and want to reference a workbook via it's path without opening it.

This is what I have done at the moment

Dim Wb As Workbook
Dim Path As String
Dim Fd As FileDialog

Set Fd = Application.FileDialog(msoFileDialogOpen)
Fd.AllowMultiSelect = False
Fd.Show

Path = Fd.SelectedItems(1)

Set Wb = Workbooks.Open(Path)

However, the last line opens up the file.

I'd like to know the way to set Wb as the workbook where the path leads to without opening it.

Upvotes: 1

Views: 2598

Answers (1)

David Zemens
David Zemens

Reputation: 53663

I just need to reference that workbook so that I can retrieve some data from its cells later on. Can't I store a workbook object in Wb which is the workbook the path leads to?

Nope. Until a file is open in Excel, it isn't a workbook, even though you & I might think of it as a workbook, it's not. It's just a file. But you can get data from closed workbooks.

There are a few ways that this is commonly done (there may be others):

  1. For small amounts of data, use ExecuteExcel4Macro method.
  2. For large amounts of data, use ADO to query the workbook.
  3. For simple solution, just open the file and keep it invisible.

I'll cover the simplest way to do that, which is to just open it invisibly in a new instance of Excel. This is only slightly more complicated than opening it visibly in the same instance of Excel. You'll just need to create a new Excel instance, set its .Visible property to False, and open the file in that instance.

Dim Wb As Workbook
Dim xlApp as Excel.Application 
Dim Path As String
Dim Fd As FileDialog

Set Fd = Application.FileDialog(msoFileDialogOpen)
Fd.AllowMultiSelect = False
Fd.Show

If Fd.SelectedItems > 0 Then
    Path = Fd.SelectedItems(1)
Else
    MsgBox "Nothing selected!"
    Exit Sub
End If

'## Create new instance of excel, and make it invisible
Set xlApp = New Excel.Application
xlApp.Visible = False
'## Open your workbook in this new instance
'   I use ReadOnly:=True to prevent conflict if the file is already open elsewhere
Set Wb = xlApp.Workbooks.Open(Path, ReadOnly:=True)

Make sure that at the end of your procedure you do this, to close the workbook and quit the new/invisible Excel instance:

Wb.Close 
xlApp.Quit

Upvotes: 2

Related Questions