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.
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.
7 1916
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 5,501
Expert Mod 4TB
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.
@ 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,
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 32,556
Expert Mod 16PB
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 5,501
Expert Mod 4TB
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.
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 FileSystemObject 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: TheKeith |
last post by:
I'm writing a script with a while loop in it. How can I get it to go slower
so that it doesn't appear to happen all at once--so that it looks
animated--basically. I tried the setTimeout(500) in the...
|
by: vaury |
last post by:
I'm having difficulty making a list (both drop down and radio button) read
only; I am successful when I attempt to make a text field read only. Any
suggestions on what java script I use to...
|
by: tony |
last post by:
I'm using PHP 5 on Win-98 command line (ie no web server involved)
I'm processing a large csv file and when I loop through it I can process
around 275 records per second.
However at around...
|
by: Dan Stromberg |
last post by:
I have two different python programs that are slowing down quite a bit as
their memory use goes up.
I'm not really sure if this is some sort of CPU cache effect, or if it's
something about...
|
by: davidst95 |
last post by:
Hello,
I have a program that runs a long process. I tried to add a thread
to check if the use hit a cancel button to abort the process.
Dim t As Threading.Thread
t = New...
|
by: myke |
last post by:
Whenever I use the DVD ROM on my PC, it slows the computer down really badly. Whether I use it to copy files onto a CD/DVD, or install a program, the CPU usage goes to around 55% (which is totally...
|
by: karl98 |
last post by:
Does anyone else experience slowing down of a web conferencing session when 5 or more people join in the meeting? Our web conferences slow down considerably and are driving me nuts.
|
by: Dan Tallent |
last post by:
A textbox has a attribute for ReadOnly. This seems like such a simple
concept. When a textbox is set to read only the user cannot change the
contents of the field.
I have been trying to find...
|
by: shale |
last post by:
Hi,
I have an ASP scripted business web application connected to a SQL 2000 server, that suddenly after a few hours is slowing down.
it becomes so slow it can take up to a minute to load each...
|
by: Echosei |
last post by:
Hi there,
I recently purchased an HP pavillon dv3
-CORE i7
-Windows 7 premium
-300GB Harddrive
-2GB RAM
PROBLEM:
The whole system slows down exceedingly everytime I use it. I think it's...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |