• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Retrieve data from a defined sheet and workbook.

L

looney

New Member
Hi,


I still hope to get some help with retrieving data from a particular sheet from a particular workbook.


To make it clear... The workbook name is located in on sheet "Name 1" and in Cell "B2".

The name of the needed sheet will be located on the same sheet in cell "B3".

I need to apply this to 28 different names/workbooks weekly. But I have no clue how to do it. I tried to work with the next macro coding. It does what I need, but I don't know how to apply the information located in cell B2 and B3. I twould even be great if I could loop this for the whole folder "CC" or something, but that is not really important right now.


I am getting lost..


This is the macro/VBA coding:

[pre]
Code: 
Option Explicit

'credit for this technique goes to John Walkenback
'http://j-walk.com/ss/excel/tips/tip82.htm

Sub GetDataDemo()

Dim FilePath$, Row&, Column&, Address$

'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Name 1.xls"
Const SheetName$ = "Week 21"
Const NumRows& = 30
Const NumColumns& = 17
FilePath = ActiveWorkbook.Path & "CC"
'***************************************

DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 9 To NumRows
For Column = 5 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)

Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
[/pre]

In the next part:

Const SheetName$ = "Week 21"

I would like to refer to a cell on the main sheet where I would fill in the data manually


I might could do the same for:

Const FileName$ = "Name 1.xls"

But then would like to refer to a list of names or something.


Please help me out. I am just a beginner in Macro's.
 
I didn't quite understand how you want to loop through things, but for the single file, you'll want to switch from using
Code: 
Constants to using variable. Here's how the one block at beginning should look:

[pre]'change constants & FilePath below to suit
'***************************************
'instead of using constants, we'll use variable
Dim FileName$, SheetName$
FileName$ = Worksheet("Name 1").Range("B2")
SheetName$ = Worksheet("Name 1").Range("B3")

Const NumRows& = 30
Const NumColumns& = 17
FilePath = ActiveWorkbook.Path & "CC"
'***************************************
[/pre]
If you have a list of workbook names and cells you want, or you just want to loop through every workbook in the CC folder and are always pulling from the same cell, we can work with that. Just need to know exactly what your goal is.
 
Hi ,


What you describe is fairly simple , and can be coded from scratch ( without having to modify Walkenbach's code ) if you can specify exactly what you want done :


1. What is the source sheet , and cell / range ?


2. What is the destination sheet , and cell / range ?


3. What kind of looping or repetition is needed ?


4. What are the variables i.e. what will change from one run of the macro to the next ?


If you can give details , anyone here can code the macro for you.


Narayan
 
Thanks Luke and Narayank!


I'll try to explain the situation.


I need help with copying a range of cells (E9:Q30) from several workbooks in a folder "CC" to a main file. I'll try to explain any further conditions.


* The main file contains a main sheet for a particular week and has several tabs/sheets that is similar to a specific sheet in the workbooks in folder "CC".

- The same sheets ( in the main file has following information that I would like to use to specify where to look for in the workbooks in folder "CC".

Cell B2 "contains the name of the workbook in folder "CC" that should correspondence with sheet in the main file.

Cell B3 has got a week number that should correspondence with the right sheet from the workbook in folder "CC".

Range (E9:Q30) should be the same in the main file.

* the workbookfiles in folder "A" contains a main sheet and 52 data sheets named Week 1, Week 2, ..... Week 52.


To make the structure more clear... maybe the following will help.


Folder: C:CCCCCC


File: Main.xls (2003version)


Sheets: main / Name 1 / Name 2 / name 3..../..../ etc.


------------------------------------------------------


Folder: C:CCCCCCCC


Files: Name 1.xls / Name 2.xls / Name 3.xls / .... .xls /... .xls etc.


Sheets (same for every file in this folder): Week 1 / Week 2 / Week 3 / .../.../ Week 52


At the moment every week the sheets are copy pasted from the workbooks to the main file.


I'd like to change this so the only input is needed Is a week number I fill in on the main sheet of the main file and just have to push a macro button.


I also tried to work with a formula before... =INDIRECT("'C:CCCCCCCC[&$B$2&.xls]"&"Week "&$B$3&"'!"&"E9") Which I used for all the cells from E9 till Q30. It works fine, but the disadvantages were that it would run very slow. Also every workbook needed to be opened and closed which I did not got the right code for yet.


So I want to try a new approach and make use of the week numbers and the names in Cell B2 an B3 and just copy paste this parts from the CC folder workbooks to the main file with a macro/VBA. This needs to be done weekly any help in this would be very much appreciated.


I'll give luke's suggestions a try. But something from scratch would even be better.


Thanks in advance!
 
Luke's suggestion is not working for me. I am getting a Compilation error. something about Sub or Function is not Defined. And then it highlights blue the part of Worksheet from the code:

[pre]<br />
FileName$ = Worksheet("Annemieke de Jong").Range("B2")<br />
[/pre]


after pressing OK. the part with Sub is highlighted in yellow.
 
Hi looney,

Sorry, about that, need to add
Code: 
.Value to the end so it doesn't think we're trying to make the variable the whole cell.

FileName$ = Worksheet("Annemieke de Jong").Range("B2").Value
 
Or, here's a new macro that loops through all the files in a subfolder and copies the data from them. From the scenario you described above, this should work. Assumption is that every file in the CC folder has a tab in the Main workbook.

[pre]
Code: 
Sub OpenFiles()
Dim myPath As String
Dim fName As String
Dim myRange As String
Dim myBook As Workbook
Dim shName As String
Dim myBookName As String

myPath = ThisWorkbook.Path & "/CC/"
'What range gets copied?
myRange = "E9:Q30"
'Generate week number from number in B3 of sheet Main
shName = "Name " & ThisWorkbook.Worksheets("Main").Range("B3")

'This will let us loop through all the XL files in a folder
fName = Dir(myPath & "*.xls*")

Application.ScreenUpdating = False

Do While fName <> ""
'Opens the workbook in read-only mode so we don't get an error
'if someone happens to have the workbook open
Workbooks.Open myPath & fName, , True
Set myBook = ActiveWorkbook
'Get name of workbook w/o the extension
myBookName = Left(myBook.Name, (InStrRev(myBook.Name, ".", -1, vbTextCompare) - 1))
'Copy desired range to correct sheet, starting in A1
myBook.Worksheets(shName).Range(myRange).Copy ThisWorkbook.Worksheets(myBookName).Range("A1")
myBook.Close

'Find the next file, if it exists
fName = Dir()
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
[/pre]
 
Thanks Luke!


I just do not get it to work... :-( Is there a way I can post you an example of the xls files. I have it zipped and should be around 421 kB I think you would understand the situation a bit better.
 
Sure, you can give it a shot. See here for suggestions about uploading info:

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Thanks!


Here is the link:

https://www.dropbox.com/s/1gvmfqt2299wj7u/CCC.zip


There are a lot of formulas that would slow down the file. So make sure Calculating is on Manual.
 
Hi Looney!


Just Little Modification.. in Luke's Code..


Code: 
myPath = ThisWorkbook.Path & "/CC/"

has been changed to

[b]myPath = ThisWorkbook.Path & "CC"

& 


shName = "Name " & ThisWorkbook.Worksheets("Main").Range("C1")

has been changed to

shName = ThisWorkbook.Worksheets("START").Range("C1")[/b]


Please update those visible User customizable errors.., and check and confirm


Regards,

Deb
 
Wow! Thanks! It seems to be working in the testing folder! I am going to test this out in production!


Thanks a lot. This really saves me a lot of trouble!!! This was bugging me for such a long time!!!
 
You must log in or register to reply here.
Back
Top