For all workbooks in a folder


C

Ctech

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
 
Ad

Advertisements

D

Dave Peterson

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
 
C

Ctech

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
 
Ad

Advertisements

D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top