Visual Basic : setting a workbook or sheet returns Nothing

Question

I have a problem that prevents me from continuing on my task. With all variables declared, and with the two Workbooks I have to work with opened, I don't manage to make the macro read values from both workbooks. When I use Set wbSource = workbooks.open(sFileName), it returns Nothing to wbSource. The same with all other variables:

Dim nColumnas As Double

Dim nFilas As Double

Dim mec, dis, trans As Double

Dim rnData As Range

Dim i, j As Double

Dim Z As Double

Dim rango As Range

Dim hojaOrigen As String

Dim hojaDestino As String

Dim temporal As Variant

Dim N, Q, E As Long

Dim lo As ListObject

Dim op As Variant

Dim y, m, y_n As Long

Dim hojaGrafico As String

Dim wbSource As Workbook

Dim wbDest As Workbook

Dim QvsE As Worksheet, Requisitos As Worksheet, shEC As Worksheet

Dim myTable As Range

Dim permisos As String

'Filtro 

Call AbrirCarpeta(permisos)


Set wbSource = Workbooks.Open(sFileName)
If Err.Number <> 0 Then Debug.Print ("Error number en Libro Formato: " & Err.Number)
If Err.Number = 0 Then Debug.Print ("No ha habido error en libro Formato")

Set wbDest = Workbooks("Nuevo Hoja de cálculo de Microsoft Excel.xlsx")
If Err.Number <> 0 Then Debug.Print ("Error number en Nuevo hoja formato: " & Err.Number)
Workbooks("Nuevo Hoja de cálculo de Microsoft Excel.xlsx").Worksheets("Seleccion_ECs").Activate

Set shEC = wbDest.Worksheets("Seleccion_ECs")
If Err.Number <> 0 Then Debug.Print ("Error number en hoja Seleccion_ECs: " & Err.Number)

Set Requisitos = wbSource.Worksheets("Requisitos Contenedor + ATI")

Set QvsE = wbSource.Worksheets("Curva - QvsE")

'definir bien rango
Q = shEC.Range("1:1").Find(what:="BU Descarga (MWd/tU)", After:=Range("A1")).Column
E = shEC.Range("1:1").Find(what:="Enriq. [%]", After:=Range("A1")).Column
d = Requisitos.Cells(41 + Z, 4).Value

I expect to read values from both worksheets (decimal numbers), but I don't manage to read them. For example, d returns "Empty" as the variable Requisitos is Nothing, even if I connected it to wbSource.Worksheets("Requisitos Contenedor + ATI"), cause wbSource is Nothing. What can I do so it doesn't return Nothing? I previously have the Workbook "Nuevo Hoja de cálculo de Microsoft Excel.xlsx" Activated, but afterwards with Call AbrirCarpeta(permisos), I open the other Workbook "Formato CO-08a (rev.1).xlsx", so I suppose the last one becomes the activated workbook. I usually have errors like 91, 92 or 1004 in Err.number.

Thanks in advance

Why are you using Workbooks.Open if the workbook is already open? That's what is causing the problem. — Rory
When you declare multiple variables in one line you have to do it like so: Dim i as Double, j As Double, otherwise only the last one is declared as the type you want it to be and all the others are implicitly declared as Variant — Stavros Jon
To be a little more explicit than Stavros: mec, dis, i, N, Q, y and m are all variables of type Variant. — Nacorid
Do you have, by any chance, the infamous On Error Resume Next -statement? Remove it and have a look to the first error thrown. What is the content of sFileName by the way? — FunThomas
In lines Q = [...] After:=Range("A1")).Column and E = [...] After:=Range("A1")).Column you should probably fully qualify the Range... — Vincent G

otr_91 otr_91 · Accepted Answer · 2019-05-29T09:24:30

What Vincent G said helped me a lot. Seems it works now:

Call AbrirCarpeta(sFileName)

Set wbSource = Workbooks.Open(sFileName)

Debug.Print (wbSource.Name) '"Formato permisos.xlsm"

If Err.Number <> 0 Then Debug.Print ("Error number en Libro gráficas: " & Err.Number)

If Err.Number = 0 Then Debug.Print ("No ha habido error en libro gráficas")

Set Requisitos = wbSource.Worksheets("Requisitos Contenedor + ATI")
Set wbDest = ThisWorkbook 'Workbooks("Rober.xlsm")
If Err.Number <> 0 Then Debug.Print ("Error number en Libro Rober: " & Err.Number)
Set shEC = wbDest.Worksheets("Seleccion_ECs")
If Err.Number <> 0 Then Debug.Print ("Error number en hoja Seleccion_ECs: " & Err.Number)

Q = shEC.Range("1:1").Find(what:="BU Descarga (MWd/tU)", After:=Range("A1")).Column

E = shEC.Range("1:1").Find(what:="Enriq. [%]", After:=Range("A1")).Column

For i = 1 To nFilas ' Sheets("Seleccion_ECs").listaopciones.ListCount

    For Z = 0 To 6

        'Si %enriq está entre dos valores de enriquecimiento de la tabla requisitos

        If Requisitos.Cells(41 + Z, 4).Value - Requisitos.Cells(40 + Z, 4).Value = 0 Then

        Debug.Print ("Intervalo es cero:" & Requisitos.Cells(41 + Z, 2).Value & Requisitos.Cells(40 + Z, 2).Value)

         ElseIf (CDbl(shEC.Cells(i + 1, E).Value) >= Requisitos.Cells(40 + Z, 4).Value) And (CDbl(shEC.Cells(i + 1, E).Value) <= Requisitos.Cells(41 + Z, 4).Value) Then

            'Cuidado con volver a nombrar el workbook, así sale mal: wbSource.Requisitos.Cells(41 + Z, 4).Value

            m = (Requisitos.Cells(41 + Z, 2).Value - Requisitos.Cells(40 + Z, 2).Value) / (Requisitos.Cells(41 + Z, 4).Value - Requisitos.Cells(40 + Z, 4).Value)

            a = shEC.Cells(i + 1, E).Value

            y = m * (CDbl(shEC.Cells(i + 1, E).Value) - Requisitos.Cells(40 + Z, 4).Value) + Requisitos.Cells(40 + Z, 2).Value

            y_n = CDbl(shEC.Cells(i + 1, Q).Value)

            Debug.Print ("Se ha encontrado un intervalo en:")

            Debug.Print (Requisitos.Cells(41 + Z, 2).Value)

            Debug.Print (Requisitos.Cells(40 + Z, 2).Value)

            Debug.Print ("Enriquecimiento EC: " & CDbl(shEC.Cells(i + 1, E).Value))

            Debug.Print ("Quemado EC y_n: " & y_n)

            Debug.Print ("m: " & m)

            Debug.Print ("y: " & y)

            If y_n > 1.02 * y Then 'Si se sale en un 2 por ciento de la zona aceptada, se elimina la fila.

                shEC.Rows(i + 1).Delete

                Debug.Print ("Se ha eliminado un elemento")

                Exit For

            Else:

                Exit For

            End If

        End If

    Next Z

Next i

The only thing I find a problem now is to assign a Chart Sheet called "Curva-QvsE":

Set QvsE = wbSource.Charts("Curva - QvsE")

QvsE returns Nothing. I also tried

Set QvsE = ThisWorkbook.Charts("Curva - QvsE")

And other forms

Thank you for all your help

Visual Basic : setting a workbook or sheet returns Nothing

1 Answers