CODEWITHSUNDEEP
excelvba
user11187837
user11187837

Reputation:

Vba error when I am trying to set a workbook - Excel

I have this code , but is throwing me an error when I am trying to set a workbook. I use this because I am trying to copy values from other workbooks, but when I set the worbooks with a variable appears and error.

I copy the code and also the screenshot of the error.

Sub AbrirArchivos()

'Paso 1: Declarar las variables

 Dim Archivos As String
 Dim vals As Variant
 Dim wbcopy As Workbook
Dim wbTarget As Workbook

Set wbTarget = ThisWorkbook


 Archivos = Dir("C:\Users\fernandofernandez\Desktop\Prueba\*.xlsx")
 Do While Archivos <> “”

 Workbooks.Open "C:\Users\fernandofernandez\Desktop\Prueba\" & Archivos
 Set wbcopy = Workbooks.Open "C:\Users\fernandofernandez\Desktop\Prueba\" & Archivos
'code

    vals = wbcopy.Worksheets(1).Range("E2").Value

    wbTarget.Range("C10").End(xlToRight).Select
    Selection.EntireColumn.Select
    Selection.Copy
    wbTarget.Range("C10").End(xlToRight).Offset(0, 1).Select
    Selection.EntireColumn.Select
    ActiveSheet.Paste

    wbTarget.Worksheets(1).Range("F11").Value = vals


 'Paso 5: Cuadro de mensaje, cerrar y guardar cambios

 'MsgBox ActiveWorkbook.Name

 ActiveWorkbook.Close SaveChanges:=True

'Paso 6: buscar más archivos en la carpeta para volver seguir la secuencia

 Archivos = Dir
 Loop

End Sub

i want enter image description here

Upvotes: 0

Views: 546

Answers (2)

Mikku
Mikku

Reputation: 6654

Use This line instead:

Set wbcopy = Workbooks.Open("C:\Users\fernandofernandez\Desktop\Prueba\" & Archivos)

This should solve the Error.

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881463

Do While Archivos <> “”

This is the first thing I'd be looking at. Languages are notoriously picky about using smart quotes where normal quotes are expected. It may be that VBA allows this but I'd be circumspect.

In addition, the docs have parentheses around the argument list and, from memory, that's a long held convention in VBA. Function calls have parentheses, procuderes calls do not.

I'd also examine the need for opening the workbook twice :-)

Upvotes: 1

Related Questions