Excel 2003 - Access data from closed workbook

>>2: from File2.xls!Sheet1 get the data from Sheets1(A1).Current Region, store in an arrMyData,
>>and provide message 'Data Collected'

If you mean get the data for A1's CurrentRegion from File1.xls!Sheet1, you can't unless you open File1.xls

hi matthewspatrick

yes i did mean get data from file1.xls!sheeet1(a1).current region and store in an array in file2.xls (sorry about that typo)

i also know that i have seen somewhere else the ability to access data from a closed workbook without opening it ... and not via formula

and in just thinking about that
if file2.xls has formulae that link to file1.xls, when we open file2.xls it asks for an update/decline - if update, it gets the data from file2.xls without opening file2.xls

therefore it must be possible to get data from file1.xls when it is closed

true?

Also, here is someone else's code to answer this need, but I cannot make sense of it in terms of my original description.
If this can be reworded to match my situation that would suffice as an answer.

Sub test() 
    Dim myFolder As String, mySheet As String, myFile As String 
    Dim myRange As String, myFormula As String, DestRng As Range 
    myFolder = ThisWorkbook.Path & "\" 
    mySheet = "Sheet1" 
    myFile = "file2.xls" 
    myRange = "A1:B100" 
    myFormula = "='" & myFolder & "[" & myFile & "]" & mySheet & "'!" 
    myFormula = myFormula & Split(myRange, ":")(0) 
    Set DestRng = Range("D1") 
    With Range(myRange) 
        DestRng.Resize(.Rows.Count, .Columns.Count).Formula = myFormula 
    End With 
End Sub

Select allOpen in new window

You said "and not via formula", but that code is using formulas.
You may be thinking of the use of ADO to query a closed workbook but that does not strictly give you the ability to extract A1's Currentregion, unless there is nothing else on the sheet. Code to extract the whole data from a sheet would look like:

Sub GetData()
   ' Sample demonstrating how to return a recordset from a workbook
   '############################################
   ' Requires a reference set to Microsoft ActiveX Data Objects 2.n library
   '############################################
   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
   Dim varData As Variant
   Set cn = New ADODB.Connection
   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & ActiveWorkbook.Path & "\File1.xls;" & _
         "Extended Properties=""Excel 8.0;HDR=Yes;"""
      .Open
   End With
   strQuery = "SELECT * FROM [Sheet1$]"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
   ' dump array of data into variable
   varData = rst.GetRows
   varData = Application.Transpose(varData)
   rst.Close
   Set rst = Nothing
   cn.Close
   Set cn = Nothing
End Sub

Select allOpen in new window

for example.

Hi rorya
Actually that code doesn't use 'formulae' per se - he has declared a variable 'myFormula' into which it seems he has tried to set it to
FullPathofFile/ColRow dimensions of the CurrentRegion
That sounds like the right way to go, but i do not understand    myFormula = myFormula & Split(myRange, ":")(0)
i also don't understand why myRange starts with = "A1:B100"
i am convinced that that snippet should work if only i can understand it and wrap it around my code :)

the ado option is not going to work in this case - the end users are not technical people and i have no way to control their machines

you said it won't work unless "unless there is nothing else on the sheet" and that is a fact ... there is nothing else on the sheet but one continuous set of records starting at A1

are you able to build that code then?

I just did build that code...

Your code is using formulas:

DestRng.Resize(.Rows.Count, .Columns.Count).Formula = myFormula 

Select allOpen in new window

Not sure what you mean about controlling user machines.

Hi rorya

when i say "and not via formula" that means no forumlae in File2.xls that link to File1.xls i.e. i don't want a formula-based solution

if the code to do this uses formulae that is perfectly fine - just not =file2.xls!sheet1 in cells in File1.xls

by not controlling other machines (and there will be hundreds of disparate users all over the place) i mean i cannot establish your "Requires a reference set to Microsoft ActiveX Data Objects 2.n library" on their machines - so that is probably not an option?

does that help?

Well, that code is putting exactly that sort of formula into your cells.

You set the reference once in the workbook and it travels with it. You can also late bind the code so that no references are required.

so it seems i am still no further advanced? can we go back to the question ... with correct file names ...

I am coding vba in File2.xls and wish to draw data from File1.xls!sheet1 without opening File1.xls.  If File2.xls exists it will always be in the same folder as File1.xls.

The code needs to do the following
1: test for existence of File1.xls
If Not-Exist handle error and provide message to user
If Exist proceed
2: from File1.xls!Sheet1 get the data from Sheets1(A1).Current Region, store in arrMyData, and provide message 'Data Collected'
3: close all links with File1.xls

the actual code which i have working correctly is below.
i wish to remove the two Worksbooks.Open .Close statements because this approach has the user choosing 'Do you wish to Save' Yes/No on the file from which the data is drawn, and it requires hard-coding the file which i don't want to do

Workbooks.Open ("F:\_ERA 2012\Citations\RCI Tool\list-of-jnls.xls")

NumArts = Range("A1").CurrentRegion.Rows.Count - 1

Dim ArtsArr()

ReDim ArtsArr(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count + 1)

ArtsArr = Range(myRange) 

Workbooks("list-of-jnls.xls").Close

Select allOpen in new window

nearly ... what if strFile does not find the file it is looking for?

and i guess i should ask the question ... are there any other things i should code to deal with?
we can be 100% certain that the correct data will be found in file1.xls!sheet1

agh ... that code opens the workbook ... i don't wish it to be opened at all

"the actual code which i have working correctly is below.
i wish to remove the two Worksbooks.Open .Close statements because this approach has the user choosing 'Do you wish to Save' Yes/No on the file from which the data is drawn, and it requires hard-coding the file which i don't want to do"

If you do not wish to open it, then formulas or ADO/DAO are your options.

So, back to ADO:

Sub Blah()
Dim strFile as String
Dim arrMyData
strFile = thisworkbook.path & "\File1.xls"
if Dir(strFile) <> "" then
   arrMyData = ArrayFromWorkbook(strFile, "Sheet1")
   msgbox "Data Collected"
End If
' more code

End Sub


Function ArrayFromWorkbook(strFile As String, strSheet As String) As Variant
   ' Sample demonstrating how to return a recordset from a workbook
   Dim cn As Object, strQuery As String, rst As Object, strConn As String
   Dim varData As Variant
   Set cn = CreateObject("ADODB.Connection")
   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strFile & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes;"""
      .Open
   End With
   strQuery = "SELECT * FROM [" & strSheet & "$]"
   Set rst = CreateObject("ADODB.Recordset")
   rst.Open strQuery, cn
   ' dump array of data into variable
   If Not rst.EOF Then ArrayFromWorkbook = Application.Transpose(rst.GetRows)
   rst.Close
   Set rst = Nothing
'   cn.Close
   Set cn = Nothing
End Function

Select allOpen in new window

Hi rorya

I am unsure if we are on the same page with the formula statement.  I do not wish to have a bunch of formulae in File2!Sheet1 that link to File1!Sheet1 i.e. ='File2!sheet1'A1 and etc.

It is fine to use formula in the macro that collects the data.

I know I have seen this done without ADO or other similar techniques ... please confirm that the ADO method requires certain installs/libraries on the end users machine.

"I do not wish to have a bunch of formulae in File2!Sheet1 that link to File1!Sheet1 i.e. ='File2!sheet1'A1 and etc."

All your macro does is enter those formulas. If that's ok then fine.
ADO does require an ADO library to be present. I've never seen a Windows machine that didn't have one.

Hi rorya,

I am struggling to see if you are trying to help by answering this question, or to just test me out on everything i say.  

1: I do now want to build a sheet that looks in another closed sheet by the use of formulae
2: i don't care how the macro works so long as it meets the rough criteria
a) do not open
b) don't have formulae in the open book that refer to the closed book
c) avoid Save/Don't save prompts

So, if the macro creates a dummy sheet in the open book, gets the data from the closed book using formula in that sheet, then puts it in an array, and then deletes the sheet with formulae, then that is fine.  I really don't give a toss as long as the above criteria are met.

Please remember the original request to have the existence of File2.xls tested and errors handled appropriately.

I don't know what else to say ... if you can do this great and I will gladly give the points.  If you cannot then please stop dancing around with every thing i say and leave it alone.

Thank you in advance.

Is it easier to go back to the beginning ... the below code is what I have.  It is code that I wrote that works.

I wish to avoid Save prompts by not opening list-of-jnls.xls at all.

I wish to test for the existence of list-of-jnls.xls in the same folder as the already open workbook, and to gracefully inform the user of non-existence.

Workbooks.Open ("F:\_ERA 2012\Citations\RCI Tool\list-of-jnls.xls")
Dim ArtsArr()
ReDim ArtsArr(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count + 1)
ArtsArr = Range(myRange)
Workbooks("list-of-jnls.xls").Close

So the two lines ReDim ArtsArr() and Range("A1").CurrentRegion need to be replace with code that does those things.

Cool?

I guess I'm done then. I've given you code that opens the workbook but avoids the prompts, and I've given you code that doesn't open the workbook. In both cases the code tested for the existence of the file.
If you don't want to use either, that's fine by me.

still trying to get this to work ... so if there are no more takers i will post the end result and then decide what to do

OK, so I will have to go with Save=false model since nothing else is coming.

The code below populates the array with rows 1 to n.  Row 1 contains heading data.  I don't wish that heading data to be in the array.  Can you please amend this code to exclude row 1 from process of populating the array.

Dim wbk as workbook
Dim strFile as String
Dim arrMyData
strFile = thisworkbook.path & "\File1.xls"
if Dir(strFile) <> "" then
   set wbk = Workbooks.Open(strFile)
   arrMyData = wbk.Sheets("Sheet1").Range("A1").CurrentRegion.Value
   wbk.Close SaveChanges:=False
   msgbox "Data Collected"
End If

Finished code that works, but not quite according to specs.

' load articles into ArtsArr
Dim VirtualBook As Workbook, FilePointer As String
FilePointer = ThisWorkbook.Path & "\list-of-jnls.xls"
If Dir(FilePointer) <> "" Then
   Set VirtualBook = Workbooks.Open(FilePointer) ' create instance of specified file
   NumArts = Range("A1").CurrentRegion.Rows.Count - 1 ' determine number of articles in the list
   Dim ArtsArr() 'establish array to size of sheet data
   ReDim ArtsArr(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count + 1)
   ArtsArr = Range("A2").Resize(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count + 1)
   VirtualBook.Close SaveChanges:=False
   ' MsgBox "The file for Journal Articles was located and the data was collected and analysed"
Else
   MsgBox "File not found.  Obtain 'list-of-jnls.xls' and place in same folder as master file'"
   MsgBox "This process will end immediately.  Re-run with correct data file present"
   Exit Sub
End If

Select allOpen in new window

Thanks