External Reference - Filename Change

[bg18461]

I am trying to create VB code that takes data from one workbook and adds info to a master workbook (database).

The filenames will all have the same prefix followed by a series of numbers.

Here is the code that I am using to grab the data from one workbook and insert it into the database.

Range("L2").Value = Range("'[oef.xls]Sheet1'!C12").Value

This code assumes that the name of the file I am inserting will always be oef.xls, however I will be taking data from files called
oef-1111.xls
oef-2222.xls
oef-3333.xls, etc...

I would like the code to be able to pull from these files without me having to rename the sheets from oef-1111.xls to oef.xls.

Thanks for your input.

[Ardus Petus]

var = "1111"
Range("L2").Value = Range("'["oef-" & var & ".xls]Sheet1'!C12").Value

HTH
--
AP

"bg18461" <bg18461.2937rn_1149776703.2667@excelforum-nospam.com> a écrit
dans le message de news:
bg18461.2937rn_1149776703.2667@excelforum-nospam.com...
>
> I am trying to create VB code that takes data from one workbook and adds
> info to a master workbook (database).
>
> The filenames will all have the same prefix followed by a series of
> numbers.
>
> Here is the code that I am using to grab the data from one workbook and
> insert it into the database.
>
> Range("L2").Value = Range("'[oef.xls]Sheet1'!C12").Value
>
> This code assumes that the name of the file I am inserting will always
> be oef.xls, however I will be taking data from files called
> oef-1111.xls
> oef-2222.xls
> oef-3333.xls, etc...
>
> I would like the code to be able to pull from these files without me
> having to rename the sheets from oef-1111.xls to oef.xls.
>
> Thanks for your input.
>
>
> --
> bg18461
> ------------------------------------------------------------------------
> bg18461's Profile:
> http://www.excelforum.com/member.php...fo&userid=8877
> View this thread: http://www.excelforum.com/showthread...hreadid=549910
>

[bg18461]

I am getting a compiler red error, i think there is a missing " somewhere in that code you sent, thanks.

[Dave Peterson]

dim myFileName as string
myFilename = "oef-1111.xls"

Range("l2").value _
= workbooks(myfilename).worksheets("sheet1").range("C12").value

But that workbook does have to be open.


=====
If you're trying to retrieve a value from a closed workbook...

John Walkenbach has a routine that can get values from a closed workbook:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

And you could also just build a formula in an empty cell, retrieve the value and
then clean up that helper cell.




bg18461 wrote:
>
> I am trying to create VB code that takes data from one workbook and adds
> info to a master workbook (database).
>
> The filenames will all have the same prefix followed by a series of
> numbers.
>
> Here is the code that I am using to grab the data from one workbook and
> insert it into the database.
>
> Range("L2").Value = Range("'[oef.xls]Sheet1'!C12").Value
>
> This code assumes that the name of the file I am inserting will always
> be oef.xls, however I will be taking data from files called
> oef-1111.xls
> oef-2222.xls
> oef-3333.xls, etc...
>
> I would like the code to be able to pull from these files without me
> having to rename the sheets from oef-1111.xls to oef.xls.
>
> Thanks for your input.
>
> --
> bg18461
> ------------------------------------------------------------------------
> bg18461's Profile: http://www.excelforum.com/member.php...fo&userid=8877
> View this thread: http://www.excelforum.com/showthread...hreadid=549910

--

Dave Peterson