Export Parsed VBA string from Word to Excel 1

[BitNet33]

Hey all,

I have a string in Word that I have Parsed using the Split() function and now want to export each element of the array into Excel so that all of the elements are in the same collumn but the rows get incremented with a single element in each row.

For example,

Dim str as String
str = "1 2 3 4 5"

Dim MyArray() as String
MyArray = Split(str)

I then want to export MyArray to Excel so that MyArray(0) would be in Cell A1, MyArray(1) would be in A2, MyArray(2) would be in A3,...etc.

What would the code be to achieve this?

Thanks for the help!
BitNet33

 

[SkipVought]

hi,

Would that be an existing workbook or not?

In general...

Sub test()
Dim str As String, MyArray() As String, i As Integer, xl As Object
  str = "1 2 3 4 5"
  
  MyArray = Split(str)
  
  Set xl = CreateObject("Excel.Application")
  
  With xl.Workbooks.Add
    With .Worksheets(1)
        For i = 0 To UBound(MyArray)
            .Cells(i + 1, "A").Value = MyArray(i)
        Next
    End With
    'save it???
    .SaveAs somepathandname.xlsx
    'close it???
    .Close
  End With
  
  Set xl = Nothing
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BitNet33]

Thanks SkipVought! That worked perfectly! and no its not an existing workbook.

One question about saving the file, can I save the name of the .xlsx file using a variable?

 

[PHV]

can I save the name of the .xlsx file using a variable?
yourVariable = "\path\to\workbook.xlsx"
.SaveAs yourVariable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveInIowa]

I thought there might be a way to do this without looping and found this technique:

Public Sub Test()
    Dim s As String
    Dim v As Variant
    
    s = "1 2 3 4 5"
    
    v = Application.WorksheetFunction.Transpose(Split(s))
    
    Range("A1").Resize(UBound(v, 1)) = v
    
End Sub