Hi

After days of assiduous swiping codes from NG, I have come to a point where
Im able to run 2 macros in conjunction and get my job done without much of a
problem. Please see both of them at the end of my post.

The task Im doing here is to open a couple of workbooks from a particular
folder based on today's date ( excel files only). Then open a file IEX
format from some other folder. I copy some specific information from all the
excel files in "particular folder based on today's date " and paste it to
the target IEX format file.After pasting Im closing the excel files from
which Im copying information.This keeps on going with the help of a for
loop. Then I save the target IEX file with a new name "IEX" + Today's date.

I have a very small problem now. Wherever I have the statement "Windows("IEX
Format.xls").Activate" I get a runtime error '9' : Subscript out of range.

In the code below the statement at many places has been suppressed as far as
possible by inserting a apostrophe at the beginning of a statement and
making it as a Msgbox. My issue is that presently it is ok but when Im
running this code later it might be possible that I have some unconnected
excel files open on which the macro may operate if I dont specify
"Windows("IEX Format.xls").Activate" explicitly. ( It happened just now).

If I dont have any unconnected excel files open and I run the code with all
the statements "Windows("IEX Format.xls").Activate" being msgboxes then
everything is fine. The moment I remove the apostrophe from msgbox I get the
Runtime error mentioned above.

The first code is pasted in "This workbook" and second code is pasted in
"Module". ( I did not use any logic for why the first code pasted in
Workbook and second in module. Also, the first code is a sub and second code
is a function. Again I did not use any logic of why the second is a function
inspite of the fact that Im not apparently returning any value back to the
calling function. Im a little new in this VBA thing and was trying out
random combinations!!!!). Please note, the second code I got by recording a
Macro hence it looks very unwieldy.

Surprisingly, I get the runtime error only in the first code ( which is
defined as a sub and placed in this workbook). I dont get this runtime error
in the second code though it also has the same windows.activate statement.

Please tell me why the code is displaying a runtime error ( so that I learn
to avoid writing incorrect codes in future) and how to overcome the same (
so that Im able to do my present job!!).

Regards,
Hari
India


Sub OpenWorkbooksInLocation()

Application.ScreenUpdating = True
Dim i As Integer
Dim p As String

Workbooks.Open "C:\Documents and
Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls"
' Windows("IEX Format.xls").Activate
Range("A3:F3500").Select
Selection.Clear
Application.Goto Reference:="R1C1"
With Application.FileSearch
.NewSearch
.LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd")
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count

Set wb = Workbooks.Open(filename:=.FoundFiles(i))
p = .FoundFiles(i)
Call TransferIEXExceldata(p)
Next i
End With
Application.ScreenUpdating = True
Windows("IEX format").Activate
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs filename:= _
"C:\Documents and Settings\hprasadh\Desktop\Janice\Project
comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")),
FileFormat:=xlNormal
Application.DisplayAlerts = True


End Sub

Public Function TransferIEXExceldata(ByVal p As String)
'
' TransferIEXExceldata Macro
' Macro recorded 6/9/2004 by Hari Prasadh
'

'
Dim q As String
Windows("IEX Format.xls").Activate
Application.Goto Reference:="R1C1"
p = Right(p, 7)
Windows(p).Activate
Application.Goto Reference:="R1C1"
Selection.Copy
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A3"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(11, 1), Array(16, 1),
Array(20, 1), _
Array(24, 1), Array(28, 1)), TrailingMinusNumbers:=True
Range("D3").Select
Selection.Copy
Windows("IEX format.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 5).Range("A1").Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Windows(p).Activate
Application.Goto Reference:="R13C1"
Range("A13:E13").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("IEX format.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Range("A1").Select
Windows(p).Activate
Application.Goto Reference:="R3C4"
Application.CutCopyMode = False
Selection.Copy
Windows("IEX format.xls").Activate
ActiveSheet.Paste
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Windows(p).Activate
Rows("3:3").Select
Selection.Clear
Application.Goto Reference:="R1C1"
Workbooks(p).Close SaveChanges:=False
Windows("IEX format.xls").Activate

End Function

Hi
try
workbooks("IEXFormat.xls").Activate

--
Regards
Frank Kabel
Frankfurt, Germany


Hari wrote:
Hi

After days of assiduous swiping codes from NG, I have come to a point
where Im able to run 2 macros in conjunction and get my job done
without much of a problem. Please see both of them at the end of my
post.

The task Im doing here is to open a couple of workbooks from a
particular folder based on today's date ( excel files only). Then
open a file IEX format from some other folder. I copy some specific
information from all the excel files in "particular folder based on
today's date " and paste it to the target IEX format file.After
pasting Im closing the excel files from which Im copying
information.This keeps on going with the help of a for loop. Then I
save the target IEX file with a new name "IEX" + Today's date.

I have a very small problem now. Wherever I have the statement
"Windows("IEX Format.xls").Activate" I get a runtime error '9' :
Subscript out of range.

In the code below the statement at many places has been suppressed as
far as possible by inserting a apostrophe at the beginning of a
statement and making it as a Msgbox. My issue is that presently it is
ok but when Im running this code later it might be possible that I
have some unconnected excel files open on which the macro may operate
if I dont specify "Windows("IEX Format.xls").Activate" explicitly. (
It happened just now).

If I dont have any unconnected excel files open and I run the code
with all the statements "Windows("IEX Format.xls").Activate" being
msgboxes then everything is fine. The moment I remove the apostrophe
from msgbox I get the Runtime error mentioned above.

The first code is pasted in "This workbook" and second code is
pasted in "Module". ( I did not use any logic for why the first code
pasted in Workbook and second in module. Also, the first code is a
sub and second code is a function. Again I did not use any logic of
why the second is a function inspite of the fact that Im not
apparently returning any value back to the calling function. Im a
little new in this VBA thing and was trying out random
combinations!!!!). Please note, the second code I got by recording a
Macro hence it looks very unwieldy.

Surprisingly, I get the runtime error only in the first code ( which
is defined as a sub and placed in this workbook). I dont get this
runtime error in the second code though it also has the same
windows.activate statement.

Please tell me why the code is displaying a runtime error ( so that I
learn to avoid writing incorrect codes in future) and how to overcome
the same ( so that Im able to do my present job!!).

Regards,
Hari
India


Sub OpenWorkbooksInLocation()

Application.ScreenUpdating = True
Dim i As Integer
Dim p As String

Workbooks.Open "C:\Documents and
Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls"
' Windows("IEX Format.xls").Activate
Range("A3:F3500").Select
Selection.Clear
Application.Goto Reference:="R1C1"
With Application.FileSearch
.NewSearch
.LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now,
"yyyy-mm-dd")
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count

Set wb = Workbooks.Open(filename:=.FoundFiles(i))
p = .FoundFiles(i)
Call TransferIEXExceldata(p)
Next i
End With
Application.ScreenUpdating = True
Windows("IEX format").Activate
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs filename:= _
"C:\Documents and Settings\hprasadh\Desktop\Janice\Project
comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")),
FileFormat:=xlNormal
Application.DisplayAlerts = True


End Sub

Public Function TransferIEXExceldata(ByVal p As String)
'
' TransferIEXExceldata Macro
' Macro recorded 6/9/2004 by Hari Prasadh
'

'
Dim q As String
Windows("IEX Format.xls").Activate
Application.Goto Reference:="R1C1"
p = Right(p, 7)
Windows(p).Activate
Application.Goto Reference:="R1C1"
Selection.Copy
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A3"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(11, 1),
Array(16, 1), Array(20, 1), _
Array(24, 1), Array(28, 1)), TrailingMinusNumbers:=True
Range("D3").Select
Selection.Copy
Windows("IEX format.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 5).Range("A1").Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Windows(p).Activate
Application.Goto Reference:="R13C1"
Range("A13:E13").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("IEX format.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Range("A1").Select
Windows(p).Activate
Application.Goto Reference:="R3C4"
Application.CutCopyMode = False
Selection.Copy
Windows("IEX format.xls").Activate
ActiveSheet.Paste
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Windows(p).Activate
Rows("3:3").Select
Selection.Clear
Application.Goto Reference:="R1C1"
Workbooks(p).Close SaveChanges:=False
Windows("IEX format.xls").Activate

End Function