link multiple files

[Tumbad]

Hi Friends,

I need to develop macro for one of my day to day activity which involves pasting data from 14 different excel files in single excel file in 14 different sheets. Format of source and destination files name is same except date change.

Destination file is Xxx.ddmmyy.xlsx

Source files format is as below.
XXXXX_XXXXXXX_ddmmyyyy.xls

data in first sheet always of all 14 files. and worksheet name is same across all 14 files.

Always clear data in destination sheets and paste updated data. There is one to one co-relation between which file to go in which sheet however worksheet name in destination file can be changed if required.

Warm regards,
Tumbad

 

[Yongle]

q1 14 files into 14 sheets: which file into which sheet?
q2 Is VBA contained in destination file ?

 

[Tumbad]

Hi Yongle,

Q1:

14 excel files with each file having single worksheet into it. These 14 can go in any sheet of destination file, but they have to go in same sheet every time its run.

For example:

File 1:
Source File name: SERIAL_DESIGNS_CODES_20200908.xls

Destination file sheet: sheet 2 (or we can name it anything)

File 2:
Source File name: IMERIAL_DESIGNS_CODES_20200908.xls

Destination file sheet: sheet 3 (or we can name it anything)

.
.
.
.
.
.
File 14:
Source File name: MATERAIL_DESIGNS_CODES_20200908.xls

Destination file sheet: sheet 15 (or we can name it anything)


It has to follow same order/worksheets of destination file every time macro is run.

Sheet 1 does some computation, hence it should start with sheet 2.


Q2:
Destination file does not have any macro, its simple computation.

Date format in source file is yyyymmdd

Warm regards,
Tumbad

 

[Yongle]

Does EVERY source workbook name end with _DESIGNS_CODES_ddmmyyyy.xls ?
(looking for pattern if there is one)

 

[Tumbad]

No, all files end with yyyymmdd.xls format. Earlier part is different for all 14 files

 

[Yongle]

Sorry you confused me
In post#1 you said
Source files format is as below.
XXXXX_XXXXXXX_ddmmyyyy.xls

In post#3 you said
Source File name: MATERAIL_DESIGNS_CODES_20200908.xls ( yyyymmdd )

Let's try again

Does EVERY source workbook name end with _DESIGNS_CODES_yyyymmdd.xls ?
I am looking for pattern in the file name (before the date) if there is one

 

[Tumbad]

Sorry Yongle for confusion.

All files end with yyyymmdd.xls (date format in my opening post was wrong)

They need not end with _design_codes, they may end with any letters before yyyymmdd.xls

There is no pattern before date. but everyday only date changes and rest of the wordings in file name remain same.

Warm regards,
Tumbad

 

[Yongle]

Ok - no problem - will post code later this morning

The user will be required to confirm the date so that VBA knows which date to look for at end of file name
- the code will convert to yyyymmdd later
- input box should be simpe for the user

Is this ok for user?
Which date should be default date in the box?

Sub GetFileDate()
Dim DateStr As String
DateStr = InputBox("amend date", "Yesterday's date", Format(Date - 1, "dd mmm yy"))

End Sub

 

[Tumbad]

Thanks Yongle its helpful.

todays date can be default date and date format can be dd mmm yy.

Warm regards,
Tumbad

 

[Yongle]

Code below works for me

Test like this
1. Place code in Module1 in a NEW workbook
2. Complete file names A to N in GetFileName
3. Amend these constant values in Loop14Files
Const DestFullPath As String = "C:\Test\Folder\Subfolder\Destination.xlsx"
Const SourcePath As String = "C:\Test\Folder\Subfolder"
4. Save the workbook as macro enabled
5. Run Loop14Files

After testing
Let me know how you get on
Is there anything that you would prefer being done in a different way?
Is there anything you do not understand?

Option Explicit

Private Function GetFileName(ByVal Position As Integer) As String
    Dim A$, B$, C$, D$, E$, f$, G$, H$, I$, J$, K$, L$, M$, N$
    A = "SERIAL_DESIGNS_CODES_"
    B = "IMERIAL_DESIGNS_CODES_"
    C = "File_3"
    D = "File_4"
    E = "File_5"
    f = "File_6"
    G = "File_7"
    H = "File_8"
    I = "File_9"
    J = "File_10"
    K = "File_11"
    L = "File_12"
    M = "File_13"
    N = "MATERAIL_DESIGNS_CODES"
    GetFileName = Array(A, B, C, D, E, f, G, H, I, J, K, L, M, N)(Position)
End Function

Private Function GetDate() As String
    On Error Resume Next
    GetDate = Format(CDate(InputBox("amend date", "Today's date", Format(Date, "dd mmm yy"))), "yyyymmdd")
    On Error GoTo 0
End Function

Sub Loop14Files()
'variables and constants
    Dim Dest As Workbook, x As Long, fName As String, DateString As String, msg As String
    Const DestFullPath  As String = "C:\Test\Folder\Subfolder\Destination.xlsx"
    Const SourcePath    As String = "C:\Test\Folder\Subfolder"
    msg = "problem with date"
Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual
'user confirmation of date
    DateString = GetDate
    If Len(DateString) = 0 Then GoTo Handling
'open destination file
    Set Dest = Workbooks.Open(DestFullPath)
'loop 14 source files and copy data into sheets 2 to 15
    For x = 0 To 13
        fName = SourcePath & "\" & GetFileName(x) & DateString & ".xls"
        If Len(Dir(fName)) > 0 Then
            Dest.Sheets(x + 2).Cells.ClearContents
            With Workbooks.Open(fName)
                .Sheets(1).Cells.Copy Dest.Sheets(x + 2).Range("A1")
                .Close (False)
            End With
        Else
            msg = "File not found" & vbCr & fName: GoTo Handling
        End If
        msg = "done"
    Next x
'save and close destination file
    Dest.Close (True)

Handling:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox msg
End Sub