Hi Laerte
I will try and help you, but as David says, it is difficult when there is a
lot of confusion about the particular objects you are using and the
difference between an object and its name.
An object such as a workbook, or worksheet has a name. The name is a
string. The name is not the object.
(I have pasted your original query below so that as we work through your
problems we can refer back to the history. Please do not delete the history
when you reply!)
Let's give your objects some imaginary names so we know what we are dealing
with
There is a workbook called "Main.xlsm" with a worksheet in it called
"Index".
There are other workbooks called "Source1.xlsx", "Source2.xlsx",
"Source3.xlsx", etc.
These each contain a worksheet called "Data"
Index in Main.xlsm has a range (B5 to B27) which has in it the names of the
workbooks containing the data you want
Index also has a range (C5 to C27) containing the sheet names within the
relevant workbooks.
You want to insert the information from Cell X111 of each relevant worksheet
in column D of Index against the relevant row.
Let's look at the VBAto fill in D5 with the data from Source1.xlsx
The full syntax is
Workbooks("Main.xlsm").Worksheets("Index").range("D5").value =
Workbooks("Source1.xlsx").Worksheets("Data").range("X111").value.
Since we are working a lot with Workbooks("Main.xlsm").Worksheets("Index")
we can simplify this with
Dim ws as Worksheet
Set ws = Workbooks("Main.xlsm").Worksheets("Index")
Note that we use set because a workbook is an object
Then the line above becomes
ws.range("D5").value =
Workbooks("Source1.xlsx").Worksheets("Data").range("X111").value.
It can also be simplified by leaving out the .value as this is implied when
you refer to a range
ws.range("D5") = Workbooks("Source1.xlsx").Worksheets("Data").range("X111")
Now we have a way of referring to any cell in any worksheet in any workbook.
No use of set here because we are assigning to a property (value) of the
range object.
So to refer to the cell referenced in worksheet Index range B5 and D5 we can
substitute these in our formula
ws.range("D5").value =
Workbooks(ws.range("B5")).Worksheets(ws.range("C5")).range("X111").value
You also ask about changing a name of a worksheet
Assume we want to change the name of the worksheet Data in Source1.xlsx to
"OldData"
We would use
Workbooks("Source1").Worksheets("data")="OldData"
Note that we do not use Set here again because the name of an object is a
property, not the object itself.
This may not quite match your scenario, but it may help you learn how to do
what you want.
I expect you will have more questions. Please come back to the group with
them. If you do it is most helpful if you give the names of any relevant
objects as I did above.
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of laertetrad
Sent: Sunday, 21 October 2012 1:50 AM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Using cell values from one sheet as workbook and
spreadsheet names
I'd appreciate if anyone can actually help me out. Saying others can't do
what you can, doesn't help at all.
Laerte
Hi
I have a list of workbooks and spreadsheet names in one spreadsheet to which
I want to write values as appropriate.
How can I use the cell value to define the workbook/spreadsheet which to
open and write values to?
I have tried something like:
Dim Wkbk1 as Workbook (or worksheet)]
Dim Wkbk2 as Workbook (or worksheet)]
Dim Doc
Dim Sheetname1
Wkbk1 = Sheets("OS TKT (TL)").Range("X4").Value
Doc = Sheets("OS TKT (TL)").Range("X12").Value
Set Sheetname1 = ("M (10)") >>
>>> I want to be able to assign and change sheet name "M (10)" here and
use names from a list in another workbook/sheet.
Sheetname1.Cells(iRow, 4).Value = Data (it's OK!)
CurJobNo = Sheets Sheetname1 .Range("D41").Value (Nothing Ive tried
concatenating values has worked)
Summing up:
Workbook1/Sheet N : contains list of workbooks and values to be written
Workbook (?)/Sheet (?): workbook/sheets to be writen to, names (?) from
lists in Workbook1
Many thanks
Laerte
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar