CODEWITHSUNDEEP
vbaexcel
agent provocateur
agent provocateur

Reputation: 830

Excel VBA - GetObject(), Open read-only (already open docs)

I am trying to use excel VBA to open another excel document to pull data.
I would like to be able to open and pull data from documents that are already open by other users.
How can I get GetObject function to open the file with read-only parameters?

Upvotes: 0

Views: 6974

Answers (2)

Brock Gion
Brock Gion

Reputation: 956

Use Workbooks.Open method and specify the full path name with ReadOnly set to true:

    Workbooks.Open(FileName:="C:\Users\User\Desktop\excelworkbook.xlsm", ReadOnly:=True)

Assuming that workbook is already open, it will open as read-only. Select all the cells from this read-only workbook, copy, and paste everything into your other workbook.

Here is the code:

    'Hide Prompts and Screen Updating (so can rename workbooks without prompts)
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

        'Create Two WorkBook Objects
        Dim Wb1 As Workbook, Wb2 As Workbook

        'Create a new workbook to copy to
        Set Wb1 = Application.Workbooks.Add()
        Wb1.SaveAs ("WorkBookToCopyTo")

        'Open the other workbook (will open as read-only because it's already open)
        Set Wb2 = Workbooks.Open(FileName:="C:\Users\User\Desktop\excelworkbook.xlsm", ReadOnly:=True)

        'activate read-only workbook, select all cells, copy
        Wb2.Activate
        Wb2.Worksheets(1).Select

        Cells.Select
        Selection.Copy

        'activate other workbook, select all cells, paste
        Wb1.Activate
        Wb1.Worksheets(1).Select

        Cells.Select
        ActiveSheet.Paste

    'Show Prompts Again
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

Upvotes: 2

Rich
Rich

Reputation: 4170

You can activate read only workbooks with the following code:

Workbooks.Open Filename:=file-path, ReadOnly:=True

Upvotes: 1

Related Questions