Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a file name within a called subroutine
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a file name within a called subroutine
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 "Matt" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call a subroutine using variable subroutine name | Excel Discussion (Misc queries) | |||
why is an Excel file called a workbook? | Excel Discussion (Misc queries) | |||
Event in a add-in called verytime a file is opened | Excel Programming | |||
Public subroutine called from a private sub | Excel Programming | |||
Subroutine call without file reference | Excel Programming |