Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 102
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to open, Update links, save and close workbooks Anton Excel Discussion (Misc queries) 0 January 7th 10 04:18 AM
How to close all open workbooks in Excel 2007 Alojz Setting up and Configuration of Excel 0 September 17th 09 11:04 PM
Workbooks Close, but window stays open Mari Excel Discussion (Misc queries) 2 January 16th 08 10:00 PM
How do I close multiple Excel '07 workbooks at the same time? KYJ Excel Discussion (Misc queries) 1 April 19th 07 04:34 PM
Open Close workbooks bbc1 Excel Discussion (Misc queries) 2 August 28th 05 11:24 AM


All times are GMT +1. The time now is 04:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"