Error Application.ScreenUpdating
 

Hi,
I am trying to get data from a collection of excel sheets. But when it
try ot execute. I get an error at

"Application.ScreenUpdating =false"
It says "Method or data member not found!"
Could someone please help me?

Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim iRow As Long
Dim oDetails As Workbook
Dim oSheet As Worksheet



Set oDetails = Workbooks.Add
Set oSheet = oDetails.Worksheets(1)
Application.ScreenUpdating =false
iRow = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\MyTest\")
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" &
objFile.Name
oSheet.Cells(iRow, 1).Value = objFolder.Path & "\" &
objFile.Name
oSheet.Cells(iRow, 2).Value =
ActiveWorkbook.Worksheets(1).Range("F45").Value
ActiveWorkbook.Close savechanges:=False
iRow = iRow + 1
End If
Next
Application.ScreenUpdating = True
End Function

Error Application.ScreenUpdating
 

That code worked just fine for me... You might want to try looking for
missing references or for issues in other procedures which keep the code from
compiling... (use Debug - Compile)
--
HTH...

Jim Thomlinson


"Sangeeta" wrote:

Hi,
I am trying to get data from a collection of excel sheets. But when it
try ot execute. I get an error at

"Application.ScreenUpdating =false"
It says "Method or data member not found!"
Could someone please help me?

Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim iRow As Long
Dim oDetails As Workbook
Dim oSheet As Worksheet



Set oDetails = Workbooks.Add
Set oSheet = oDetails.Worksheets(1)
Application.ScreenUpdating =false
iRow = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\MyTest\")
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" &
objFile.Name
oSheet.Cells(iRow, 1).Value = objFolder.Path & "\" &
objFile.Name
oSheet.Cells(iRow, 2).Value =
ActiveWorkbook.Worksheets(1).Range("F45").Value
ActiveWorkbook.Close savechanges:=False
iRow = iRow + 1
End If
Next
Application.ScreenUpdating = True
End Function

Error Application.ScreenUpdating
 

You must have some problem with this line because if VBA had read/compiled
it, "false" would be changed to "False"
Application.ScreenUpdating =false

May be there some characters at the start of the line that are neither
spaces nor tabs.

NickHK

"Sangeeta" wrote in message
oups.com...
Hi,
I am trying to get data from a collection of excel sheets. But when it
try ot execute. I get an error at

"Application.ScreenUpdating =false"
It says "Method or data member not found!"
Could someone please help me?

Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim iRow As Long
Dim oDetails As Workbook
Dim oSheet As Worksheet



Set oDetails = Workbooks.Add
Set oSheet = oDetails.Worksheets(1)
Application.ScreenUpdating =false
iRow = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\MyTest\")
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" &
objFile.Name
oSheet.Cells(iRow, 1).Value = objFolder.Path & "\" &
objFile.Name
oSheet.Cells(iRow, 2).Value =
ActiveWorkbook.Worksheets(1).Range("F45").Value
ActiveWorkbook.Close savechanges:=False
iRow = iRow + 1
End If
Next
Application.ScreenUpdating = True
End Function