INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Reading Data From Other Workbooks without opening

Reading Data From Other Workbooks without opening

(OP)
My friends,

I have a workbook full of data in F:\Design\DataFiles and the filename is ProfData.xlsm. So the full path is C:\Design\DataFiles\ProfData.xlsm. The first sheet in that file is named HE.

Sub ReadData()
dim wb as workbook
dim ws as worksheet
wb=Workbooks.Open("F:\Design\Datafiles.xlsm",True,True) 'This line works just fine, no error
ws=wb.Sheets("HE") 'This one fails for reasons I can not fully understand
..
wb.close 'This one also fails when it should not.
end sub

What am I doing wrong here. Internet and manuals havent helped yet. I

respects
IJR

RE: Reading Data From Other Workbooks without opening

Firstly check your path. Is it F: or C:, and does your Open statement actually include \ProfData?

With the correct path, add Set to the lines starting wb= and ws=, and it should work.

When you assign any object to a variable in VBA you have to use Set; e.g. Set wb = Workbooks.Open("F:\Design\Datafiles\ProfData.xlsm")

I don't know why your wb = line worked, it didn't work for me.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Reading Data From Other Workbooks without opening

(OP)
IDS, thanks.

Let me correct my first post in which I typed things fast and wrong:

Sub ReadData()
dim wb as workbook
dim ws as worksheet
Set wb=Workbooks.Open("F:\Design\Datafiles\ProfData.xlsm",True,True) 'This line works just fine, no error
Set ws=wb.Sheets("HE") 'This one fails for reasons I can not fully understand
..
wb.close 'This one also fails when it should not.
end sub

My drive is truly F:. Still I can not get it running.

An alternative to get wb right is:

FileType = "ProfData.xlsm"
FilePath = "F:\Design\DataFiles"
Curr_File = Dir(FilePath & FileType)
Set wb = Workbooks.Open(FilePath & Curr_File, True, True)

which works fine but the Set ws= and the wb.close statements do not work.

thanks again
IJR

RE: Reading Data From Other Workbooks without opening

Do you know what the True, True are doing? I left them out in my test.

I didn't find full documentation for the arguments, but if one of the Trues sets the file to ReadOnly it might explain why the Set ws doesn't work, although I don't know why wb.close wouldn't.

Getting late here now, but I'll check in tomorrow.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Reading Data From Other Workbooks without opening

Hmmmmmm???

Worksheets is NOT equivalent to Sheets!!!

CODE

Set ws = wb.Worksheets("HE") 

Also why do you type your code in this window???

Plz COPY your code and PASTE here down to wb.Close.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reading Data From Other Workbooks without opening

(OP)
OK skipVought

will paste here but the Set ws=wb.Worksheets("HE") does'nt work either(tried that many times). Using error handling, I catch the error as Error 91 which does not accept the statement Set ws=Worksheets("HE") as a valid object assignment. On the net there is a single page showing Microsoft reporting a bug with the Workbooks.Open Method(something related to Shift Key). I may also have issues with external references. Excel VBA references tend to be messy.

But will post my code tomorrow here. Because I really want this code to work. Thanks in advance for your time.

IJR

RE: Reading Data From Other Workbooks without opening

In your module containg your code, you ought to have

CODE

Option Explicit 
...at the very to of the code window, as a result of having set Tools > Options -- EDITOR TAB > Code Settings -- Require Variable Declaration.

Before executing your code, perform: Debug > Compile VBAProject

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reading Data From Other Workbooks without opening

Quote (SkipVought)

Worksheets is NOT equivalent to Sheets!!!

But Sheets works (for me). Checking what the difference is I found:

Quote (http://www.vbforums.com/showthread.php?512860-Work...)

The Sheets collection can contain Chart or Worksheet objects.
The Worksheets collection contain only Worksheet objects.

i.e.: Each item (Worksheet) in Worksheets collection is also an item in Sheets collection,
but an item of Sheets collection may not be an item of Worksheets collection.
A Chart sheet is an item of Sheets collection but it is not an item of Worksheets collection.

eg.: A Workbook has 4 sheets with 3 worksheets and a chart.
In this case Worksheets.Count =3, Sheets.Count = 4

So either Sheets or Worksheets should work, and does work for me.

The True, True in the open statement should not be a problem. The first is UpdateLinks and the second is ReadOnly (fortunately the argument descriptions still appear when you enter a function in Excel 2016 VBA, even though the help system has otherwise been totally ruined).

If you step through the code does the Locals window show wb as Workbook/ThisWorkbook after the Workbooks.Open line?

You might also try setting UpdateLinks to False.

I have attached a screenshot of my VBE window after the Set ws line has run.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Reading Data From Other Workbooks without opening

(OP)
Thanks IDS

will try harder and post my code here if all works fine.

regards
IJR

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close