Notepad to xls

[gouthamr]

I need to wite a VBA macro to extract all the views in a query, The query is in a notepad.The views extracted must be placed in a xls.

Similarly i have around 200 queries to be done manually ...
Please provide me the possiblities

[Jan Karel Pieterse]

What do you mean by "a query"? And what by "In a notepad"?

Are these files which open in notepad if you doubleclick them?
What is in these files?

[gouthamr]

A notepad contains SQL code (example :Select a.name,b.number from View1 a, View2 b), I have mentioned this as a "query".

I have around 200 such SQL codes each present in different notepad's.

What i need is to " open each notepad search for the word named "view", copy that view name( with the above example i copy View1 and View2) to a xls.

Am doing it manually. Can this be automated through VBA macro.

[Kenneth Hobs]

You need to be more specific. An example text file and what you expect to get from it is needed. You can attach files to this forum. If the example is simple enough, just post the contents like this:
In Test.txt:
Other Stuff
Select * from Table1
View
Other Stuff

Get from Test.txt:
Select * from Table1

You can use the WorksheetFunction.Match to find a word in an array and return the index position. This example shows how to read your text file into an array.
[VBA]Sub test()
Dim s() As String, v
FileLoadToArray s(), "x:\FileReadWrite\csv\FirstLast.csv"
For Each v In s()
Debug.Print v
Next v
End Sub


'http://www.visualbasic.happycodings.com/Files_Directories_Drives/code54.html
'Purpose : Reads a file into a string array.
'Inputs : asLines() A string array (see Outputs)
' sFileName The path and file name of the file to open and read
'Outputs : Returns an empty string on success, else returns the error decription
' asLines(1 to NumLines) String array containing the file
'Notes : Usually used for text files, but will load any file type.


Function FileLoadToArray(ByRef asLines() As String, ByVal sFileName As String) As String
Dim iFileNum As Long, lFileLen As Long
Dim sBuffer As String

'Initialise Variables
On Error GoTo ErrFailed

'Open File
iFileNum = FreeFile
Open sFileName For Binary Access Read As #iFileNum
'Get the size of the file
lFileLen = LOF(iFileNum)
If lFileLen Then
'Create output buffer
sBuffer = String(lFileLen, " ")
'Read contents of file
Get iFileNum, 1, sBuffer
'Split the file contents
asLines = Split(sBuffer, vbNewLine)
End If

Close #iFileNum
'Return success
FileLoadToArray = ""

Exit Function

ErrFailed:
Debug.Assert False
Debug.Print Err.Description
FileLoadToArray = Err.Description
'Close file
If iFileNum Then
Close #iFileNum
End If
End Function[/VBA]