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.

'Change p, s1, and s2 values, a, and 1st input to aFFs() to suit.
Sub Main()
  'https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/type-characters
  Dim p$, s$, s1$, s2$, n, nn, i&, j%, fso As Object, a
  
  p = ThisWorkbook.path & "\Test2"
  s1 = "Enter Info"
  s2 = "Item Work Sheet"
  'First 4 cells from sheet s1, last 4 cells from sheet s2.
  a = Split("C18,C3,C13,C19,N5,F2,N4,P4", ",")
  
  n = aFFs(p & "\*.xlsx", "/o:n")  'Order by filename.
  If Not IsArray(n) Then Exit Sub
  ReDim nn(0 To UBound(n), 0 To UBound(a))
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  
  For i = 0 To UBound(n)
    For j = 0 To UBound(a)
      'Set sheet name
      s = s1: If j >= 4 Then s = s2
      nn(i, j) = GetValue(p, fso.GetBasename(n(i)), s, a(j))
    Next j
  Next i

  Range("B1").Resize(UBound(nn, 1) + 1, UBound(nn, 2) + 1).Value = nn
  Set fso = Nothing
End Sub

'Retrieves a value from a closed workbook
Private Function GetValue(path, file, sheet, ref)
  Dim arg As String
  'path = "d:\files"
  'file = "budget.xls"
  'sheet = "Sheet1"
  'ref = "A1:R30"
     

  'Make sure the file exists
  If Right(path, 1) <> "\" Then path = path & "\"
  If Dir(path & file) = "" Then
    GetValue = CVErr(xlErrNA)
  End If
     
  'Create the argument
  arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
  Range(ref).Range("A1").Address(, , xlR1C1)
   
  'Execute an XLM macro
  GetValue = ExecuteExcel4Macro(arg)
End Function

'Set extraSwitches, e.g. "/ad", to search folders only.
'MyDir should end in a "\" character unless searching by wildcards, e.g. "x:\test\t*
'Command line switches for the shell's Dir, http://ss64.com/nt/dir.html
Function aFFs(myDir As String, Optional extraSwitches = "", _
  Optional tfSubFolders As Boolean = False) As Variant
  
  Dim s As String, a() As String, v As Variant
  Dim b() As Variant, i As Long
  
  If tfSubFolders Then
    s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b /s " & extraSwitches).StdOut.ReadAll
    Else
    s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b " & extraSwitches).StdOut.ReadAll
  End If
  
  a() = Split(s, vbCrLf)
  If UBound(a) = -1 Then
    Debug.Print myDir & " not found.", vbCritical, "Macro Ending"
    Exit Function
  End If
  ReDim Preserve a(0 To UBound(a) - 1) As String 'Trim trailing vblfcr
  
  For i = 0 To UBound(a)
    If Not tfSubFolders Then
      s = Left$(myDir, InStrRev(myDir, "\"))
      'add the folder name
      a(i) = s & a(i)
    End If
  Next i
  aFFs = sA1dtovA1d(a)
End Function

Function sA1dtovA1d(strArray() As String) As Variant
  Dim varArray() As Variant, i As Long
  ReDim varArray(LBound(strArray) To UBound(strArray))
  For i = LBound(strArray) To UBound(strArray)
    varArray(i) = CVar(strArray(i))
  Next i
  sA1dtovA1d = varArray()
End Function

 

[Bond00]

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

what do i have to change aFFs() to?

can you comment things out even more just so i can better follow what is happening with the code so i can learn from it? I am pretty new to vba with excel.
Also do i have to un-comment this section and edit it or is this just to show what its doing with the GetValue or arg?
'path = "d:\files"
'file = "budget.xls"
'sheet = "Sheet1"
'ref = "A1:R30"

The more detail the better, also where is the part where i can control what row it starts the output on? like row 2 etc.

 

[Kenneth Hobson]

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

The first comment explains what you "may" need to change.

I am looking for xlsx files in aFFs(). Change that to xls if that is what you wanted. After that, the value of p (path/folder to files to send to GetValue) is probably all that needs changing.

You are the one that posted the GetValue() routine. I used a slightly different version that returns #NA if no value is found. It also has the example comments showing inputs for GetValue().

If you need detailed help what does what, for command words, put cursor in or next to the word and press F1.

1. aFFS() is a batch routine that returns a zero base array. For the power of what all it can do with command line switches, see the commented link.
2. a = Split line simply makes an array of your cell addresses for GetValue().
3. The two For() loops simply fill the dimensioned array nn.
4. FSO is a whole topic unto itself. I can show you a CHM file link if you want to learn the power of it.
a. For now, fso.BaseName returns the filename.ext part of a full drive:/path/filename.ext string.
5. The next to last line with resize set the location to quickly fill values from the array nn.

So, not all that complicated is it?

aFFS() gives you lots of power with very few inputs needed. Think of it as a car, you don't need to know how it was built. You just need to know the few inputs to get it to do what you want which is to return an array of full drive:/path/filename.ext.

As I explained, GetValue() is very unforgiving. IF you know your data well, it can be used with confidence. If you don't, then not so much.

 

[Bond00]

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

I tested the code and when I make it run it brings up a window to the directory for me to open a file like a 100 times, so i just hit Esc a lot and it fills in some of the data but a lot of it is #REF !. I'm assuming its looping through and does that for every cell its trying to get a value for. How do i make it not prompt to open a window every time it goes to open a file? Also i want the data to start being entered on row 2 but currently it starts on row 1, where is the variable to change for that?
Thanks for the help so far.

 

[Kenneth Hobson]

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

GetValue() method opens the file in the background. If the workbook is password protected, since you have 8 calls to open/get the value/close it, it will prompt for password 8 times.

The reference error is well, a reference error. The sheet name, does not exist is one reason. The other is that when prompted, you did not type in the password and pressed ESC/Cancel for another reason.

For the start at row 2, change the top left cell:

'Range("B1").Resize(UBound(nn, 1) + 1, UBound(nn, 2) + 1).Value = nn
Range("B1").Resize(UBound(nn, 1) + 1, UBound(nn, 2) + 1).Value = nn

 

[Bond00]

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

Thanks for the code change there.

there is no password protecting the docs, can you think of any other reasons? the odd thing is some populate but others dont, like the first 3 things populate in line 1 and then like 4-5 lines down the full row populates values for 3 rows and then all #REF!'s from there on down. very odd.

 

[Kenneth Hobson]

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

From post $4:

As I explained, GetValue() is very unforgiving. IF you know your data well, it can be used with confidence. If you don't, then not so much.

Works perfectly for me. Try it for 1 or 2 files that you know have no workbook protect and those 2 sheet names exists.

A Debug.Print can be used to ouput during a run to the VBE Immediate Window. Then you will know which file has the issue(s).

You can post a snippet of the dialog or post in a workbook and post the link to a free shared site like dropbox.com. Without seeing your code, I suspect that it is the password dialog though.

 

[Bond00]

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

Thanks for the help Kenneth, i didn't get time to test it out today but I''ll let you know if i run into issues again.

 

[Bond00]

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

I ran into another issue, when i put that file and folder of files and try to use it on the network share it wont populate. I fixed the directory locations etc so thats right but it wont work. I also allowed macros to run. What else could it be? the macro i have that populates the file names and makes them hyperlinks works but not the part that pull the info from the docs. But it works on my local machine if the files are also on my local machine.