CODEWITHSUNDEEP
vbaexcel
Ebtic Bobo
Ebtic Bobo

Reputation: 79

Filename is empty when trying to open file

I try to merge workbooks from a folder in a new workbooks. The VBA code reads the excel file from folder, add every file name to a list box and then, after pressing the button "Start", add very file to the workbook. That is the idea.

The code is as folows: When opening the file the userform is shown:

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

When activating userform, the list box is populated:

Private Sub UserForm_Activate()
   Const strFolder As String = "C:\Users\user\Desktop\tmp\"
   Const strPattern As String = "*.xls"
   Dim strFile As String
   Dim collection As New collection
   Dim i As Integer
   Dim isMerger As Integer
   Dim lngth As Integer

   strFile = Dir(strFolder & strPattern, vbNormal)
   If (StrComp(strFile, "FileMerger.xls") <> 0) Then
       If (Len(strFile) <> 0) Then
           col.Add (strFolder & strFile)
           Do While Len(strFile) > 0
               strFile = Dir
               If (StrComp(strFile, "FileMerger.xls") <> 0) Then
                   If (Len(strFile) <> 0) Then
                       col.Add (strFolder & strFile)
                   End If
               End If
            Loop
        End If
    End If
    Vars.xlsFiles = ColToArray(collection)
    For i = 1 To UBound(Vars.xlsFiles)
        lstFiles.AddItem (Vars.xlsFiles(i))
     Next i
 End Sub

At this moment listbox and array Vars.xlsFiles are populated; they are OK.

Click on Start button in the userform:

Private Sub cmdStart_Click()
    Dim fileName As String
    Dim sheet As Worksheet
    Dim i As Integer
    Dim ub As Integer

    ub = UBound(Vars.xlsFiles)
    For i = 1 To ub
        Workbooks.Open fileName:=Vars.xlsFiles(i), ReadOnly:=True
        For Each sheet In ActiveWorkbook.Sheets
             sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next sheet
        Workbooks(fileName).Close
    Next i
 End Sub

In the folder are 3 files. Their name are in the listbox. But when the first is to be closed I received an error message and after debugging it says that fileName ="" (line Workbooks(fileName).Close). whatever I try I got the same error, i.e. fileName = "". What to do ?

Upvotes: 0

Views: 806

Answers (2)

user1590217
user1590217

Reputation: 11

FileName:= is a named parameter of the Workbooks.Open method. It does not set the value of the cmdStart_Click's fileName variable.

Private Sub cmdStart_Click()
    Dim fileName As String
    Dim sheet As Worksheet
    Dim i As Integer
    Dim ub As Integer

    ub = UBound(Vars.xlsFiles)
    For i = 1 To ub
        fileName = Vars.xlsFiles(i)
        Workbooks.Open FileName:=fileName, ReadOnly:=True
        For Each sheet In ActiveWorkbook.Sheets
             sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next sheet
        Workbooks(fileName).Close
    Next i
 End Sub

Upvotes: 1

arcadeprecinct
arcadeprecinct

Reputation: 3777

Your never set the variable fileName, so it's still the default value "". Maybe you got confused by the fileName:=Vars.xlsFiles(i) of the Workbooks.Open method. That just sets the option FileName of that method. Use some unique name to avoid confusion and set it to Vars.xlsFiles(i) or use

Workbooks(Vars.xlsFiles(i)).close

Upvotes: 1

Related Questions