Visual Basic Excel - Create Folders using 2 columns

Question

Visual Basic Excel - Create Folders using 2 columns

Navigation

#1 by (0 votes)

1

I am new to Visual Basic 6 and I have a problem.

I need to create folders using two columns, that is, in each folder I have to appear the contents of A1 and F1 separated by a low script, for example, and so on.

Searching forums and YouTube I found:

Sub CrearCarpetas()
    ruta = InputBox("INGRESAR LA RUTA")
    Range("A2").Select

    Do While ActiveCell.Value <> ""
       MkDir (ruta & "/" & ActiveCell.Value)
       ActiveCell.Offset(1, 0).Select
    Loop
End Sub

And similar, which are fine but only serve for one column. What I need is, based on a basic example of an excel sheet:

Nombre Apellido Otros
1      a        mm
2      s        nn
3      d        pp
4      f        kk

I want to create folders with the contents of A2_C2, A3_C3, etc ... I have tried with random things of the style:

Sub CrearCarpetas2()
    ruta = InputBox("INGRESAR RUTA")

    Dim fila1 As String, fila2 As String

    fila1 = Range("A2").Select
    fila2 = Range("C2").Select

    If Dir(fila1 & fila2) = Empty Then
        MkDir (ruta & "/" & fila1 & "_" & fila2)
    End If
End Sub

But a folder with values true_true is created. Any suggestions?

vba excel

asked by doomhammer 09.12.2018 в 22:30

source

1 answer

Convert parameters of a javascript array Attempt to invoke virtual method 'void android.widget.TextView.setText (java.lang.CharSequence)' on a null object reference

score 0 · Accepted Answer

Ok, there are several problems.

The first is the ignorance of Excel macros written in VBA. For this reason I recommend that you look at the Excel documentation: link (in this case we have used the Range object that has a property called Cells, which returns two-dimensional arrays with the value of each column-row).

If the terminology is unknown: method, property, object, matrices, I recommend a basic programming course. I also recommend in this case, what stores to think as a programmer, basically, according to the objective you want to meet, to segment the problems into smaller problems. If you need to create as many folders as rows there are two underlying problems, the first to know the number of rows to be read and the second to read those values.

Secondly, the code you found on YouTube currently only works for one column basically because it is focused. Basically, what I suppose this will do is to select the rows and extract the value from the current cell. You can focus with that code, but you would have to modify ActiveCell.Offset(1, 0).Select where 0 would be changed by the destination number, if we are in cell A then we would have to go to F, that is, 5 cells to the right.

Therefore, not to make it very complicated, the solution would be this:

link

Option Explicit

'Dim ruta As String

Sub CrearCarpetas()

'ruta = InputBox("INGRESAR LA RUTA")

Dim lRow As Long

'Busca la ultima columna sin valor en la columna A(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Row 'El objeto cells devuelve una matriz de 2 dimesiones,
                                          'donde el primer parametro representa el rango de filas a coger y el segundo el numro de columnas
                                          'Con el metodo End lo que haremos será ir, en este caso, a la última celda
                                          'https://www.excelcampus.com/vba/find-last-row-column-cell/
Dim i As Integer

'Iteramos empezando desde A2 hasta A[x] donde x es el número de celdas
For i = 2 To lRow
    MsgBox (Cells(i, 1).Value & "_" & Cells(i, 2).Value)
    'Vamos obteniendo los valores donde i es el número actual de la celda
    'Y donde 1 y 2 representan A y B, si es F, pues ya sería 6

    'MkDir(ruta & "/" & Cells(i, 1).Value & "_" & Cells(i, 2).Value)
Next i

End Sub

The code is explained within the comments of this.

I hope I have helped.