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/

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

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/

Hmmmmmm???

Worksheets is NOT equivalent to Sheets!!!


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

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

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

In your module containg your code, you ought to have
...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,

_{Just traded in my OLD subtlety...
for a NUance!}

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/

• http://files.engineering.com/getfile.aspx?folder=53955b15-c888-422f-bbcd-98

Thanks IDS

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

regards
IJR

I'm responding late to this thread and I didn't read through the posts all that closely, but I've successfully used this code (as a base) in the past to pull data values from multiple closed workbooks...hope it helps...

http://www.xtremevbtalk.com/excel/312473-indirectex-indirect-closed-workbooks.html