How to switch to next window and back

[Guest]

I have recorded a macro which works fine but need to change, in effect, one
code line.

What I have right now:
Is a working macro I got Excel [2003] to record, except for the Windows(
"<name>").Activate command which has a specific name. Because the name
varies, it needs to move to the 'next' xls window.

Macro:
Sub GetData()
'
' GetData Macro
' Macro recorded 28/11/2006 by Me
'

' Windows( _

"HighSpeed_Service_12756864-HighSpeed_20G(15_Nov_2006-14_Dec_2006)-1.csv"). _
Activate

Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
'
Windows("My Internet Traffic.xls").Activate
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub


The macro works perfectly, except next time I get the data (obviously it
contains my internet traffic usage ;-} ) the name of the csv file will be
different so I either need to edit the macro before running it ... or do it
manually which would turn out quicker in that case. But I want to learn VB
macros.

Is there a way to move to the next Window in VB (a replacement for the
Windows( "HighSpeed_Service_etc etc.csv").Activate command) before the Range
thru Selection.Copy commands. I was hoping for something like Windows(
+1).Activate but no such luck & I can't find any documentation on the
Windows(xx) command. Of course then being able to go back one to the
previous active window would be useful too.

Which probably raises another question ... any useful website documenting
the VB command in excel?

 

[NickHK]

It looks like you would be better of working with Workbook objects instead
of Windows.

Assuming this code will reside in your file "My Internet Traffic.xls", you
can allow yourself/the user to select the .csv file of choice, so the actual
name does not matter. But you will need to know the worksheet name (or index
number).

dim Filename as string
Dim SourceWB as workbook

filename=application.getopenfilename()
set sourcewb=workbooks.open(filename)

with sourcewb.worksheets("Whichever")
.Range("A2", .Range("A2").SpecialCells(xlLastCell)).Copy
end with

Thisworkbook.worksheets("WhicheverHere").Range("A5").PasteSpecial
Paste:=xlPasteValues

Note that you do not (normally) have to .Select objects to work with in
Excel.
Check the help for GetOpenFilename to see how to limit the choices to .csv
files and check a valid filename was supplied..

NickHK