VBA Variant array position for String

Asked

Viewed 289 times

0

Good morning,

I have in the Excel a search that returns a ARRAY of Variant filled with the full address of the found files.
I need to pass each array position to one STRING, I’m trying to wear a bow For...Next to pass each array position to a STRING with the function CStr() but an error of incompatible types and returned.
Follow the piece of code: 

For i = LBound(vArray) To UBound(vArray)
            On Error Resume Next
            strFile = CStr(vArray(i))
            Set myDoc = wdApp.Documents.Open(Filename:=strFile, ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
            .Cells(i, 1).Value = myDoc.txtNome.Text
            .Cells(i, 2).Value = myDoc.txtNis.Text
            .Cells(i, 3).Value = myDoc.txtCpf.Text
            .Cells(i, 4).Value = myDoc.txtEnd.Text
            .Cells(i, 5).Value = myDoc.txtCep.Text
            .Cells(i, 6).Value = myDoc.Combobox1.Value
            myDoc.Close SaveChanges:=False
        Next i

What I’m doing wrong, there’s some other way to do this procedure?

Thanks in advance!!!

excel vb.net vba excel-vba office-2013

  • In which line does the error occur? And it has an example array with which it causes the error?

  • An error of incompatible types occurs on this strfile line = Cstr(vArray(i)). The Array is filled in correctly before executing the quoted line by executing a Debug.Print on it and the data is displayed correctly. I cannot pass a specific position of it to a String.

  • But how is it an example? Like C:\Users\usuario\Desktop\teste\arquivo.docm?

  • Sorry, I am running over the network. It would be PC01 System 2019 LOTE 8 3808 OK.Docm file

  • I created an array with vArray = Array("\\PC01\Sistema\2019\LOTE 8\3808\APROVADOS\arquivo.docm", "\\PC01\Sistema\2019\LOTE 8\3808\APROVADOS\arquivo2.docm", "\\PC01\Sistema\2019\LOTE 8\3808\APROVADOS\arquivo3.docm") and I couldn’t reproduce your error. Try checking the local variables window and post a photo of your array.

1 answer

3


In VB.NET you can use LINQ to do this, but as you are using VBA you can simply loop and concatenate the string. In your example, I think it would be as follows:

strFile = strFile & CStr(vArray(i))

    For i = LBound(vArray) To UBound(vArray)
        On Error Resume Next
        strFile = strFile & CStr(vArray(i))
        Set myDoc = wdApp.Documents.Open(Filename:=strFile, ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
        .Cells(i, 1).Value = myDoc.txtNome.Text
        .Cells(i, 2).Value = myDoc.txtNis.Text
        .Cells(i, 3).Value = myDoc.txtCpf.Text
        .Cells(i, 4).Value = myDoc.txtEnd.Text
        .Cells(i, 5).Value = myDoc.txtCep.Text
        .Cells(i, 6).Value = myDoc.Combobox1.Value
        myDoc.Close SaveChanges:=False
    Next i

If you want to leave a space between the values of the vector you have to concatenate a space as well, like this:

strFile = strFile & CStr(vArray(i)) & " "

Browser other questions tagged excel vb.net vba excel-vba office-2013

You are not signed in. Login or sign up in order to post.