Edit 500 workbooks at the same time multiple workbooks

[bombadillio]

Greetings,

I have 500 .csv files that I need to edit as they are a backup of a database which at the end is being moved to a new device. In this process I need to:

- Take column A and format them as numbers(currently set as text) with 0 decimal places
- Take Column E and any text in there needs to be cut and copied to the end of the text in column C

I was able to use notepadd++ for the other tasks which was a simple find and replace for all 500 workbooks but I do not think I can use it for the rest. In notepad++ I did a find for a 10 digit character which is on every other line and replaced it with the new number. I then did a search for any other text (,,,,) and had it deleted. The only thing it would not do was delete all the extra columns and rows which had no text.

Can you help?
Thanks!

 

[bombadillio]

anyone have any ideas?

 

[Cindy Ellis]

Hello bombadillo. Welcome to the board!
The following routine is a generic process to do the same thing to all files in a given folder.

Sub ProcessFilesInFolder()
    Dim folderName As String, filePathName As String, FileName As String
    Dim WBName As String, DSName As String
    
    folderName = "C:\temp\"  'Change this to your directory
    FileName = Dir(folderName & "*.csv", vbNormal)
    
    While FileName <> ""
    
        filePathName = folderName & FileName
        Workbooks.Open FileName:=filePathName
            
        WBName = ActiveWorkbook.Name  'use WBName to refer to the name of the current workbook
        DSName = ActiveSheet.Name 'use DSName to refer to the name of the worksheet
                                  'that was created when you opened the text file
                                  
        '***********your code here*************
        
        'do whatever you need to do to the file here.
        MsgBox (WBName)   'this is just for testing...remove it when you know you're getting the files you want
        
        
        '***********end of your code here*************
        
        
        Workbooks(WBName).Close SaveChanges:=True
        
        FileName = Dir()   'this gets the name of the next file
        
    Wend
End Sub

This should get you started...it doesn't include the details that you need, though. On my way to work now, and won't be able to help more for several hours, but maybe someone else can step in and help with the 2 specific data manipulation pieces you need.
Cindy