remove everything but cell A1 across 500 excel workbooks

Are these CSV files or actual Excel workbooks?

So, after the code runs, there is only one non-empty cell in A1 in the first/only sheet?

csv

exactly - first sheet for all 500 books

Wouldn't it be easier to create one workbook and the copy it over the others, replacing them?

Never mind.  That would only work if all the workbooks were identical.

Please test this:

Sub Q_28618738()
    Dim strFilename As String
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim vData As Variant
    Const cPath As String = "C:\Users\AikiMark\Downloads\Q_28618738\"
    
    strFilename = Dir(cPath & "*.csv")
    Do Until Len(strFilename) = 0
        Set wkb = Workbooks.Open(cPath & strFilename)
        Set wks = wkb.Sheets(1)
        vData = wks.Range("A1").Value
        wks.UsedRange.ClearContents
        wks.Range("A1").Value = vData
        wkb.Close True
        strFilename = Dir
    Loop

End Sub

Select allOpen in new window

is it just Cell A1 you want to keep, or all cells in Col A?

And do you want to retain the 500 workbooks - ie NOT extract the 500 cell A1's to a single location instead?

if you're looking to build a master list of the contents of A1, and you can get a list of the filenames, you could use a formula like this:
="='C:\folder\["&A1&".xlsx]Sheet1'!$A$1"
where the filename is listed downwards in col A, and this formula is in Col B, for instance.  Obviously, change "folder" to be your actual location.

Then, copy all of Col B, and Paste Special.. Values over Col B.
then, do a Find/Replace for = with = (ie replace every equals sign with another equals sign), which will force Excel to recalcuate the constructed formulae.

If you need a list of the files extracting, we can probably figure that out too...

Thx this should

work although not sure if five hundred files overloads

Question - can you modify your solution to also save the value b1 into the b column so that a1,b1 from every book are only values saved?

awesome that works!

I need more help if you are free!...

https://www.experts-exchange.com/questions/28619254/need-to-DELETE-cell-b1-in-500-workbooks.html

don't forget to close this question