×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Reading Data From Other Workbooks without opening
2

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! Already a Member? Login


Resources

White Paper - The Criticality of the E/E Architecture
Modern vehicles are highly sophisticated systems incorporating electrical, electronic, software and mechanical components. Mechanical systems are giving way to advanced software and electronic devices, driving automakers to innovate and differentiate their vehicles via the electric and electronic (E/E) architecture. As the pace of change accelerates, automotive companies need to evolve their development processes to deliver and maximize the value of these architectures. Download Now
White Paper - Model Based Engineering for Wire Harness Manufacturing
Modern cars, trucks, and other vehicles feature an ever-increasing number of sophisticated electrical and electronic features, placing a larger burden on the wiring harness that enables these new features. As complexity rises, current harness manufacturing methods are struggling to keep pace due to manual data exchanges and the inability to capture tribal knowledge. A model-based wire harness manufacturing engineering flow automates data exchange and captures tribal knowledge through design rules to help harness manufacturers improve harness quality and boost efficiency. Download Now
White Paper - Modeling and Optimizing Wire Harness Costs for Variation Complexity
This paper will focus on the quantification of the complexity related costs in harness variations in order to model them, allowing automated algorithms to optimize for these costs. A number of real world examples will be provided as well. Since no two businesses are alike, it is the aim of this paper to provide the foundational knowledge and methodology so the reader can assess their own business to model how variation complexity costs affect their business. Download Now

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