Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Linking data from one Workbook to another

  Asked By: Dylan    Date: Oct 14    Category: MS Office    Views: 1427
  

I am trying to link data from one excel workbook to another using VB code.
I have an idea roughly were to start, however I could use some help
refining it.

So here is what I have.
We are storing our excel work books on share point these work books
are to be deployed to each of our plants SharePoint sites. As a
result the file locations are not static.

I have a routing to get (setup) the workbook (that I want to link to)
location. Were I seem to be missing is getting to the data from one
work book into the other. I can't seem to get the VB code to go to
the file location and pick the info from the other workbook.

I am assuming at this point I need to use some format of the
workbooks( ) code.
Any help with this would be great.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Jawahir Burki     Answered On: Oct 14

I think we need to clarify some terminology here.
You say "link data". Do you mean you wish to ACCESS the data  in the shared
file,
or perhaps "copy" the data to your file?
Or are you trying to use lookup formulas, but cannot because the file  location
changes?

Then there's:
"I have a routing(??) to get the workbook  location"

Now, you DO realize that Excel expects the two workbooks to be OPEN, right?
Because if you're trying to "get the VB code  to go to the file location", it's
not going to
happen.
What you do is use whatever means you have at your disposal to locate the full
name
of the file with the data. Use Workbook.open FULLNAMEofFILE to get the file
open.
but the Workbooks() object uses just the filename.
so, you can use something like:
Workbooks.open filename:="C:\temp\BIGTIMEREPORT.xls"
then use
workbooks("LocalReport").worksheets("Sheet1").cells(1,1) =
workbooks.("BIGTIMEREPORT.xls").worksheets("Sheet1").cells(1,1)

 
Didn't find what you were looking for? Find more on Linking data from one Workbook to another Or get search suggestion and latest updates.




Tagged: