Error 400??

J

Joel

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
 
C

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)
 
J

Joel

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
 

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