Method not available

Hello everyone!

Im trying to really develop some skills in VB so I may have some follow up questions as to how a bit of code works.

Right now I'm working on a project to open all workbooks in a folder (the folder will change on a daily basis and the amount of workbooks in each folder will vary too). Eventually I'll have it make certain changes in each workbook.

But for right now I have

Sub combine()
 

 
 Dim directory As String
 Dim foldername As String
 Dim fldrobj As FileSystemObject
 Dim fileobj As FileSystemObject
 
 

 

 foldername = Format(DateTime.Now, "yyyymmdd")
directory = "C:\combiner\"
Set fldrobj = FileSystemObject.GetFolder(directory & foldername)

For Each fileobj In fldrobj.Files


Application.Workbooks.Open (fileobj)


 Next
    
 
 
End Sub

One issue I have is that the ".Files" method in the For each Loop is not available. I see other similar Subs out there that utilize this method but for some reason I dont have it. Is there some other type of fileobject that will allow me to run this for each loop?

I also have the microsoft striplet library imported

Hi vincenzo345,

You were missing a call that created the 'File System Object'. I modified your code slightly to use 'Late Binding' (not important what 'Late Binding is or does) so that you do not need the library reference:


Try:

Sub combine()
 
  Dim directory As String
  Dim foldername As String
  Dim oFs As Object
  Dim fldrobj As Object
  Dim fileobj As Object
 
  'Create the File System Object
  Set oFs = CreateObject("Scripting.FileSystemObject")

 

  foldername = Format(DateTime.Now, "yyyymmdd")
  directory = "C:\combiner\"
  Set fldrobj = oFs.GetFolder(ThisWorkbook.Path)

  For Each fileobj In fldrobj.Files

    Debug.Print fileobj.Name    'Sends output to the Immediate Window (CTRL G) in the debugger
    Application.Workbooks.Open (fileobj)

  Next
    
 'Clear object pointers
 Set oFs = Nothing
 Set fldrobj = Nothing
 Set fileobj = Nothing
 
End Sub

For more information about 'Early Binding' and 'Late Binding' (only if you want to make your head spin) see: http://peltiertech.com/Excel/EarlyLateBinding.html
I prefer 'Late Binding' which disagrees with the Author. I like 'Late Binding' because it avoids the need for Library references, even though it may require a little more work at times when referring to Constants that are automatically provided by the Library reference.

The following items may help you in the future:
Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

Lewis

LTMetzger,

Thanks for the information on early and late binding. As a habit I always use early binding. . . in fact I didn't know lat binding existed.

Norie,

Thanks for the solution. I realized what I was having issues with. The first thing was that I defined Fldrobj and Fileobj as the wrong variable type. The other issue I realized was that I was trying to use the Filesystemobject class instead of an instance of the class.


Thanks again!