Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Closing Workbooks
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Closing Workbooks
Resolved · Medium Priority · Version 2003
Reply Mon 7 Sep 2009, 15:08Delegate 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?
For upcoming training course dates see: Pricing & availability
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
Reply Tue 8 Sep 2009, 09:13Delegate 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
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.
|