cubbycub
03-07-2008, 07:50 AM
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 :
workbooks(WBName).Worksheets(wks.name).cells(1,1).value=workbooks("Thisworkbook").Worksheets(wks.name).cells(1,1).value
I think i'm aproaching this from the wrong angle so any help would be very much appreciated.
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
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 :
workbooks(WBName).Worksheets(wks.name).cells(1,1).value=workbooks("Thisworkbook").Worksheets(wks.name).cells(1,1).value
I think i'm aproaching this from the wrong angle so any help would be very much appreciated.
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