Need to modify TextJoin code to populate not just the top row cell, but all the way down

[CaptainKen]

I got TextJoin to work in a cell and within code as shown below. What I need to do is to go to the next step, which would be populate the rest of the rows in column C based on values in columns A and B. The number of rows is an unknown. I assume I'd need a Loop or something, but I'm not that knowledgeable.

Column A: Movie name (300 Collection)
Column B: Movie path (movie sets\300 Collection)
Column C: Results populated in this column

This is an example of the desired output:
xcopy "movie sets\300 Collection\*.jpg" "v:\300 Collection" /i

This works from within a cell:
=TEXTJOIN("",TRUE, "xcopy """, "m:\", B1, "\*.jpg"" ""v:\", A1, """ /i")

This works as VBA, although is inserts a single quote as the first character

Dim strResult As Sring
Dim Sep As String

'Sep represents one double quotation mark
Sep = """"
'MsgBox Sep & "TEXT HERE" & Sep & Range("B1").Value & Sep

strResult = WorksheetFunction.TextJoin("", True, "xcopy ", Sep, "m:\", Range("B1").Value, "\*.jpg", Sep, Space(1), Sep, "v:\", Range("A1").Value, Sep, "/i")
MsgBox strResult
Range("C1").Value = strResult

 

[Trebor76]

Hi CaptainKen,

Maybe this:

Option Explicit
Sub Macro1()

    Dim strResult As String
    Dim Sep As String
    Dim i As Long, j As Long
   
    Application.ScreenUpdating = False
   
    'Sep represents one double quotation mark
    Sep = """"
    'MsgBox Sep & "TEXT HERE" & Sep & Range("B1").Value & Sep
    j = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = 1 To j
        Range("C" & i).Value = WorksheetFunction.TextJoin("", True, "xcopy ", Sep, "m:\", Range("B" & i).Value, "\*.jpg", Sep, Space(1), Sep, "v:\", Range("A" & i).Value, Sep, "/i")
    Next i
   
    Application.ScreenUpdating = True
   
End Sub

I see you're using the command prompt to copy the files but VBA does have a FileCopy function that should do what you're after (refer here for an example).

Regards,

Robert

 

[CaptainKen]

Hi Robert, thank you that worked!

Only one issue, which is the same I had before posting. It's prefixing each row with an apostrophe/single quote as shown in this example.
'xcopy "m:\Westerns\Butch Cassidy and the Sundance Kid Collection\*.jpg" "v:\Butch Cassidy and the Sundance Kid Collection"/i

 

[Trebor76]

Not sure to be honest. It may have something to do with the TextJoin function.

I think you just need this:

Range("C" & i).Value = "xcopy ""m:\" & Range("B" & i).Value & "\*.jpg"" ""v:\" & Range("B" & i).Value & """/i"

Also check the formatting that's being applied to Col. C. Mine is General - yours might be Text??

 

[CaptainKen]

Thank you again. I tried both General and Text formatting for both Range examples and get the same results. I did a test copy the column into my batch file and it stripped out the apostrophe, so it must be some weird Excel thing.

Have a great evening...really appreciate the help and so quick.

 

[Trebor76]

Try deleting Col. C, ensuring the now new Col. C is formatted as General and then running the code again as maybe Col. C formatting is stuck somehow.

Have a great evening...really appreciate the help and so quick.

Thanks for the feedback and you're welcome

 

[CaptainKen]

Deleting the column got rid of the apostrophe!

 

[Peter_SSs]

@CaptainKen
Please be very careful with the information that you give us as that is all we have to go on. For example

This is an example of the desired output:
xcopy "movie sets\300 Collection\*.jpg" "v:\300 Collection" /i

How can that be when your code puts "m:\" somewhere in the result yet the above desired result does not have that?

This works as VBA,

It doesn't for me as this triggers a Compile error

Anyway, see if this would work for you to populate the whole of column C at once.
There may be a space character missing near the end - again that seems to be a discrepancy between your code and your written information above your code in post 1.

Sub Test()
  With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula2 = "=Concat(""xcopy """"m:\"", B1,""\*.jpg"""" """"v:\"",A1,""""""/i"")"
    .Value = .Value
  End With
End Sub

 

[Trebor76]

Deleting the column got rid of the apostrophe!

 

[CaptainKen]

Good catch, sorry about that missing "m:\". I copied the wrong desired results over. I was doing a lot of testing and got sloppy. I'd update the original post, but I see that's not possible.

Every piece of code you've supplied works the same, which is perfect.