0

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

Rory
  • 32,730
  • 5
  • 32
  • 35
otr_91
  • 11
  • 3
  • 2
    Why are you using `Workbooks.Open` if the workbook is already open? That's what is causing the problem. – Rory May 20 '19 at 11:15
  • 2
    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 May 20 '19 at 11:19
  • To be a little more explicit than Stavros: mec, dis, i, N, Q, y and m are all variables of type `Variant`. – Nacorid May 20 '19 at 11:25
  • 1
    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 May 20 '19 at 11:25
  • In lines `Q = [...] After:=Range("A1")).Column` and `E = [...] After:=Range("A1")).Column` you should probably fully qualify the Range... – Vincent G May 20 '19 at 11:28
  • To complement on @Rory comment, you can check [this answer](https://stackoverflow.com/questions/52168437/when-two-workbooks-are-opened-in-visual-basic-workbook-variables-do-not-get-ass/52168954#52168954). – Vincent G May 20 '19 at 11:31
  • @Rory you're right, I had it different before, but I tried different ways so I left that Workbooks.Open by accident. – otr_91 May 21 '19 at 09:22
  • @Rori There's a thing I dont understand, the funtion AbrirCarpeta opens the workbook "Formato CO-08a (rev.1).xlsx", and it should be Activated right? After that statement, I changed Workbooks.Open and wrote directly set wbSource=This workbook and Debug.Print (wbSource.Name). The Name returned is "Nuevo Hoja de cálculo de Microsoft Excel.xlsx", not "Formato CO-08a (rev.1).xlsx". What am I doing wrong? – otr_91 May 21 '19 at 11:27
  • Thank you @Vincent G, I note that :) – otr_91 May 21 '19 at 11:30
  • `ThisWorkbook` is the workbook containing the code, `ActiveWorkbook` is the active workbook. – Vincent G May 21 '19 at 12:36
  • @VincentG thank you, I changed my code a bit and seems it reads now the Sheets. – otr_91 May 29 '19 at 09:25

1 Answers1

0

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

otr_91
  • 11
  • 3