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
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
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
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
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
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,
for a NUance!
RE: Reading Data From Other Workbooks without opening
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
CODE
Before executing your code, perform: Debug > Compile VBAProject
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Reading Data From Other Workbooks without opening
But Sheets works (for me). Checking what the difference is I found:
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
will try harder and post my code here if all works fine.
regards
IJR
RE: Reading Data From Other Workbooks without opening
http://www.xtremevbtalk.com/excel/312473-indirectex-indirect-closed-workbooks.html