Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Referencing Excel Cells in Closed(UnOpened) Worksheets in VBA

Posted on 2003-03-20
Medium Priority
Last Modified: 2012-06-22
Currently I am able to use VLOOKUP to reference tables on a network using the following formula for each cell:


which I then paste to other cells (in VBA) by using

Range(B2:B1000).PasteSpecial xlPasteFormulas

This method is rather slow.

Is it possible (using VBA) to read data from this excel sheet and manually compare the values (rather than use VLOOKUP formula) without actually opening the sheet and get the required data.

Maybe something that would allow me to get data from an excel sheet without opening it.


Question by:csuld
1 Comment
LVL 50

Accepted Solution

Dave Brett earned 375 total points
ID: 8173492
Hi csuld,

I think you will find the process is slow due to linking to an unopened file, not because you are using the Vlookup function.

If you don want to go down this path then the following link gives a procedure for extracring data from an unopened file.




Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question