Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For all workbooks in a folder
Hi I have a macro which worked previously, however it seams to have som sort of problem. but I can't figure out whats causing it. Im getting this error: "Error 438 - Object doesn't support property o method" on this part of the macro Application.wbResults.Sheets(mSheet).Range(mRange) .Select All of the macro: Dim sFileBase As String Dim sFilename As String Sub Kkkemen() Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook Dim mRows As Long Dim mSheet As String Dim mCostCenter Dim mRange ' Application.ScreenUpdating = False ' Application.DisplayAlerts = False ' Application.EnableEvents = False Set wbCodeBook = ThisWorkbook ' Set active Cell Range("A4").Select mAddress = "X:\Data\OLAP\Budgets UK\Budgets - 2005\test" mRange = "C10" mSheet = "Sch 5" mCostCenter = "101" With Application.FileSearch .NewSearch 'Change path to suit .LookIn = mAddress & "\" .FileType = msoFileTypeExcelWorkbooks '.Filename = "Book*.xls" If .Execute 0 Then 'Workbooks in folder For lCount = 1 To .FoundFiles.Count 'Loop through all. 'Open Workbook x and Set a Workbook variable to it Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount) UpdateLinks:=0) '--------------- CODE HERE ------------------ ' If the Sheet exist then If SheetExists(mSheet, wbResults) Then ' Activate Workbook ' Application.wbCodeBook.Activate ' Cost center in Column A ' If Not mCostCenter Is Nothing Then ' ActiveCell = Application.wbResults.Sheets(mSheet).Range(mCostC nter) ' End If ' Copy Capital expenditure numbers Application.wbResults.Sheets(mSheet).Range(mRange) .Select ' Count the number of rows in the range mRows = Application.wbResults.Sheets(mSheet).Range(mRange) .Rows.Count Selection.Copy ' Activate and paste the workbook range to sheet Application.wbCodeBook.Activate ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlValues Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(0, -1).Select ' Set activeCell of next workbook ActiveCell.Offset(mRows, 0).Select ' Delete Copied area for memory Application.CutCopyMode = False End If '-------- END -- CODE HERE -- END ------------ ' Do not save changes in opened workbooks wbResults.Close SaveChanges:=False Next lCount End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True ' Close the UserForm Unload GetFromWorkbook End Sub '----------------------------------------------------------------- Function SheetExists(Sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing) On Error GoTo 0 End Function Private Sub cmd_Cancel_Click() Unload GetFromWorkbook End Su -- Ctec ----------------------------------------------------------------------- Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774 View this thread: http://www.excelforum.com/showthread.php?threadid=50629 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For all workbooks in a folder
You can only select a range on an activesheet.
But if all you're doing is copying, maybe you could use: Application.wbResults.Sheets(mSheet).Range(mRange) .copy instead of Application.wbResults.Sheets(mSheet).Range(mRange) .Select .... selection.copy Ctech wrote: Hi I have a macro which worked previously, however it seams to have some sort of problem. but I can't figure out whats causing it. Im getting this error: "Error 438 - Object doesn't support property or method" on this part of the macro : Application.wbResults.Sheets(mSheet).Range(mRange) .Select All of the macro: Dim sFileBase As String Dim sFilename As String Sub Kkkemen() Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook Dim mRows As Long Dim mSheet As String Dim mCostCenter Dim mRange ' Application.ScreenUpdating = False ' Application.DisplayAlerts = False ' Application.EnableEvents = False Set wbCodeBook = ThisWorkbook ' Set active Cell Range("A4").Select mAddress = "X:\Data\OLAP\Budgets UK\Budgets - 2005\test" mRange = "C10" mSheet = "Sch 5" mCostCenter = "101" With Application.FileSearch NewSearch 'Change path to suit LookIn = mAddress & "\" FileType = msoFileTypeExcelWorkbooks '.Filename = "Book*.xls" If .Execute 0 Then 'Workbooks in folder For lCount = 1 To .FoundFiles.Count 'Loop through all. 'Open Workbook x and Set a Workbook variable to it Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0) '--------------- CODE HERE ------------------ ' If the Sheet exist then If SheetExists(mSheet, wbResults) Then ' Activate Workbook ' Application.wbCodeBook.Activate ' Cost center in Column A ' If Not mCostCenter Is Nothing Then ' ActiveCell = Application.wbResults.Sheets(mSheet).Range(mCostCe nter) ' End If ' Copy Capital expenditure numbers Application.wbResults.Sheets(mSheet).Range(mRange) .Select ' Count the number of rows in the range mRows = Application.wbResults.Sheets(mSheet).Range(mRange) .Rows.Count Selection.Copy ' Activate and paste the workbook range to sheet Application.wbCodeBook.Activate ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(0, -1).Select ' Set activeCell of next workbook ActiveCell.Offset(mRows, 0).Select ' Delete Copied area for memory Application.CutCopyMode = False End If '-------- END -- CODE HERE -- END ------------ ' Do not save changes in opened workbooks wbResults.Close SaveChanges:=False Next lCount End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True ' Close the UserForm Unload GetFromWorkbook End Sub '----------------------------------------------------------------- Function SheetExists(Sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing) On Error GoTo 0 End Function Private Sub cmd_Cancel_Click() Unload GetFromWorkbook End Sub -- Ctech ------------------------------------------------------------------------ Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745 View this thread: http://www.excelforum.com/showthread...hreadid=506295 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For all workbooks in a folder
Application.wbCodeBook.Activate It seams that the code works if I take away the Application. bit in th code? Why is this? Am I missing a references tool in excel -- Ctec ----------------------------------------------------------------------- Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774 View this thread: http://www.excelforum.com/showthread.php?threadid=50629 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For all workbooks in a folder
Sorry, I didn't test it. But wbcodebook is already a workbook variable. That
means that it comes with lots of properties--in fact, it's parent is the application. It would be equivalent to: workbooks("test.xls").wks.range("a1") Wks already has a parent (the workbook it belongs to)--so you can't specify it again. == An ugly alternative: application.workbooks(wbcodebook.name).activate but why bother. Ctech wrote: Application.wbCodeBook.Activate It seams that the code works if I take away the Application. bit in the code? Why is this? Am I missing a references tool in excel? -- Ctech ------------------------------------------------------------------------ Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745 View this thread: http://www.excelforum.com/showthread...hreadid=506295 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Workbooks in a Folder One by One | Excel Programming | |||
Looping through workbooks in Folder | Excel Programming | |||
Workbooks within a Folder - B-Lists | Excel Programming | |||
Update WorkBooks in Folder | Excel Programming | |||
Update WorkBooks in Folder | Excel Programming |