VBA code to open multiple files in same folder and perform same macros

[cmurray91]

I have thousands of .fac files that i need to go through and format so that they will be able to be viewed by Diffmerge. I have macros already created in excel which will do all the formatting for me.

Now i want to automate the process of opening each .fac file individually and selecting the macros.

Is there a way to write a VBA code to do this for me automatically?

 

[Dazzawm]

Is the code exactly the same in all the files?

 

[cmurray91]

Yeah basically when the .fac files open in excel everything is in one column. so i created a macro which uses the "Text to Columns" button to have everything into separate tabs.

Then, because these tables are coming from Prophet, there is a bit of code at the bottom of each table which needs to be removed. I've coded that into the macro too.

The macro works perfectly.

Is that what you mean?

 

[Dazzawm]

This is a code I use that opens each file and clears all the contents in column AA in each file. You may be able to adapt it to your needs.

Sub OpenAndClearContentsInColumnAA()
Dim strF As String, strP As String
Dim wb As Workbook
Dim ws As Worksheet


'Edit this declaration to your folder name
strP = "C:\Documents and Settings\MANAGER\Desktop\Dazzas Files" 'change for the path of your folder


strF = Dir(strP & "\*.xlsm") 'Change as required


Do While strF <> vbNullString


    Set wb = Workbooks.Open(strP & "\" & strF)
    Set ws = wb.Sheets(1) 'uses first sheet or if all the same names then ws.Sheets("yoursheet")
    ws.Range("AA2:AA" & ws.Range("AA" & ws.Rows.Count).End(xlUp).Row).ClearContents
    wb.Close True
    
    strF = Dir()
Loop


End Sub

 

[cmurray91]

Thanks so much for your reply. Really appreciate it. I'll give this a go tomorrow when i'm back in work.

Thanks again,

Chris.

 

[minakshiksd]

Hi

I want exactly same code you had asked for. May I ask if you have received it, if yes could you please help me out with the same?

Thanks in anticipation.

 

[JozefMJ]

This is a code I use that opens each file and clears all the contents in column AA in each file. You may be able to adapt it to your needs.

Sub OpenAndClearContentsInColumnAA()
Dim strF As String, strP As String
Dim wb As Workbook
Dim ws As Worksheet


'Edit this declaration to your folder name
strP = "C:\Documents and Settings\MANAGER\Desktop\Dazzas Files" 'change for the path of your folder


strF = Dir(strP & "\*.xlsm") 'Change as required


Do While strF <> vbNullString


    Set wb = Workbooks.Open(strP & "\" & strF)
    Set ws = wb.Sheets(1) 'uses first sheet or if all the same names then ws.Sheets("yoursheet")
    ws.Range("AA2:AA" & ws.Range("AA" & ws.Rows.Count).End(xlUp).Row).ClearContents
    wb.Close True
    
    strF = Dir()
Loop


End Sub

Thank you Dazzawn.
Please, in case of sum content of the column AA - how you would edit the code to save results in txt file? Is it better to set the active cell before "ws.Range("AA2:AA...Row).Sum" or add StreamWriter (https://www.homeandlearn.co.uk/NET/nets8p4.html) before "wb.Close True"?

Thanks.

 

[avnesingh]

I have thousands of .fac files that i need to go through and format so that they will be able to be viewed by Diffmerge. I have macros already created in excel which will do all the formatting for me.

Now i want to automate the process of opening each .fac file individually and selecting the macros.

Is there a way to write a VBA code to do this for me automatically?

Is the code exactly the same in all the files?

Hi cmurray91

I am trying to open various .FAC files from prophet using vba and I want to make changes to them. basically values in each .Fac has to change differently.Is there a code that I can use firstly to open these files