How can I fix dynamic vlookup slowing down when referenced workbook is read only?

[Rodney Roe]

I'm using VBA in excel 2010. I figured out that i could use vlookup to access workbooks without opening them up this is the code i've got.

For i = 0 to Ubound(myData,1) -1
  [A2].Offset(i, 2) = myData(i + 1, p + 1) 'partnumber
  [A2].Offset(i, 3) = "=VLOOKUP(" & CStr([A2].Offset(i, 2).Value) & _
    ",'" & myPath & "Sheet1'!$A$2:$C$10000,3,FALSE)"
  [A2].Offset(i, 3) = [A2].Offset(i, 3).Value 'convert formula to a value
next
 

This works great but the issue i'm having is that if someone has the workbook that i'm referencing open making it read only, the vlookup function drastically slows down. If i have 100+ entries in my array this can take quite a while to finish.

Is there anything i can do to fix this or some other option available that i can try.

[Rabbit]

Your question has been moved to the Access / VBA forum from the VB6 forum.

You could make a copy of the workbook and then run your vlookup against that.

[zmbd]

Rabbit's suggestion of saving the "read-only" workbook to a new file and working against that is great alternative; however, you may not have the most up-to-date data if someone is changing entries.

There is another option that you should try to do, not a guaranteed fix by any means, is the next time you have the workbook open in edit mode, set it up for sharing: Use a shared workbook to collaborate. Depending on what you need, you more than likely can turn off the "track changes" option; however, that depends on what you are doing with the workbook.
I have a workbook out on our network drive that my labtechs enter their data into when I have the database down for backups and this what I do so that I only have one workbook to import and validate data from.

As for why you're seeing a slow down: despite what you may have been lead to believe, the code you are using is actually "opening" the workbook, in that, it has to have a lock on the cells in the worksheet referenced in order to read the values. It is just that the worksheet isn't made the "active" worksheet in the user interface so it is not so obvious.

[Rodney Roe]

@ zmbd that makes sense why it takes so long to get the data.

Thanks guys for the help, do to the nature of what these workbooks handle i can't make them shared so i'll try to make a copy of the workbooks get my data and then delete that copy that way every time i run my program i'll get the latest and greatest.

Thanks,

[Killer42]

On the topic of shared workbooks, I'd like to make a suggestion.

Never, ever make a workbook shared for any reason whatsoever. Shared workbooks are a curse and a plague.

Alright, perhaps I'm being a little melodramatic. But in all seriousness, unless things have changed a lot since Excel 2003 (and if so, hooray!) shared workbooks restrict you from doing pretty much anything useful.

[NeoPa]

While Z is correct in stating that workbooks that are referenced do need to be opened, albeit invisibly, the issue with workbooks that are already open and locked is a level on from that. When a locked file is found it will wait for a timeout period to expire before giving up repeatedly trying to open the workbook.

Opening a workbook is serious time compared to working within an open workbook, but waiting for timeouts to expire is another level of delay entirely.

[zmbd]

Neopa,
I usual, I was simplifying the actual process; however, you are exactly correct with the additional delay. It would be soooooo aggravating for me to have to wait like that!

Killer42,
My experience with shared workbooks is limited at best; therefore, I had not encountered the issues you (and after a quick Google) others appear to have encountered.
What I noted is, in general, that the more complex the workbook, the more likely it is to start behaving badly. SO maybe a tad melodramatic (IMHO - ;-) ) HOWEVER, a warning well taken - especially in a business/production environment where up-time is critical.

[Rodney Roe]

I would like to post my rusults,

I first tried to copy my files using the function "FileCopy" but found out that it errors out if the workbook is already open by someone. Due to the time delay like NeoPa discussed i didn't want to open each file individually and copy or search for my data.

So I used the FileSystemObjec t instead. This is what I did,

    sourceFolder = "\\usbla-s004\misc-eng\ITEMDRAW"
    myPath = "\\usbla-s004\main-eng\CUSTOMER ORDERS\Temp\"
    Set fso = New FileSystemObject
    Set myFolder = fso.GetFolder(sourceFolder)
    i = 0
    ReDim itemdraw(0)
    For Each myFile In myFolder.Files
    DoEvents
        myFile.Copy myPath & myFile.Name 'copy file to temp folder
        itemdraw(i) = myFile.Name
        ReDim Preserve itemdraw(UBound(itemdraw) + 1)
        i = i + 1
    Next
    Set myFolder = Nothing
    Set fso = Nothing
 

By using the FSO i could copy all my workbooks without getting and error if one of them is open by another user to a hidden temp folder which can be overwritten everytime the form loads. I used this on the initialize form event and added the file name to my array. This made my program work a lot faster by searching my array for the specific workbook I needed and addeded it to my vlookup function.

Thanks everyone for your help and info, i'm a self made programmer who loves looking at all these posts to learn more.