使用范围将数据输入到文件名 [英] Using a range to input data to a filename

问题描述

下午好我已经遇到这个代码的问题。如果我允许打开excel工作簿，然后使用 ActiveWorkbook.Close 函数关闭它，它的工作效果很好。如果我将 .close 函数注释掉，我会收到运行时错误。我希望所有的工作簿开放并保持开放。

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

推荐答案

这样的东西（未经测试）

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

这篇关于使用范围将数据输入到文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！