Solved: Concatenation of many cells from another workbook as a “block text”?

[trpkob]

I am looking to copy the contents of many cells from one workbook into another. I want to display it in a “block text” format across many cells so it looks appealing. I am using =Other_Workbook.xlsm!$A$798 to reference the other document and desired cell but I exceed the character limit allowed in a formula as I need to grab A798-A1054 and a concatenation gets long. = Other_Workbook.xlsm!$A$798&" "& Other_Workbook.xlsm!$A$799… won’t work. Suggestions?

[shrivallabha]

Not sure but have you tried like this:
Public Sub ConcatText()
Dim strConcat As String
For i = 798 To 1054
strConcat = strConcat & " " & Range("A" & i).Value
Next i
Range("A797").Value = Trim(strConcat) 'Set where you want the string to appear
End Sub

[trpkob]

How do I get this to reference the other workbook to pull these addresses and display them in the current one?

[shrivallabha]

Place this code in the Sheet's module where you want to reflect the data.
Public Sub ConcatText()
Dim rng As Range
Dim strConcat As String
For Each rng In Workbooks("Other_WorkBook").Sheets("Sheet1").Range("A798:A1054") 'Refer other WB
strConcat = strConcat & " " & rng.Value
Next rng
'Set where you want the string to appear
Range("A797").Value = Trim(strConcat)
End Sub

[trpkob]

Thank you! I am getting a compile error of “invalid outside procedure” it does not seem to like the reference to the other excel document.

[shrivallabha]

Thank you! I am getting a compile error of “invalid outside procedure” it does not seem to like the reference to the other excel document.
Can you please post the code as you have now and specify the line which offends?

[Teeroy]

The workbook name includes the file extension. Change "Other_WorkBook" to "Other_WorkBook.xlsm" and it should work.

[trpkob]

Thank you, have it working!