Reading Data From Other Workbooks without opening 2

[IJR]

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

 

[IDS]

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/

 

[IJR]

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

 

[IDS]

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/

 

[SkipVought]

Hmmmmmm???

Worksheets is NOT equivalent to Sheets!!!

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,

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

 

[IJR]

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

 

[SkipVought]

In your module containg your code, you ought to have

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,

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

 

[IDS]

Worksheets is NOT equivalent to Sheets!!!

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

http://www.vbforums.com/showthread.php?512860-Worksheets-vs-Sheets[/URL]]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/

 

[IJR]

Thanks IDS

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

regards
IJR

 

[justhumm]

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

[URL unfurl="true"]http://www.xtremevbtalk.com/excel/312473-indirectex-indirect-closed-workbooks.html[/url]