Referencing a file name within a called subroutine

[Matt]

Hey all,

First off, thanks to Topper and Norman, I've gotten this code working,
now I need to make some tweaks.

Here's the code:

========================================================================

Option Explicit

Sub OpenFiles()

Dim RawFileToOpen
Dim CriticalToOpen
Dim CriticalLastRow As Long
Dim CriticalRange As Range
Dim CriticalCode As Range

'Open the raw file
RawFileToOpen = Application.GetOpenFilename("Text Files
(*.txt),*.txt", , "Select Raw File", , False)

'Import the raw file
Workbooks.OpenText Filename:=RawFileToOpen, _
StartRow:=5, _
DataType:=xlDelimited, _
Other:=True, _
OtherChar:="|", _
FieldInfo:=Array( _
Array(1, 1), Array(2, 2), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 5), Array(12, 9),
Array(13, 1), Array(14, 1), Array(15, 9), Array(16, 9))
Columns("A:N").EntireColumn.AutoFit

'Open the critical file
CriticalToOpen = Application.GetOpenFilename("Worksheets (*.xls),
*.xls", , "Select Critical File", , False)

Workbooks.OpenText Filename:=CriticalToOpen
Range("A1").Activate
CriticalLastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Find and
set last row
Set CriticalRange = Worksheets(1).Range("A2:A" & CriticalLastRow)

For Each CriticalCode In CriticalRange
If Not IsEmpty(CriticalCode) Then
Call FilterGroups(CriticalCode.Value)
End If
Next CriticalCode

MsgBox "Search Complete"

End Sub

Sub FilterGroups(SearchCode)

Dim LastRow As Long 'Last row of dataset
Dim SearchRange As Range 'Search range
Dim n As Long 'Current loop step
Dim c '
Dim FirstAddress As String '
Dim StoreRows() As Long '
Dim Check As Integer 'Check column
Dim i As Integer 'Incremental for retrieving stored rows
Dim r As Integer 'Current row
Dim bLevel As Integer 'Level of found code

Workbooks("Raw.txt").Activate

LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Find and set last
row

Set SearchRange = Worksheets(1).Range("B2:B" & LastRow) 'Set search
range

n = 0 'Set n variable to zero

'Search for all occurences of SearchCode and store row numbers
With SearchRange
Set c = .Find(SearchCode, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
n = n + 1
ReDim Preserve StoreRows(n)
StoreRows(n) = c.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

Check = 14 'Check column number

'For each occurence of "SearchCode" mark rows with "x" in "Check"
For i = 1 To n
r = StoreRows(i)
Cells(r, Check) = "x"
bLevel = Cells(r, 1) 'Store level of "SearchCode"
r = r + 1
Do While Cells(r, 1) > bLevel 'Add "x" if level > than level of
"SearchCode"
Cells(r, Check) = "x"
r = r + 1
Loop
Next

End Sub
========================================================================

The issue I'm having is in the called FilterGroups subroutine. I want
to reference the workbook declared by CriticalToOpen, rather than using
the file name "Raw.txt".

I tried using:

Workbooks(RawFileToOpen).Activate but I guess called functions don't
inherit the declarations from the parent routine. Please bear with me,
it's been a long time since I've use VBA.

Anyway, I'm sure there's some simple modification I need to make to the
code, but everything I've tried hasn't worked. Any help would, as
always, be greatly appreciated.

Thanks!

- Matt

 

[Greg Wilson]

You can pass the workbook to the called procedure the same as you are doing
already with the SearchCode string. Give the called procedure a second
argument, in this case of type Workbook. Pass the identity of the workbook to
the called procedure. A simple example follows:

Sub xxx()
Dim fn As String
Dim txt As String
Dim wb As Workbook
txt = Range("A1").Value
fn = Application.GetOpenFilename("Excel Files(*.xls), *xls", _
Title:="Open File")
Set wb = Workbooks.Open(fn)
Call yyy(txt, wb)
End Sub

Sub yyy(SearchCode As String, wkbk As Workbook)
Dim r As Range
Set r = wkbk.Sheets("Sheet1").UsedRange.Find(SearchCode)
If Not r Is Nothing Then MsgBox r.Address
End Sub

Regards,
Greg