VBA Coding for opening all the workbooks and writing the file name inside them

[Amit Punia]

I need a VBA where I it updates the name of the excel file inside the excel file. There are 24 files in the folder. Path for this folder is D:\Amit\ and there are 12 files in it namely "Cash Report as on 11-05-2017 0000Hrs". We prepare these files daily after every 2 hours, so starting from midnight - 0000Hrs, 0200Hrs, 0400Hrs etc. Sometimes it does happen that we run the file after 3 hours making it 0500Hrs instead of 0400Hrs just after 0200Hrs. What I need is a VBA file which opens all these 12 files and in column A in the last row(just after the last row having any text, one cell below that need the file name which is opened. So if the VBA opened file "Cash Report as on 11-05-2017 0400Hrs" then in the last cell of the column A just after the text or data in the cell, using offset the very below blank cell should have the name of this file as "Cash Report as on 11-05-2017 0000Hrs". Likewise need something like this for all the files.

I was trying some of the codes but it's still in bits and pieces.

 fldr = Activeworkbook.Path 
Dt = Application.InputBox("Enter Date as 'dd-mm-yyyy' ", format(Now," dd-mm-yyyy"
Workbooks.open Filename:= fldr & "\Cash Report as on" & 0400 & "Hrs.xlsx"
Range("A1").End(xlDown).Select
Offset(1).Select

 

[PCosta87]

Hi,

The following will loop through all "xlsx" files in the specified folder and write the file's name after the last used cell of column A:

Sub LoopThroughFiles()

    Application.ScreenUpdating = False

    Dim MyObj As Object, MySource As Object, file As Variant
  
    file = Dir("D:\Amit\")
  
    While (file <> "")
        If InStr(file, ".xlsx") > 0 Then
            Workbooks.Open file
            Cells(Rows.Count, 1).End(xlUp).Offset(1) = Replace(file, ".xlsx", "")
            ActiveWorkbook.Close True
        End If
        file = Dir
    Wend

    Application.ScreenUpdating = True
  
End Sub

I suggest you test this in a different path with some sample files first.

I hope this helps

 

[Amit Punia]

@PCosta87 : I copied the code which you shared above, however it's showing an error while running the VBA.

Run-time error '1004':
'Cash Report as on 11-05-2017 0000Hrs.xlsx' could not be found. Check the speeling of the file name. Though I kept the same file and there is not mistake in the name of the file.

Screenshot attached.

Thanks for responding

 

[PCosta87]

@PCosta87 : I copied the code which you shared above, however it's showing an error while running the VBA.

Run-time error '1004':
'Cash Report as on 11-05-2017 0000Hrs.xlsx' could not be found. Check the speeling of the file name. Though I kept the same file and there is not mistake in the name of the file.

Screenshot attached.

Thanks for responding

Hi,

I'm not sure what is causing the error as here it seems to work fine.
Let me ask you this:

1) Where did you paste the code?

2) Is the file hosting the code one of the files in "D:\Amit\"?
If it is, try running the code from a file stored in a different location (or a new file not yet saved). The code loops through all files in the folder which could cause problems if the file hosting the code is one of them.

Please test this and report back, thanks.

 

[Amit Punia]

Thank you so much @PCosta87 for the help. It's working absolutely fine now

 

[PCosta87]

Thank you so much @PCosta87 for the help. It's working absolutely fine now

I'm glad to hear that