0

If there is a shared excel workbook (read-only/notify) that someone is using at the moment and when i try to open it, it asks me.

  • I would like to open it as read-only

(highly important): i would not actually like to open the excel file in the background, but rather copy the data from the closed file. The reason being that the file is huge, and it takes almost 3-4 minutes to open.

Is there a way i can:

  1. open a workbook being used by another user;
  2. copy e.g. Column A into a new spreadsheet (I am only trying to copy info out from the workbook, into a newer one); and do it all while:
  3. without actually opening the file i am referencing in the background, since the file is so huge.

I am looking for a generic skeleton of the code.

-Thank you!

Community
  • 1
  • 1
Code
  • 157
  • 2
  • 3
  • 16
  • Concerning "I would not like to notify the other user that I am trying to access the file", when you click "Notify", it works the other way around; YOU get notified when the read-only property is removed (rather than the other user getting notified of your attempt to open) – Bernard Saucier Mar 20 '14 at 18:37
  • Ah, very true! That makes sense. I am not sure how to edit my previous post, but that bullet-point can be disregarded (as well as #2). Thank you for bringing that up. – Code Mar 20 '14 at 19:28
  • Check **[this post](http://stackoverflow.com/a/9261915/2687063)**, maybe it could help you. – simpLE MAn Mar 20 '14 at 19:50

1 Answers1

1

This will work for you but it has to have a maximum Range(I used 60000) trying to use the whole column will copy values into rows well beyond where you want to. This will put the values into the corresponding cells copy the results and repaste them then remove all rows after the first 0 which will be the result of the formula in the event the cell is empty.

Sub closed_copier()
    Range("A1", "A60000").Value = "='Path\to\your\workbook\[workbook_name.xlsx]SheetName'!A1:A60000"
    Range("A1", "A60000").Copy
    Range("A1").PasteSpecial xlPasteValues
    clearEmptyRows
End Sub


Sub clearEmptyRows()
    Dim lcell As Range
    Dim last_data_row As Long
    For Each lcell In Range("A:A")
        If lcell.Value = 0 Then
            last_data_row = lcell.Row
            Exit For
        End If
    Next lcell
    Range("A" & last_data_row, "A60000").EntireRow.Delete
End Sub

Also know that if this row contains any zeros everything below that will be deleted completely. Opening the workbook would result in far better results but since you requested this not happen your options are limited.

engineersmnky
  • 25,495
  • 2
  • 36
  • 52