Using FSO to insert folder name into cell based on

Question

I'm having trouble putting the syntax together for this and I just started working with FSO in VBA, so please bear with me.

fsofol.name = test1 (this is correct)

I'm trying to determine if each cell has any data in column "A" and if so, put the name of the folder in the offset cell I have listed. I'm hoping I'm close, but if anyone can help with a suggestion I would be grateful. Thanks!

I put a note in the problem line below

    Dim fsoFol As Object  

    If fileName Like "V*.xls" Then
         wbkCS.Worksheets("Cut Sheet").Range("S4:S2000").Copy
              With wbkVer.Worksheets("Cutsheets")
              Set firstRange = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0) 
    firstRange.PasteSpecial xlPasteValues
    If firstRange.Value Like "*" Then
         fsoFol.Name.Copy **'error is here and states object required**
         firstRange.Offset(0, 5).PasteSpecial xlPasteValues
    End If
              End With

Answer 1

Assuming that FSO is the Scripting.FileSystemObject and that you want to work through all of the "V*.xls" files in a folder called "test1", you're missing a few steps:

Creating the file system object

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Assigning the folder

Dim fsoFol As Object
Set fsoFol = fso.GetFolder("C:\Temp\test1")

Working through the files

Dim fsoFile As Object
For Each fsoFile in fsoFol.Files
  If fsoFile.Name Like "V*.xls" Then
    ' code for working with the Excel files goes here
  End If
Next fsoFile

Open a specific Excel file and assign it to a variable

' Assuming you have Dim wbkCS As Workbook at the start of the module

Set wbkCS = Workbooks.Open(fsoFile.Name)

' code to process the actual file goes here

wbkCS.Close False

I'm unclear on what you are trying to do with the cells copied into wbkVer. You're copying nearly 2000 cells but only looking at the value of the first cell copied. Also the test Like "*" will return True for both empty cells and cells with values so it probably isn't what you need. If you can clarify that requirement then we can move things on a bit

Answer 2

Change

 fsoFol.Name.Copy **'error is here and states object required**
 firstRange.Offset(0, 5).PasteSpecial xlPasteValues

to

 firstRange.Offset(0, 5).Value = fsoFol.Name