VBA to extract file information, add any new information after last row of data

Question

Sub GetFileList()

    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim objOL As Object
    Dim Msg As Object
    Dim xPath As String
    Dim thisFile As String
    Dim i As Integer
    Dim lastrow As Long
    
    xPath = Sheets("UI").Range("D7")
    
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    
    Set xFolder = xFSO.GetFolder(xPath)

    i = 1

    
    For Each xFile In xFolder.Files
     i = i + 1
        
        Worksheets("Info").Cells(i, 1) = xPath
        Worksheets("Info").Cells(i, 2) = Left(xFile.Name, InStrRev(xFile.Name, ".") - 1)
        Worksheets("Info").Cells(i, 3) = Mid(xFile.Name, InStrRev(xFile.Name, ".") + 1)
        Worksheets("Info").Cells(i, 6) = Left(FileDateTime(xFile), InStrRev(FileDateTime(xFile), " ") - 1)

        
    Next
    Set Msg = Nothing
    
    Worksheets("Info").Visible = True
    Worksheets("Info").Activate
    



End Sub

The code to extract file information from a folder. The issue is when I change the folder path, it overwrites on the previously fetched data.

Sheet -UI is where the sub executed on press of button, Sheet Info is the place where the data needs to be pasted.

How to write the code to add a new row of data after the data which is already available. If the sheet is blank then add data from the 1st ROW otherwise add data from the LAST ROW.

https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-a-specific-column — Tim Williams, Jul 02 '20 at 06:16
@TimWilliams -- This looks to be a different functionality implemented. Can you pls help with the above code? — Win_umra, Jul 02 '20 at 06:24
Your question is how to find the last row/first empty row. If there's no data that will be row1 — Tim Williams, Jul 02 '20 at 06:27
@TimWilliams suggestion is your answer. This can very easily be implemented in your code. Please ask nicely and I'm sure Tim will help you :) — Zac, Jul 02 '20 at 08:58

Matthias Fuglsang-Damgaard · Accepted Answer · 2020-07-02T08:16:20.497

0

Sheets("UI").Range("A1").End(xlDown).Select
i = Selection.Row + 1

edited Jul 02 '20 at 08:16

answered Jul 02 '20 at 06:35

Matthias Fuglsang-Damgaard

303
2
15

When I am including this code, it gives run time error '5'. Invalid procedure call or argument. Sheet -UI is where the sub executed on press of button, Sheet Info is the place where the data needs to be pasted. The headers are already pasted in cell A1 of the sheet "info" – Win_umra Jul 02 '20 at 07:35
Can you help? The FIRST ROW has headers so want to skip it – Win_umra Jul 02 '20 at 07:57
Try this instead. If you have a header there is no need to check if the first row i empty :-) – Matthias Fuglsang-Damgaard Jul 02 '20 at 08:17
I hope you find the answer satisfactory :-) – Matthias Fuglsang-Damgaard Jul 02 '20 at 08:48

Charles Peirce · Answer 2 · 2020-07-02T06:52:49.780

0

Try replacing

i = 1

with

i = Worksheets("Info").UsedRange.Rows.Count + 1

This will set i to 1 the first time around, and to the first free row ever after. New data will be added below the existing data, if there is any.

edited Jul 02 '20 at 06:52

answered Jul 02 '20 at 06:37

Charles Peirce

1
2

It is pasting out the values in random cell, even if I have an empty sheet. Only headers are there i.e. sheet is empty starting from cell A2. – Win_umra Jul 02 '20 at 07:27

VBA to extract file information, add any new information after last row of data

2 Answers2