trying to close a workbook in VBA

[Chris Davison]

I'm trying to close a workbook in VBA but it's not even starting the subroutine......

the workbook name is in cell A1, which changes, so I can't hard-code it's name :

<code>
Sub import_files()

sourceworkbook = range("A1").Value

'open source
Workbooks.Open Filename:=sourceworkbook

..... lots of copy paste stuff......

'close source workbook
Workbooks(sourceworkbook).close

End Sub
</code>

I get runtime error 9 : subscript out of range on the "Workbooks(sourceworkbook).close" line

if I omit the "Workbooks(sourceworkbook).close" everything works fine, but I'm left with that workbook sitting open, which needs to be closed

I know I'm missing something simple....

thanks
Chris

 

[Tom Urtis]

Does your "lots of copy and paste stuff" code put you on a different worksheet than the one you are on when the workbook opens and the sourceworkbook variable is declared? Try qualifying the declaration with the worksheet name parent to which the sourceworkbook variable in that A1 cell belongs, such as Worksheets("Sheet1").Range("A1").Value (or the VBA code name for the sheet instead of its tab name), and see if it helps.

 

[Chris Davison]

Hi Tom,

thanks for looking at this

yeah, it's parent is declared within a for-next loop :

<code>
Sub import_files()

For Each cell In Range(ThisWorkbook.Worksheets("lookups").Range("O3"), ThisWorkbook.Worksheets("lookups").Range("O65536").End(xlUp))

sourceworkbook = cell.Offset(0, -1).Value

'open source
Workbooks.Open Filename:=sourceworkbook

...lots of other stuff...

'close source workbook
Workbooks(sourceworkbook).Close


Next cell

End Sub
</code>

ta
Chris

 

[parry]

Hi Chris, I think the problem is that when you open the workbook you need the full path while closing you only need the book name so thats why theres an error. Create a workbook variable and work off that.

eg

Sub test()
Dim Source As String, Wb As Workbook
Source = "c:\Test1A1.xls"
Set Wb = Workbooks.Open(Source)
Workbooks(Wb.Name).Close

End Sub

 

[Chris Davison]

thanks both....

aaargh - I had the workbook close line in the wrong place

all fixed now and time for bed (!)


thanks a lot again

Chris