Can I have a loop to open a set of workbooks get some data, close it one a time.
I'm trying to have a loop to open a set of workbook one at
a time to sort and copy some data out and then close it. The following code ONLY works for the first workbook, it fails when it tries to open the 2nd workbook and prompted me "Run-time error '1004' Application-defined or object- defined error." Can someone please tell me what I need to do to have s.t. I can open this set of workbooks in a loop ? Thanks in advance for any assistance. dim wkbk as Workbook dim fPathname as String For i=1 to 5 fPathname = ("c:\Myfile"+cstr(i)+".xs" Set wkbk = Workbooks.Open(fPathname) j = 10 Do While wkbk.Sheets(1).Cells(j, 8).Value 0 j = j + 1 Loop MsgBox (fPathname + " contains " + CStr(j - 1) + " rows of data.") ' *** Copy data and other stuff here. Application.DisplayAlerts = False wkbk.Close fPathname Application.DisplayAlerts = True Set wkbk = Nothing Next i |
Can I have a loop to open a set of workbooks get some data, close it one a time.
Wellie,
I didn't test this out, but it looks like your setting your wkbk to nothing then trying to have it set to a workbook again without doing it... Try putting dim wkbk as Workbook dim fPathname as String inside your loop instead of before it Dan E "wellie" wrote in message ... I'm trying to have a loop to open a set of workbook one at a time to sort and copy some data out and then close it. The following code ONLY works for the first workbook, it fails when it tries to open the 2nd workbook and prompted me "Run-time error '1004' Application-defined or object- defined error." Can someone please tell me what I need to do to have s.t. I can open this set of workbooks in a loop ? Thanks in advance for any assistance. dim wkbk as Workbook dim fPathname as String For i=1 to 5 fPathname = ("c:\Myfile"+cstr(i)+".xs" Set wkbk = Workbooks.Open(fPathname) j = 10 Do While wkbk.Sheets(1).Cells(j, 8).Value 0 j = j + 1 Loop MsgBox (fPathname + " contains " + CStr(j - 1) + " rows of data.") ' *** Copy data and other stuff here. Application.DisplayAlerts = False wkbk.Close fPathname Application.DisplayAlerts = True Set wkbk = Nothing Next i |
All times are GMT +1. The time now is 02:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com