Using a range to input data to a filename

Question

Good Afternoon. I have been running into an issue with this code. It works perfectly if I allow the excel workbook to open, and then close it using the ActiveWorkbook.Close function. If I comment the .close function out, I get the runtime error. I would desire all workbooks to open and stay open.

Sub openwb1()
    Dim EPath As String, EFile As String, EMo As String, EVar As String, lastrow As Long, counter As Long, EFound As String

    lastrow = Worksheets("Opener").Cells(Rows.Count, 1).End(xlUp).Row

For counter = 1 To lastrow

    EPath = "Q:\MY PATH\"
    EVar = Worksheets("Opener").Range("A" & counter).Value
    EMo = MonthName(DatePart("m", Now), True) & " " & DatePart("yyyy", Now) & "\"
    EFound = Dir(EPath & EVar & EMo & "*$*.xlsx")

    If EFound <> " " Then
       Workbooks.Open FileName:=EPath & EVar & EMo & "\" & EFound
       End If

    If Len(Dir(EPath & EVar & EMo, vbDirectory)) = 0 Then
        MkDir EPath & EVar & EMo
        End If

    'ActiveWorkbook.Close

Next counter
End Sub

The variable `counter` doesn't appear to be used inside the loop. Try changing `EVar = Worksheets("Opener").Range("A1").Value` to `EVar = Worksheets("Opener").Range("A" & counter).Value` — Chris Slade, Jun 17 '16 at 20:37
Do you want `EVar = Worksheets("Opener").Range("A" & counter).Value` ? Also, it's worth mentioning to fix your line to `lastrow = Worksheets("Opener").Cells(Worksheets("Opener").Rows.Count, 1).End(xlUp).Row` — BruceWayne, Jun 17 '16 at 20:38
`EPath = EPath = "C:My path\"` should be `EPath = "C:\My path\"` — Maximilian Peters, Jun 17 '16 at 20:50
yes that is correct ashfix; however the result is the same. It opens the file path in A1 and the errors for the name is A2. It gives me subscript out of range error — E.Pyles, Jun 17 '16 at 20:53
Add some `debug.print` and it will become obvious where the weak link is. — findwindow, Jun 17 '16 at 21:08
my last row is showing false.. with the Debug.Print I am stumped. — E.Pyles, Jun 17 '16 at 21:19
So what does Debug.Print(Worksheets("Opener").Cells(Worksheets("Opener").Rows.Count, 1).End(xlUp).Row) yield, or just Debug.Print(lastrow)? — Absinthe, Jun 18 '16 at 21:36
No multiple sheets. 1 sheet. Several excel files that I am trying to open automatically — E.Pyles, Jun 20 '16 at 17:32
`MonthName(DatePart("m", Now), True) & " " & DatePart("yyyy", Now) & "\"` could be changed to `Format(Now, "mmm yyyy") & "\"` — PaichengWu, Jun 21 '16 at 00:39

PaichengWu · Answer 1 · 2016-06-18T15:25:52.513

0

Try to change EVar = Worksheets("Opener").Range("A" & counter).Value to EVar = Thisworkbook.Worksheets("Opener").Range("A" & counter).Value.

BTW, lastrow = Worksheets("Opener").Cells(Worksheets("Opener").Rows.Count, 1).End(xlUp).Row could be changed to lastrow = Worksheets("Opener").Cells(Rows.Count, 1).End(xlUp).Row, because every worksheet in this workbook has the same Rows.Count. You do not need to specify which worksheet to count.

edited Jun 18 '16 at 15:25

answered Jun 17 '16 at 23:58

PaichengWu

2,649
1
14
28

I am sorry. That did not work. I have posted my results to this thread. – E.Pyles Jun 20 '16 at 14:26

score 0 · Accepted Answer · answered Jun 21 '16 at 00:14

Something like this (untested)

Sub openwb1()

    Dim EPath As String, EFile As String, EMo As String
    Dim EVar As String, lastrow As Long, counter As Long, EFound As String
    Dim wb As Workbook

    lastrow = Worksheets("Opener").Cells(Rows.Count, 1).End(xlUp).Row

    'next two lines do not need to be inside the loop
    EPath = "Q:\MY PATH\"
    EMo = MonthName(DatePart("m", Now), True) & " " & DatePart("yyyy", Now) & "\"

    For counter = 1 To lastrow

        EVar = Worksheets("Opener").Range("A" & counter).Value

        If Len(Dir(EPath & EVar & EMo, vbDirectory)) = 0 Then

            MkDir EPath & EVar & EMo

        Else
            'only check for a file if the source folder was found...
            EFound = Dir(EPath & EVar & EMo & "*$*.xlsx")

            If EFound <> "" Then
                Set wb = Workbooks.Open(Filename:=EPath & EVar & EMo & "\" & EFound)

                'do something with wb

                wb.Close False 'don't save changes?

            End If

        End If

    Next counter

End Sub

Using a range to input data to a filename

2 Answers2

Linked