Visual Basic para Aplicaciones - ¿Cómo llamar hojas por su número de índice desde VBA?

Life is soft - evento anual de software empresarial
 
Vista:
sin imagen de perfil
Val: 5
Ha disminuido su posición en 10 puestos en Visual Basic para Aplicaciones (en relación al último mes)
Gráfica de Visual Basic para Aplicaciones

¿Cómo llamar hojas por su número de índice desde VBA?

Publicado por Luciano (2 intervenciones) el 01/07/2020 00:50:32
Hola, muy buenas a todos los del grupo.

No sé si existe alguien que se haya topado con este problema que el día de hoy no me deja dormir.

Tengo un libro con una hoja llamada “CSV”, que tiene una tabla llamada “t_csv” donde lleva una columna para colocar el número de Hoja, otra columna para la Celda, otra columna con un texto en español y finalmente una columna con ese texto en inglés. Esta hoja “CSV” es para hacer la traducción de otras hojas de manera automática, muestro una parte de la tabla “t_CSV”.

CSV

Ya había programado el procedimiento para hacer mi traducción, pero recién agregué una hoja llamada “Data”, lo que hizo que mi traducción comenzara a fallar. La falla: la posición de esta nueva hoja. Cosa que en teoría no debería ser debido a que estoy tratando de usar el Índice de las hojas.

Después de darle muchas vueltas, me di cuenta que al usar la función Sheets(Hoja), donde Hoja = número de hoja tomado de la columna “Hoja” de “CSV” toma el orden de las hojas del libro de izquierda a derecha, sin embargo, mi intención es justamente evitar esto y usar el índice de hoja dado por la lista de objetos de VBA.

orden orden_VBA

He estado tratando de solucionar este bug en mi programación desde hace un par de días, pero no he logrado dar con la solución que me gustaría emplear. Ya que quiero usar un código como este:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub traduccion ()
Dim lang As String
Dim Hoja As Integer
Dim celda As String
Dim dato As String
‘Obtener idioma del boton btn_esp o btn_eng  de un formulario llamado “f_Login”
  If f_Login.btn_esp.Value = True Then lang = "Español"
  If f_Login.btn_eng.Value = True Then lang = "English"
‘Bucle para obtener los datos de la tabla “t_csv” ubicados en la hoja “CSV” (Sheet2)
  For i = 1 To Sheet2.ListObjects("t_csv").DataBodyRange.Rows.Count
     With Sheet2.ListObjects("t_csv")
          Hoja = .ListColumns("Hoja").DataBodyRange(i).Value
          celda = .ListColumns("Celda").DataBodyRange(i).Value
          dato = .ListColumns(lang).DataBodyRange(i).Value
          ‘Colocar o sobreescribir texto de la celda correspondiente (código a cambiar)
          Sheets(Hoja).Range(celda).Value = dato
     End With
  Next i
End Sub

El procedimiento de arriba funciona sin problemas siempre y cuando el índice de hoja sea igual al orden de izquierda a derecha de las hojas en el libro. Pero me gustaría cambiar por algo de este estilo Sheet3.Range(“A1”).Value = dato
pero de manera automática quedando algo como Sheet & Hoja.Range(celda).Value = dato

El problema con mi idea es que la función Sheet1.Range o Sheet2.Range, etc., no me permite cambiar de manera automática por más que intento todos los trucos que sé para hacerlo. Porque no puedo convertir un string en un objeto del tipo sheet o worksheet.

Ya que si uso esto
1
2
3
Dim HojaIndex As Worksheet
Set HojaIndex = “Sheet” & Hoja
HojaIndex.Range(celda).value = dato

No me lo permite ya que “Sheet” & Hoja no es un objeto
Lo que si me permite es
1
2
3
Dim HojaIndex As Worksheet
Set HojaIndex = Sheet3
HojaIndex.Range(celda).value = dato

Pero no sé cómo indicarle al código que el número 3 va a ir cambiando según lo requiera.

Creo que se podría hacer usando la función CallByName, el detalle es que no sé usarla correctamente. O desconozco alguna forma de convertir un string en un objeto con las propiedades de una hoja.

Espero que alguien me pueda ayudar con esta consulta.

Se me ocurren algunas maneras para hacer funcionar mi código original:

1. Poner en el mismo orden las hojas, no llevaría más de 1 minuto hacerlo.
El detalle es que varias de estas hojas van a quedar completamente ocultas cuando termine de programar todos los procedimientos, lo que volvería hacer aparecer este problema del índice de hojas.

2. En lugar de usar números en la columna Hoja de “CSV” podría poner el nombre de la hoja para llamarla.
Pero en lo particular se me hace más practico usar números para poder hacer ciclos, pero funcionaria siempre y cuando alguien no decida cambiar los nombres.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
sin imagen de perfil
Val: 5
Ha disminuido su posición en 10 puestos en Visual Basic para Aplicaciones (en relación al último mes)
Gráfica de Visual Basic para Aplicaciones

¿Cómo llamar hojas por su número de índice desde VBA?

Publicado por Luciano (2 intervenciones) el 01/07/2020 18:55:57
Una pequeña actualización. Me han recomendado usar la función Worksheets().

Ya he probado con lo siguiente en la línea de Código 16.

1
Worksheets(Hoja).Range(celda).Value = dato

Pero esta funcion se comporta exactamente igual que sheets().

Sigo en la busqueda de pasar el String "sheet3" en objeto sheet3...
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de RET
Val: 117
Bronce
Ha mantenido su posición en Visual Basic para Aplicaciones (en relación al último mes)
Gráfica de Visual Basic para Aplicaciones

¿Cómo llamar hojas por su número de índice desde VBA?

Publicado por RET (32 intervenciones) el 06/07/2020 13:42:13
Create una funcion a la que pases el parámetro Worksheet.Name y te recorra un for x=1 to Sheets.Count next hasta que Sheets(x).Name=Parametro. Con eso consegurirás saber el inidce de cada hoja.

IMF_RET
https://InformaticaMuyFacil.com
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de MIGUEL
Val: 451
Plata
Ha mantenido su posición en Visual Basic para Aplicaciones (en relación al último mes)
Gráfica de Visual Basic para Aplicaciones

¿Cómo llamar hojas por su número de índice desde VBA?

Publicado por MIGUEL (111 intervenciones) el 26/07/2020 06:36:48
Buenas Luciano

Modifica esta línea de tu codigo y me cuentas

1
Sheets("Sheet" & Hoja).Range(celda).Value = dato

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de MIGUEL
Val: 451
Plata
Ha mantenido su posición en Visual Basic para Aplicaciones (en relación al último mes)
Gráfica de Visual Basic para Aplicaciones

¿Cómo llamar hojas por su número de índice desde VBA?

Publicado por MIGUEL (111 intervenciones) el 26/07/2020 19:32:34
Buenas Luciano

perdon por el mensaje anterior no se en que estaba pensando xd.

te dejo la macro.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Sub traduccion()
Dim lang As String
Dim Hoja As Integer
Dim HojaNom As String
Dim celda As String
Dim dato As String
'Obtener idioma del boton btn_esp o btn_eng  de un formulario llamado “f_Login”
  If f_Login.btn_esp.Value = True Then lang = "Español"
  If f_Login.btn_eng.Value = True Then lang = "English"
'Bucle para obtener los datos de la tabla “t_csv” ubicados en la hoja “CSV” (Sheet2)
  For i = 1 To Sheet2.ListObjects("t_csv").DataBodyRange.Rows.Count
     With Sheet2.ListObjects("t_csv")
          Hoja = .ListColumns("Hoja").DataBodyRange(i).Value
          celda = .ListColumns("Celda").DataBodyRange(i).Value
          dato = .ListColumns(lang).DataBodyRange(i).Value
          HojaNom = traerNombreHoja(Hoja)
          If HojaNom = "" Then Exit Sub
          'Colocar o sobreescribir texto de la celda correspondiente (código a cambiar)
          Sheets(HojaNom).Range(celda).Value = dato
     End With
  Next i
End Sub
 
Private Function traerNombreHoja(index As String) As String
    Dim a As Integer
    Dim nomHoja As String
    Dim existe As Byte
    Dim Hoja As String
    nomHoja = "Sheet"
    For a = 1 To Sheets.Count
        If Sheets(a).CodeName = nomHoja & index Then
            Hoja = Sheets(a).Name
            existe = 1
            Exit For
        Else
            existe = 0
        End If
    Next a
    If existe = 1 Then
        traerNombreHoja = Hoja
    Else
        traerNombreHoja = ""
        MsgBox "La Hoja no existe"
    End If
End Function

de esta forma no importa que le cambien el nombre a la hoja, pero ojo si pasas el codigo a una pc con configuracion en español no funcionara ya que los CodeName en este idioma es "Hoja1" en vez de "Sheet1".

Espero te sirva

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar