Data Chart pulling single cells from many Excel docs in a Given Folder.

[Bond00]

This is the working code i started with below:

Private Function GetValue(path, file, sheet, ref)
    Dim arg As String
    If Right(path, 1) <> "" Then path = path & ""
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
    GetValue = ExecuteExcel4Macro(arg)
End Function

Sub LoopThruBooks1()
    Dim p, f, s, a, r
    p = "C:\excelfolder"
    f = Dir(p & "*.xls")
    s = "Enter Info"
    a = "C18"
    r = 1
    Do While f <> ""
        r = r + 1
        Range("B" & r) = GetValue(p, f, s, a)
        f = Dir()
    Loop

Sub LoopThruBooks2()
    Dim p, f, s, a, r
    p = "C:\excelfolder"
    f = Dir(p & "*.xls")
    s = "Enter Info"
    a = "C3"
    r = 1
    Do While f <> ""
        r = r + 1
        Range("C" & r) = GetValue(p, f, s, a)
        f = Dir()
    Loop
End Sub

Then i just did Call LoopThruBooks1, Call LoopThruBooks2, Call LoopThruBooks3 etc.. 1-8 Loops to make the 8 columns of data i needed from 8 individual cells.
That works fine, Its 99% there and does what i need almost.. the only issue i have is when it pulls the data into my new doc and makes the column lists its not in alphabetical/numerical order like the doc names listed in the folder directory.

I'm pulling 8 individual cells from 2 different sheets within a workbook from many excel docs in a folder. So 4 cells from 1 sheet and 4 cells from another sheet within the workbook and making columns from those docs in the folder.

cell data from sheet2 named "Enter Info"
get C18 and put it in the B column
get C3 and put it in the C column
get C13 and put it in the D column
get C19 and put it in the E column

cell data from sheet1 named "Item Work Sheet"
get N5 and put it in the F column
get F2 and put it in the G column
get N4 and put it in the H column
get P4 and put it in the I column

I have something in column A already so i start entering the data in B, i also have it setup as a Table so i start the data on row 2 because row 1 has the Chart title names.

I started this info on this thread https://www.mrexcel.com/forum/excel...multiple-files-same-folder-3.html#post4928812 but it started becoming its own thing so i am posting it here as a new thread.
Please help!

 

[Kenneth Hobson]

Re: Help making a Data Chart pulling single cells from many Excel docs in a Given Folder.

Is the network path a drive, virtual drive, or UNC path?

You can test if the problem is the aFFs() if after that line you do:

Debug.print ubound(a)

After a run, in Visual Basic Editor (VBE) Immediate Window, you will see a result or it will error, no array was returned so it exited gracefully before. Enable that window in VBE menu View if no error occurs. If no error, GetValue() is the likely problem.

 

[Bond00]

Re: Help making a Data Chart pulling single cells from many Excel docs in a Given Folder.

where do i enable that window to see the menu?

also the odd thing is even if i run that file on my local machine with the correct network path it still wont work, so its like the fact that the files are in the network shared folder that it wont work. I tried it at home on another pc on the network on that works though so im wondering if there is something on the work network blocking something on the domain. But then again the other macro runs fine, but thats only reading the files names and making them hyperlinks not actually getting data from within the files.. hmm

 

[Kenneth Hobson]

Re: Help making a Data Chart pulling single cells from many Excel docs in a Given Folder.

If menu method is unclear, use the hotkey method:
1. Open VBE Alt+F11
2. Open VBE Window and place cursor in it: Ctrl+G

Search for this to learn about an important VBE featue, Immediate Window. "excel tutorial Immediate Window"

I may show you a hybrid method that uses VBA's Dir() for the parent folder and FSO for the subfolders. Or, I may modify it and do it all in FSO. FSO would be the most network friendly method most likely.

 

[Bond00]

Re: Help making a Data Chart pulling single cells from many Excel docs in a Given Folder.

k yeah just had to press ctrl G in the vba editor window. I figured out what was the issue, it was not looping because it didnt pick up the name for some reason. On my local computer it was fine using *.xls" but on the network it wanted the exact file extension for some reason.. So i made it *.xls*" and it works now with the .xlsx files now.
thanks again!