Thread: Incremental naming in cells by file and date

Mike,

In cell A1, try this formula:

=INDIRECT("'\\example\[1."&MOD(ROW()-1,31)+1&"_filename.xlsx]sheet'!I"&INT((ROW()-1)/31)+3)

You'll have to change the path, filename, and sheetname. Also, the formula will not work properly if it does not start in row 1.

EDIT: After testing, it looks like the formula only works properly when the referenced workbook(s) are open...

Mike,

This macro will accomplish what you're looking for.

How to use a macro:

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor
6. In Excel, press ALT+F8 to bring up the list of available macros to run
7. Double-click the desired macro (I named this one IncrementFileLink)

Sub IncrementFileLink()
    
    Const strDirectory As String = "\\example\"
    Const strFileName As String = "filename"
    Const strSheetName As String = "sheet"
    
    Dim i As Long
    For i = 1 To 310
        Cells(i, "A").Formula = "=" & Evaluate("=""'" & strDirectory & "[1.""&MOD(" & i & "-1,31)+1&""_" & strFileName & ".xlsx]" & strSheetName & "'!I""&INT((" & i & "-1)/31)+3")
    Next i
    
End Sub

Thank you sooo much!!!! Sooo close!! Right now it checks all the files, gets the data, etc... but adds all of the data to column A1. Is there a way to break them by row?

='\\example\[1.1_filename.xlsx]sheet'!I3 (starting at A1)
='\\example\[1.2_filename.xlsx]sheet'!I3 (..........A2)
='\\example\[1.3_filename.xlsx]sheet'!I3 (A3.....)
='\\example\[1.4_filename.xlsx]sheet'!I3 (A4)
='\\example\[1.5_filename.xlsx]sheet'!I3
etc..... to 1.31

Thanks again!!

-Mike

I don't understand, isn't the macro doing that already? Do you mean you want to split it up into different columns?

Mike,

I'm pretty sure you want one of these two, but I don't know which, so here's both:

Sub IncrementFileLink2()
    
    Const strDirectory As String = "\\example\"
    Const strFileName As String = "filename"
    Const strSheetName As String = "sheet"
    
    Dim r As Long
    Dim c As Long
    Dim i As Long
    
    For c = 1 To 10
        For r = 1 To 31
            i = i + 1
            Cells(r, c).Formula = "=" & Evaluate("=""'" & strDirectory & "[1.""&MOD(" & i & "-1,31)+1&""_" & strFileName & ".xlsx]" & strSheetName & "'!I""&INT((" & i & "-1)/31)+3")
        Next r
    Next c
    
End Sub

Sub IncrementFileLink3()
    
    Const strDirectory As String = "\\example\"
    Const strFileName As String = "filename"
    Const strSheetName As String = "sheet"
    
    Dim r As Long
    Dim c As Long
    Dim i As Long
    
    For r = 1 To 10
        For c = 1 To 31
            i = i + 1
            Cells(r, c).Formula = "=" & Evaluate("=""'" & strDirectory & "[1.""&MOD(" & i & "-1,31)+1&""_" & strFileName & ".xlsx]" & strSheetName & "'!I""&INT((" & i & "-1)/31)+3")
        Next c
    Next r
    
End Sub

in the code, r is the row number and c is the column number. A1 is row 1, column 1. If you needed it to start in D4 (which is row 4, column 4), you would change the Cells(r, c)... line to be Cells(r + 3, c + 3)....

To start in E2 (which is row 2, column 5), you would use:

Cells(r + 1, c + 4).Formula = ...

EDIT: Ack! double-post due to database error