Solved

Excel 2003 - Access data from closed workbook

Posted on 2011-09-29
24
289 Views
Last Modified: 2012-05-12
Hi,

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 File2.xls!Sheet1 get the data from Sheets1(A1).Current Region, store in an arrMyData, and provide message 'Data Collected'
3: close all links with File2.xls

Can someone please help with this?
dave
0
Comment
Question by:TigerMan
  • 15
  • 8
24 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
>>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
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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?
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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

Open in new window


for example.
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I just did build that code...

Your code is using formulas:
DestRng.Resize(.Rows.Count, .Columns.Count).Formula = myFormula 

Open in new window


Not sure what you mean about controlling user machines.
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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.
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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

Open in new window

0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
Comment Utility
If those are the only issues you have with that code, it's a pretty simple fix:
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

Open in new window

0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
nearly ... what if strFile does not find the file it is looking for?
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Author Comment

by:TigerMan
Comment Utility
agh ... that code opens the workbook ... i don't wish it to be opened at all
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
"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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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

Open in new window

0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
"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.
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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.
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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.
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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
0
 
LVL 5

Author Comment

by:TigerMan
Comment Utility
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

Open in new window

0
 
LVL 5

Author Closing Comment

by:TigerMan
Comment Utility
Thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now