Error 400??

[Guest]

TIA:

I run the following code and traced it to the bottom line that produces
Error 400 ?? What's the problem?? I have additional code to run on the
opened file but I not getting to it...

Thanks,
Joel


Sub OpnFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\") 'change directory
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name

'do whatever
Worksheets(1).Activate
Range("A2").Select
............more code and end sub

Thanks, Joel

 

[Chip Pearson]

Joel,

Where is the code located? Is it in a standard code module or is it within
one of the Sheet modules?

When code is in a normal code module, as statement like Range("A2").Select
"rolls up" to become

ActiveWorkbook.ActiveSheet.Range("A2").Select

However, of the code is in a Sheet module, a statement like
Range("A2").Select refers to Range("A2") of the sheet whose module contains
the code, regardless of what sheet is active. Thus, if your code is in the
code module for Worksheets(2), code like

Worksheets(1).Activate
Range("A2").Select

will fail because Worksheets(1) is active, but Range("A2") refers to
Range("A2") of the sheet whose code module contain the code. You can't
Select a cell on a sheet that is not active, so you'll get an error.

You can remedy this with code like

Worksheets(2).Activate
ActiveSheet.Range("A1").Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

Chip,
you are correct, the code was at a sheet moduled in stead of a standard
module, when I ran the code in teh standard module it was fine,

Thanks,
Joel