Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 very puzzling indeeed
Hi, I am performing some simple collation. The code opens workbooks in a folder in turn, counts the number of rows in the open book and then copies them into the main book (with the macro) starting at the next row thus creating a master list of names from all the other books. Here's the puzzling problem (sure it's obvious but I've tried everything and the solution eludes me). Private Sub CommandButton2_Click() 'Opens Workbook to be tested placed in folder Call OpenAllWorkbooks("C:\Cleaned\") End Sub Public Sub OpenAllWorkbooks(sFolder As String) Dim sFile As String Dim s As String Dim a As String Dim iLastRow As Integer Dim iPasteRow As Integer sFile = Dir(sFolder & "*.xls") Do While sFile < "" Workbooks.Open sFolder & sFile 'CountRowsInSourceSheet (newly opened book will always be no 2 since after running it is closed iLastRow = Workbooks(2).Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row 'CountRowsInDestinationSheet iPasteRow = Workbooks(1).Sheets("MasterList").Cells(Rows.Count , "A").End(xlUp).Row + 1 etc etc The strange thing is that the second count works perfectly (iPasteRow is correct) yet the first jut refuses to behave. Any help much appreciated, regards, Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 very puzzling indeeed
First, I wouldn't use "As Integer", I'd use "as long". They hold bigger numbers. And I'm not sure what's going wrong, but I'd try: Public Sub OpenAllWorkbooks(sFolder As String) Dim sFile As String Dim s As String Dim a As String dim wkbk as workbook dim mstrWkbk as workbook Dim iLastRow As Long Dim iPasteRow As Long set mstrwkbk = activeworkbook sFile = Dir(sFolder & "*.xls") Do While sFile < "" set wkbk = Workbooks.Open(filename:=sFolder & sFile) 'CountRowsInSourceSheet with wkbk.sheets(1) iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row end with 'CountRowsInDestinationSheet with mstrwkbk.sheets("masterlist") iPasteRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 end with ... wkbk.close savechanges:=false 'or true??? mark Stephens wrote: Hi, I am performing some simple collation. The code opens workbooks in a folder in turn, counts the number of rows in the open book and then copies them into the main book (with the macro) starting at the next row thus creating a master list of names from all the other books. Here's the puzzling problem (sure it's obvious but I've tried everything and the solution eludes me). Private Sub CommandButton2_Click() 'Opens Workbook to be tested placed in folder Call OpenAllWorkbooks("C:\Cleaned\") End Sub Public Sub OpenAllWorkbooks(sFolder As String) Dim sFile As String Dim s As String Dim a As String Dim iLastRow As Integer Dim iPasteRow As Integer sFile = Dir(sFolder & "*.xls") Do While sFile < "" Workbooks.Open sFolder & sFile 'CountRowsInSourceSheet (newly opened book will always be no 2 since after running it is closed iLastRow = Workbooks(2).Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row 'CountRowsInDestinationSheet iPasteRow = Workbooks(1).Sheets("MasterList").Cells(Rows.Count , "A").End(xlUp).Row + 1 etc etc The strange thing is that the second count works perfectly (iPasteRow is correct) yet the first jut refuses to behave. Any help much appreciated, regards, Mark -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error when cell A1 is not active and xlInsideVertical border formatthrowing error 1004 | Excel Programming | |||
Error handling error # 1004 Run-time error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Puzzling Memory Error ! | Excel Programming |