Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I have a loop to open a set of workbooks get some data, close itone a time.
You can get a 1004 error if you try to open a workbook that doesn't exist. So
you could add a check for existance: Option Explicit Sub testme99() Dim i As Long Dim j As Long Dim wkbk As Workbook Dim fPathname As String For i = 1 To 5 fPathname = "c:\Myfile" & CStr(i) & ".xls" If Dir(fPathname) = "" Then MsgBox "missing file: " & fPathname Else 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 End If Next i End Sub I dimmed some more variables and changed the + to & in the fpathname line. (and added an L to the fpathname, too.) wellie wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to open, Update links, save and close workbooks | Excel Discussion (Misc queries) | |||
How to close all open workbooks in Excel 2007 | Setting up and Configuration of Excel | |||
Workbooks Close, but window stays open | Excel Discussion (Misc queries) | |||
How do I close multiple Excel '07 workbooks at the same time? | Excel Discussion (Misc queries) | |||
Open Close workbooks | Excel Discussion (Misc queries) |