VBA to access data from another spreadsheet

  • Thread starter Thread starter dovrox
  • Start date Start date
D

dovrox

If cell a1 contains the name of a file like : Costings and this is th
name of a completely separate spreadsheet that is in C:\data, what VB
code can I use to utilise the data in A1 to create a path to looku
data from the costings spreadsheet
 
Dov,

You don't necessarily need VBA. If you want to use VLOOKUP to look in
another workbook, it might look like the following. The external workbook
name (Costings.xls) is hard coded into the formula. The value being looked
up is in B1, and the table in Costings is in A2:B7:

=VLOOKUP(B1, [Costings.xls]Sheet1!$A$2:$B$7, 2)

The external (Costings) file need not be open for this to work.

If the name of the external file must be variable (in a cell), it gets a
little messy:

=VLOOKUP(B1, INDIRECT("["&A1&"]Sheet1!$A$2:$B$7"), 2)

The external file must be open, a requirement of INDIRECT for external
files. VBA could be used to open the file whenever this workbook is opened
(or the sheet is selected)
 
Now that is a cool tip! Do you happen to know of a way to have the same
functionality, but not have the file open as needed with indirect?
 
Thomas,

If the name of the file is to be supplied in a cell (rather than hard-coded
into a VLOOKUP function in a formula), I think INDIRECT is the only formula
way. Or a macro could build the formula from the cell.
 
Earl - thank you for taking time to answer my question.

I have another one now, though. I can't seem to get the posted formul
to work - it is exactly what I would like to do. I don't really min
if the supporting file needs to remain open. I posted a new threa
questioning how to do this.

Appreciate the help... Having this as an option will save quite a bi
of time redeveloping something that has already been put together.
Just as a brief overview, we use Cognos cubes to analyze our data.
request was made to have the reports available in a standard packet fo
those not interested in going into the cube reports. Excel was chose
primarily for its graphics. We have Cognos macros to extract necessar
data, but with the increasing demand we are running into memory issues
This started off as a small reporting package, but has grow
considerably. Having this as an option eliminates the creation o
multiple folders - one for each area we are reporting on. There are n
doubt better options as to setting up the whole process, but we'r
already up to our noses in this type of reporting...

Thanks
 
Thomas23 said:
Earl - thank you for taking time to answer my question.

I have another one now, though. I can't seem to get the posted formula
to work - it is exactly what I would like to do. I don't really mind
if the supporting file needs to remain open. I posted a new thread
questioning how to do this.
Click to expand...

You mentioned you will use the info to get data from another workbook
(I don't see the new thread), so how about this alternative approach
which uses ADO:

Sub Test()

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strSql1 As String

' Amend the following constants to suit
Const PATH As String = "" & _
"C:\data\"

Const FILENAME_XL As String = "" & _
"db.xls"

Const SHEET_XL As String = "" & _
"Sheet1"

' Do NOT amend the following constants
Const CONN_STRING_1 As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH><FILENAME>;" & _
"Extended Properties='Excel 8.0;HDR=NO'"

' Build connection string
strCon = CONN_STRING_1
strCon = Replace(strCon, _
"<PATH>", PATH)
strCon = Replace(strCon, _
"<FILENAME>", FILENAME_XL)

' Build sql statement
strSql1 = "" & _
"SELECT F1 As Filename" & _
" FROM [" & SHEET_XL & "$A1:A1]"

' Open connection
Set Con = CreateObject("ADODB.Connection")
With Con

.Open strCon

Set rs = .Execute(strSql1)
MsgBox rs!Filename

' Clean up
rs.Close
.Close

End With
End Sub

--
 
Back
Top