Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 400??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 400??
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) "Joel" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 400??
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 "Chip Pearson" wrote: 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) "Joel" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Form Err.Raise error not trapped by entry procedure error handler | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |