External Reference - Filename Change
 

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

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

Here is the code that I am using to grab the data from one workbook an
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 alway
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 m
having to rename the sheets from oef-1111.xls to oef.xls.

Thanks for your input

--
bg1846
-----------------------------------------------------------------------
bg18461's Profile: http://www.excelforum.com/member.php...nfo&userid=887
View this thread: http://www.excelforum.com/showthread.php?threadid=54991

External Reference - Filename Change
 

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

HTH
--
AP

"bg18461" a écrit
dans le message de news:
...

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

External Reference - Filename Change
 

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

--
bg1846
-----------------------------------------------------------------------
bg18461's Profile: http://www.excelforum.com/member.php...nfo&userid=887
View this thread: http://www.excelforum.com/showthread.php?threadid=54991

External Reference - Filename Change
 

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