Macro code for openning files

aioliaexcel

New Member
Joined
Sep 24, 2014
Messages
3
Hi all,

I have to open three files everyday. The file's name is: xxx 09-24-2014. The date will be changed every business day. I have to go to the folder where all these files are saved: Folder's year>Folder's month>the files and open the files.
let say today is 9/24. I have to open three files, 1 business day, 2 bd and 3 bd prior to today everyday. the files name would be: xxx 09-23-2014, xxx 09-22-2014 and xxx 09-19-2014.
Is there a macro code for this?
Also, I want to copy and paste all the three files contents.

Thanks for your help!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
Hi, and welcome to the forum.

Place the code below in a standard module, i.e., Insert=>Module.
The code will require editing at your end for Folders, sheet names, ranges, etc.
I have heavily commented the code and highlighted (red) these possible edits.

Rich (BB code):
Option Explicit


Sub MergeFiles()
   Dim wbSource As Workbook   'workbook we copy from
   Dim wsSource As Worksheet  'worksheet we copy from
   Dim wsTarget As Worksheet  'worksheet we paste into
   
   Dim rowSource As Long      'number of rows to copy
   Dim colSource As Long      'number of column to copy
   Dim rowTarget As Long      'row on target sheet to paste into
   
   Dim fileName As String
   Dim fileDate As Date
   Dim fileCount As Long      'we only need to process three files
   Dim LoopCounter As Long    'escape counter to prevent infinite loop
   
   'define the target worksheet
   Set wsTarget = Sheets("Sheet1")
   
   'loop through the files
   fileDate = Now - 1
   Do Until fileCount = 3


      '***EDIT PATH AND FILNAME***
      fileName = "C:\temp\Merge Files\xxx " & Format(fileDate, "mm-dd-yyyy") & ".xlsx"
      
      'test the file exists
      If FileExists(fileName) Then
         
         'increment file count
         fileCount = fileCount + 1
         
         'get the next available row on the target sheet, based on column A
         '***EDIT IF NECESSARY***
         rowTarget = wsTarget.Cells(Rows.Count, "A").End(xlUp).Row + 1
         
         'open the workbook
         Set wbSource = Workbooks.Open(fileName)
         
         'get the row and column count based on A1 of the first worksheet in the file
         '***EDIT IF NECESSARY***
         Set wsSource = wbSource.Worksheets(1)
         rowSource = wsSource.Cells(Rows.Count, "A").End(xlUp).Row
         colSource = wsSource.Cells(1, Columns.Count).End(xlToLeft).Column
         
         'copy and paste
         wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(rowSource, colSource)).Copy _
            Destination:=wsTarget.Range("A" & rowTarget)
         Application.CutCopyMode = False
         
         'close the workbook
         wbSource.Close SaveChanges:=False
      End If
      
      'get next file date
      fileDate = fileDate - 1
         
      'This was necessary at my end as I ran out of files to process
      'Escape the loop after, say 5, iterations; 3 for files + 2 for weekend
      LoopCounter = LoopCounter + 1
      If LoopCounter = 5 Then Exit Do
   Loop
   
   'tidy up
   Set wsTarget = Nothing
   Set wsSource = Nothing
   Set wbSource = Nothing
End Sub




Function FileExists(ByVal FullFilePath As String) As Boolean
   FileExists = Len(Dir(FullFilePath)) > 0
End Function

Hope this helps,
Bertie
 

aioliaexcel

New Member
Joined
Sep 24, 2014
Messages
3
Question on the first three codes. the workbooks' name we copy from will change everyday so do the worksheets. The date will keep changing every business day
 

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
We have a variable called FileDate, We use this to build up the file name, then process that file.
At each iteration of the loop we subtract one from the DileDate, rebuild the file name and process that file
And so on...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,879
Messages
5,834,215
Members
430,263
Latest member
abz54

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
Top