VBA & POWERSHELL: THE TECHNIQUE TO EXTRACT A SPECIFIC STRING FROM MODULES, CLASSES OR SQL OF QUERIES IN ACCESS DATABASE

  1'******************************************************************************************
  2'*Function      :output codes of module and class, and query source SQLs
  3'******************************************************************************************
  4Sub exportCodesSQLs()
  5    
  6    'Consts
  7    Const FUNC_NAME As String = "exportCodesSQLs"
  8    
  9    'Vars
 10    Dim outputDir As String
 11    Dim vbcmp As Object
 12    Dim fileName As String
 13    Dim ext As String
 14    Dim qry As QueryDef
 15    Dim qName As String
 16    
 17    
 18    
 19    On Error GoTo ErrorHandler
 20    
 21    outputDir = _
 22        Access.CurrentProject.Path & _
 23        "\" & _
 24        "src_" & _
 25        Left(Access.CurrentProject.Name, InStrRev(Access.CurrentProject.Name, ".") - 1)
 26    If Dir(outputDir, vbDirectory) = "" Then MkDir outputDir
 27    
 28    'output modules, classes
 29    For Each vbcmp In VBE.ActiveVBProject.VBComponents
 30        With vbcmp
 31            'set extension
 32            Select Case .Type
 33            Case 1
 34                ext = ".bas"
 35            Case 2, 100
 36                ext = ".cls"
 37            Case 3
 38                ext = ".frm"
 39            End Select
 40                        
 41            fileName = .Name & ext
 42            fileName = gainStrNameSafe(fileName) 'replace some charactors which aren't allowed to use for a file name.
 43            If fileName = "" Then GoTo ExitHandler
 44            
 45            'output
 46            .Export outputDir & "\" & fileName
 47            
 48        End With
 49    Next vbcmp
 50    
 51    'output query sources
 52    With CreateObject("Scripting.FileSystemObject")
 53        For Each qry In CurrentDb.QueryDefs
 54            Do
 55                qName = gainStrNameSafe(qry.Name) 'replace some charactors which aren't allowed to use for a file name
 56                If qName = "" Then GoTo ExitHandler
 57                
 58                If qName Like "Msys*" Then Exit Do 'exclude queries related MS system
 59                
 60                With .CreateTextFile(outputDir & "\" & qName & ".sql")
 61                    .write qry.SQL
 62                    .Close
 63                End With
 64            Loop While False
 65        Next qry
 66    End With
 67
 68ExitHandler:
 69
 70    Exit Sub
 71    
 72ErrorHandler:
 73
 74    MsgBox "An error has occurred and the macro will be terminated." & _
 75           vbLf & _
 76           "Func Name:" & FUNC_NAME & _
 77           vbLf & _
 78           "Error No." & Err.Number & vbNewLine & _
 79           Err.Description, vbCritical, "Macro"
 80        
 81    GoTo ExitHandler
 82        
 83End Sub
 84
 85
 86
 87
 88'******************************************************************************************
 89'*Function      :replace some charactors with a underscore and return replaced string. The charactors aren't allowed to use for a file name.
 90'*Arg           :target string
 91'*Return        :replaced  string
 92'******************************************************************************************
 93Public Function gainStrNameSafe(ByVal s As String) As String
 94    
 95    'Consts
 96    Const FUNC_NAME As String = "gainStrNameSafe"
 97    
 98    'Vars
 99    Dim x As Variant
100    
101    On Error GoTo ErrorHandler
102
103    gainStrNameSafe = ""
104    
105    For Each x In Split("\,/,:,*,?,"",<,>,|", ",") 'array of chars not to be used
106        s = Replace(s, x, "_")
107    Next x
108    
109    gainStrNameSafe = s
110
111ExitHandler:
112
113    Exit Function
114    
115ErrorHandler:
116
117    MsgBox "An error has occurred and the macro will be terminated." & _
118           vbLf & _
119           "Func Name:" & FUNC_NAME & _
120           vbLf & _
121           "Error No." & Err.Number & vbNewLine & _
122           Err.Description, vbCritical, "Macro"
123        
124    GoTo ExitHandler
125        
126End Function

1Get-ChildItem | ForEach-Object{ Write-Output  ($_.Name + "`r`n------") ; (Get-Content $_   | Select-String "here you write a string to be searched"  )  | ForEach-Object{Write-Output ($_.lineNumber.Tostring() + ":" + $_) } ;Write-Output "------"  } 

1Get-ChildItem | ForEach-Object{ Write-Output  ($_.Name + "`r`n------") ; (Get-Content $_   | Select-String "ITEM_CODE"  )  | ForEach-Object{Write-Output ($_.lineNumber.Tostring() + ":" + $_) } ;Write-Output "------"  }