Closing Workbooks

Resolved · Medium Priority · Version 2003

Mon 7 Sep 2009, 15:08 Delegate Tony said...

Tony has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Access Intermediate course
Access Advanced course

Closing Workbooks

I have a number of similar spreadsheets that need to be combined and am trying to write a macro that opens workbooks using a variable name (StrFileName) in a for loop. I have no problem opening the workbook using

Workbooks.Open FileName:=StrFileName

The problem I am having is returning to the workbook to copy a second set of data and finally closing the workbook at the end.

I have tried

Windows(StrFileName).activate to move between workbooks
and

StrFileName. close to try and close the workbook but neither work.

What do I need to do?

Mon 7 Sep 2009, 17:44 Trainer Stephen said...

RE: Closing Workbooks

Hi Tony

Thank you for your question

I am assuming that you are opening a workbook at a time, and copying the required data from each of those workbooks into a "consolidating workbook"

If this is the case then I suggest simply copying the cells directly using the following syntax

Workbooks("Consolidating Workbook").Sheets("Sheet Name").Cells(i,j) = workbooks(strFilename).sheets("Sheet Name").cells(i,j)

This avoids the need to activate a workbook, and greatly speeds up your code

Regards

Stephen

Tue 8 Sep 2009, 09:13 Delegate Tony said...

RE: Closing Workbooks

Thanks Steven.

I had a go with your line of code but encountered some problems. The line I ended up with was

Workbooks("COMBINED RETURN.XLS").Sheets("MSFI").RangeCells(RowNo + 1, 1), Cells(RowNo + 491, 10)= Workbooks(StrFileName).Sheets("MSFI").Range("a10:j500").

RowNo is a variable that counts the number of rows in the combined spreadsheet so that new data is entered into the next empty line.

This code did not work so I edited back for specific cells and worbook names and got a line that worked as follows:

Workbooks("COMBINED RETURN.XLS").Sheets("MSFI").Range("d5") = Workbooks("Test1.xls").Sheets("MSFI").Range("d12").

Workbook Test1.xls was open

As soon as I change Workbooks("Test1.xls")... to
Workbooks(strFileName)... i get 'Run-time error'9' Subscript out of range. The variable strFileName is setting correctly so I am bact to my original problem.

Regards Tony

Thu 17 Sep 2009, 14:07 Trainer Stephen said...

RE: Closing Workbooks

Hi Tony

Apologies for the delayed response. I have been away from the office for the last few weeks.

I have identified an error in the code you sent me.

Workbooks("COMBINED RETURN.XLS").Sheets("MSFI").RangeCells(RowNo + 1, 1), Cells(RowNo + 491, 10)= Workbooks(StrFileName).Sheets("MSFI").Range("a10:j500").

There should be a dot between the word range and cells, thus

Workbooks("COMBINED RETURN.XLS").Sheets("MSFI").Range.Cells(RowNo + 1, 1), Cells(RowNo + 491, 10)= Workbooks(StrFileName).Sheets("MSFI").Range("a10:j500").

If this doesn't resolve the problem please let me know and I will investigate further

Regards

Stephen

Thu 24 Sep 2009: Automatically marked as resolved.

Training courses