VBA - Reading a text file, splitting it into array and printing it back in VBA Word

Question

Hello I would like to take my txt file containing string of data, split it into array by line. However, I am not able to either input data to array correctly or I have problem with the function GetArrLength. I am pretty new to VBA and can't figure the problem out. The macro stops with Run-time error '13': type mismatch and highlights this section of the code:

GetArrLength = UBound(arr) - LBound(arr) + 1

Hopefully it's not a big issue. Thanks for any ideas.

Sub apokus()

'PURPOSE: Send All Data From Text File To A String Variable


Dim TextFile As Integer
Dim filePath As String
Dim FileContent As String
Dim strAll As String
Dim arrString() As String

'File Path of Text File
  filePath = InputBox("Path to your MD file.", "Path to MD", "actual path to the file")


'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile
  
'Open the text file
  Open filePath For Input As TextFile
  
'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)
  Close TextFile
  
arrString = Strings.Split(FileContent, vbCr)
Selection.TypeText Text:=GetArrLength(1)
Dim i As Long
For i = 1 To GetArrLength(arrString)
   Selection.TypeText Text:=GetArrLength(i) + vbNewLine
Next i

End Sub

Public Function GetArrLength(arr As Variant) As Long
   If IsEmpty(arr) Then
      GetArrLength = 0
   Else
      GetArrLength = UBound(arr) - LBound(arr) + 1
   End If
End Function

I guess your problem comes from Text:=GetArrLength(i). You ment probably arrString(i), but be carefull, split returns a 0-based array, so you will need to use arrString(i-1) — FunThomas
– FunThomas, Commented May 31, 2022 at 13:26

Timothy Rylatt · Accepted Answer · 2022-05-31 14:02:12Z

1

Your code should be as follows:

arrString = Strings.Split(FileContent, vbCr)
Selection.TypeText Text:=GetArrLength(arrString)
Dim i As Long
For i = 0 To UBound(arrString)
   Selection.TypeText Text:=arrString(i) + vbNewLine
Next i

However, I can't see the point of your code. You take a text file that contains a number of paragraphs, remove the carriage returns, then insert the text into Word adding carriage returns back in.

answered May 31, 2022 at 14:02

Timothy Rylatt

7,9352 gold badges13 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jahelnice Over a year ago

thanks for really elegant solution. I plan to work more with the text and make some changes to the style and I thought that it will be easier to change the style by line than as a whole long text document. If you think it is a bad approach, I'm open to and thankful for any new ideas.

Collectives™ on Stack Overflow

VBA - Reading a text file, splitting it into array and printing it back in VBA Word

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related