Edit 500 workbooks at the same time multiple workbooks

bombadillio

New Member
Joined
Jan 20, 2011
Messages
2
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!
 
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.
Code:
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top