Stop column data from moving

[kevindorward_dfsdanf]

Hello,

So i'm trying to build a macro-updating document register.

So column A = the full URL of the file location (input via macro - see below)
Column B uses the following formula

=LEFT(A2, FIND(CHAR(1), SUBSTITUTE(A2, "\", CHAR(1), LEN(A2)-LEN(SUBSTITUTE(A2, "\", ""))))-1)

to find the folder location. This is used to hyperlink to an 'Open Folder' column
Column C uses the following code

=TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",LEN(A2))),LEN(A2)))

to find the document name
Column D is 'Date Received' - This is input manually
Column F is 'Received From' - This is input via data validation
Column G is 'File Type' which uses the following code

=TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",LEN(A2))),LEN(A2)))

to find the file extension
Columns H encodes Column A as there is a hashtag in the URL path.
Column I encodes Column B as there is a hashtag in the URL path
Column J is a hyperlink to column H which opens the file
Column K is a hyperlink to column I which opens the containing folder
Column L is a document description - This is input manually

I macro that runs on a button press. It opens the terminal in Windows, changes to a set directory and then runs the dir /b /s /a-d |clip command to copy the file directory paths to the clipboard. It then selects column A, finds the last row, pastes the data and then fills down all the formulas in the columns next to it.

Here is the macro code

Sub Info_Received()

Call Shell("cmd.exe /S /c" & "cd /d C:\Users\12010\Desktop\Macro Example && dir /b /s /a-d |clip > nul", vbNormalFocus)
Columns(1).Select
Dim xColIndex As Integer
Dim xRowIndex As Integer
xColIndex = Application.ActiveCell.Column
xRowIndex = Application.ActiveSheet.Cells(Rows.Count, xColIndex).End(xlUp).Row
Range(Cells(2, xColIndex), Cells(Rows.Count, xColIndex)).Select
ActiveSheet.Paste
Range("B2:K2").AutoFill Destination:=Range("B2:K" & Cells(Rows.Count, "A").End(xlUp).Row)


End Sub

The dir command extracts all the links in the directory tree, starting with the first folder and listing documents A-Z. If i add something into the first folder in the directory tree with a document name that starts with a letter that comes before the one that appears on the first line of my sheet then it will appear at the top of the spreadsheet. However, if the columns that require manual input are already full, columns B-K will populate as they contain formulas but the document descriptions will remain in place and will then correspond to the wrong file if it has slotted a new one in.

Is there a way to keep all the current document names attached to the corresponding document path in column A and when a new document path is added that has no document name, it just stays blank?

Or if you can think of an easier way to make this auto-update register I would be keen to hear

Example:

There are 3 files in the folder C:\Users\12010\Desktop\Macro Example they are:

B Test.bmp
This is a word document.docx
Test.bmp

The output is:

[TABLE="width: 1827"]
<tbody>[TR]
[TD]DOCUMENT NAME[/TD]
[TD]DATE RECEIVED[/TD]
[TD]RECEIVED FROM[/TD]
[TD]FILE TYPE[/TD]
[TD]OPEN LINK[/TD]
[TD]OPEN FOLDER[/TD]
[TD]DOCUMENT DESCRIPTION[/TD]
[/TR]
[TR]
[TD]B Test[/TD]
[TD][/TD]
[TD][/TD]
[TD]bmp[/TD]
[TD]CLICK HERE[/TD]
[TD]CLICK HERE[/TD]
[TD]This is a PNG document[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD][/TD]
[TD][/TD]
[TD]bmp[/TD]
[TD]CLICK HERE[/TD]
[TD]CLICK HERE[/TD]
[TD]This is a PNG document[/TD]
[/TR]
[TR]
[TD]This is a word document[/TD]
[TD][/TD]
[TD][/TD]
[TD]docx[/TD]
[TD]CLICK HERE[/TD]
[TD]CLICK HERE[/TD]
[TD]This is a word document[/TD]
[/TR]
</tbody>[/TABLE]



When I add a new file into the folder called A test.txt the output is:

[TABLE="width: 1827"]
<tbody>[TR]
[TD]DOCUMENT NAME[/TD]
[TD]DATE RECEIVED[/TD]
[TD]RECEIVED FROM[/TD]
[TD]FILE TYPE[/TD]
[TD]OPEN LINK[/TD]
[TD]OPEN FOLDER[/TD]
[TD]DOCUMENT DESCRIPTION[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]txt[/TD]
[TD]CLICK HERE[/TD]
[TD]CLICK HERE[/TD]
[TD]This is a PNG document[/TD]
[/TR]
[TR]
[TD]B Test[/TD]
[TD][/TD]
[TD][/TD]
[TD]bmp[/TD]
[TD]CLICK HERE[/TD]
[TD]CLICK HERE[/TD]
[TD]This is a PNG document[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD][/TD]
[TD][/TD]
[TD]bmp[/TD]
[TD]CLICK HERE[/TD]
[TD]CLICK HERE[/TD]
[TD]This is a word document[/TD]
[/TR]
[TR]
[TD]This is a word document[/TD]
[TD][/TD]
[TD][/TD]
[TD]docx[/TD]
[TD]CLICK HERE[/TD]
[TD]CLICK HERE[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Where A is not a PNG file, it is just the same document description that was originally in that cell.


EDIT: I had thought about comparing column A with the data held on the clipboard and for entries that are not already in column A, insert a new row for them.

But also, if i delete a file out the folder structure and I had previously entered a description for it, then that will likely still remain also.

 

[kevindorward_dfsdanf]

So i've tweaked this a bit.

I have inserted a new column to the left of Column A (so it's now column A) which pastes the file path there.
I then compare Column A & B (which holds the last list of file paths).
If there is a filepath in Column A (the new list i.e. a file has been added to the directory) it copies that cell to the bottom of column B.
If there is a filepath in Column B (the old list i.e. a file has been deleted from the directory) it deletes that row.

Dim ws As Worksheet:    Set ws = ThisWorkbook.ActiveSheet    Dim rngCell As Range


    Application.ScreenUpdating = False
    With ws.UsedRange
        For Each rngCell In .Columns(1).Offset(1).Cells
            If WorksheetFunction.CountIf(.Columns(2), rngCell) = 0 Then
                Range("B" & Rows.Count).End(xlUp).Offset(1) = rngCell
            End If
        Next
        For Each rngCell In .Columns(2).Offset(1).Cells
            If WorksheetFunction.CountIf(.Columns(1), rngCell) = 0 Then
                rngCell.EntireRow.Delete
            End If
        Next
    End With
    Application.ScreenUpdating = True

However, now if column A is different to column B, it adds thee rows fine, but if i delete the files again, depending on how many extra file paths were in column A, it deletes them on running the macro in halves. So if it was 16 it deletes 8 then 4 then 2 then 1 then back to normal.

Would I be better to just add the new file paths to the bottom of the existing ones then set the range and use range.deleteduplicates ??