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!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

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