Solved: Referencing open workbooks

[cubbycub]

I have a small macro in which I want the user to select other workbooks in a directory these are then added to a worksheet called List. The file names are then opened one by one a cell is then checked for a flag. If the flag is set then rows are copied from the newly opened workbook to the workbook running the macro. The opened workbook is then closed and the next in the list is opened and so on...

What i would like to know is how do I reference the workbook that is running the macro the new file as it is opened becomes active.

is it a case of :

[vba]
workbooks(WBName).Worksheets(wks.name).cells(1,1).value=workbooks("Thiswork book").Worksheets(wks.name).cells(1,1).value
[/vba]

I think i'm aproaching this from the wrong angle so any help would be very much appreciated.

[vba]
Sub GetImportFilename()
Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim Filename As Variant

'Set up list of file filters
Finfo = "Excel Files (*.xls*),*.xls*," & _
"All Files (*.*),*.*"

FileQty = 1
Continue = 0

'Display *.xls* by default
FilterIndex = 1

'Set the dialog box caption
Title = "Select a File to Add"

'Get the filename
Filename = Application.GetOpenFilename(Finfo, FilterIndex, file)

'Handel return info dialog box
If Filename = False Then
MsgBox "No file was selected."
Else
'MsgBox "You selected " & Filename
Worksheets("List").Cells(FileQty, 1).Value = Filename
FileQty = FileQty + 1
End If

Do While Continue = 0

Select Case MsgBox("Do you wish to add another file?", 4, "Add File?")
Case vbYes
' Display *.* by default
FilterIndex = 1

' Set the dialog box caption
Title = "Select a File to Add"

' Get the filename
Filename = Application.GetOpenFilename(Finfo, FilterIndex, file)

' Handel return info dialog box
If Filename = False Then
MsgBox "No file was selected."
Else
'MsgBox "You selected " & Filename
Worksheets("List").Cells(FileQty, 1).Value = Filename
FileQty = FileQty + 1
End If

Case vbNo
Continue = 1
Application.DisplayAlerts = False
End Select
Loop
ListLastRow = Worksheets("List").Cells(65536, 1).End(xlUp).Row
For Workbook = 1 To ListLastRow

'***isolate filename
WorkBookName = Worksheets("list").Cells(Workbook, 1).Value
For Letter = Len(WorkBookName) To 1 Step -1
Character = Mid(WorkBookName, Letter, 1)
Select Case Character
Case "."
pos1 = Letter
Case "\"
pos2 = Letter
Letter = 1
End Select
Next Letter
WBName = Mid(WorkBookName, pos2 + 1, (pos1 - pos2) - 1)
WBNameExt = Mid(WorkBookName, pos2 + 1, (Len(WorkBookName) - pos2))

Workbooks.Open Filename:=WorkBookName

For Each wks In Worksheets
If InStr(UCase(wks.Name), UCase("Stg")) > 0 Then 'proceed if Stg is not part of the Sheet name

'select and copy from newly open workbook to this workbook
End If
Next wks
Application.ScreenUpdating = True
Application.EnableEvents = True 'reset screen flashing

'process it
ActiveWorkbook.Close
Next Workbook
End Sub
[/vba]

[Simon Lloyd]

At the top of your macro:
[VBA]
Dim MyBook as String
Mybook = Thisworkbook.Name
[/VBA]
Then refer to it as [VBA]Workbooks(MyBook).Activate'/select etc.[/VBA]

[cubbycub]

Thanks simon that worked !!

This isnt something i need to do, just for the sake of curiosity, but if i opened a third workbook how would i have referenced the first opened work book?

[Charlize]

I tend to use this type of declarations. ex :[VBA]Sub reference_workbooks()
Dim myworkbook1 As Workbook
Dim myworkbook2 As Workbook
Dim myworkbook3 As Workbook
Set myworkbook1 = Workbooks.Open("name of 1")
Set myworkbook2 = Workbooks.Open("name of 2")
Set myworkbook3 = Workbooks.Open("name of 3")
myworkbook3.Activate
myworkbook2.Activate
myworkbook1.Activate
End Sub[/VBA]Charlize

[cubbycub]

thanks for that i'll have a little play later on