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

cmurray91

New Member
Joined
Jul 11, 2013
Messages
17
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?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Is the code exactly the same in all the files?
 
Upvote 0
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?
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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