ashok_theagarajan
Board Regular
- Joined
- Oct 4, 2005
- Messages
- 68
Public Sub DailyCE()
Dim brio1 As Object, fso1 As Object, ok1 As Boolean
Dim pth1 As String, pth2 As String, sFldr1 As String, Folder1 As String, Week1 As String, fldOb1 As Object, fils1 As Object
Dim f1 As Object
Folder1 = ' Give the name of the folder where the Brio File exists with the full file name
pth1 = Folder1
Set fso1 = CreateObject("Scripting.FileSystemObject")
ok1 = fso1.folderexists(Folder1)
Do While ok1 <> True
Folder1 = InputBox("The Folder does not exist. Please check the path and enter the complete path again")
If Folder1 = "" Then Exit Sub
ok1 = fso1.folderexists(Folder1)
Loop
Set fldOb1 = fso1.getfolder(Folder1)
Set fils1 = fldOb1.Files
If fils1.Count = 0 Then
MsgBox ("The Daily Files have not been dumped into the folder")
Exit Sub
End If
Set brio1 = CreateObject("BrioQuery.Application")
brio1.Visible = True
For Each f1 In fils1
pth2 = f1.Path
MsgBox pth2
MsgBox pth1
If pth2 = pth1 Then
ExtractBqyMetaDaily1 brio1, pth2
End If
Next
do_cleanup:
Set fils1 = Nothing
Set fldOb1 = Nothing
Set fso1 = Nothing
brio1.ActiveDocument.Close
brio1.Application.Quit
Set brio1 = Nothing
End Sub
Private Sub ExtractBqyMetaDaily1(brio As Object, sBqyFilename As String)
On Error GoTo err_ExtractBqyMeta
brio.Documents.Open (sBqyFilename)
brio.ActiveDocument.Sections("SQL Results").Copy
Workbooks.Open Filename:="C:\Documents and Settings\ashok_theagarajan\Desktop\Brio\DailyCE.xls"
Workbooks("DailyCE").Sheets("RawBase").Select
Windows("DailyCE.xls").Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").PasteSpecial
Sheets("Index").Select
Exit Sub
err_ExtractBqyMeta:
MsgBox Err.Description
Exit Sub
End Sub
Make sure that you add Brio Query from the Tools References for this to work.
Dim brio1 As Object, fso1 As Object, ok1 As Boolean
Dim pth1 As String, pth2 As String, sFldr1 As String, Folder1 As String, Week1 As String, fldOb1 As Object, fils1 As Object
Dim f1 As Object
Folder1 = ' Give the name of the folder where the Brio File exists with the full file name
pth1 = Folder1
Set fso1 = CreateObject("Scripting.FileSystemObject")
ok1 = fso1.folderexists(Folder1)
Do While ok1 <> True
Folder1 = InputBox("The Folder does not exist. Please check the path and enter the complete path again")
If Folder1 = "" Then Exit Sub
ok1 = fso1.folderexists(Folder1)
Loop
Set fldOb1 = fso1.getfolder(Folder1)
Set fils1 = fldOb1.Files
If fils1.Count = 0 Then
MsgBox ("The Daily Files have not been dumped into the folder")
Exit Sub
End If
Set brio1 = CreateObject("BrioQuery.Application")
brio1.Visible = True
For Each f1 In fils1
pth2 = f1.Path
MsgBox pth2
MsgBox pth1
If pth2 = pth1 Then
ExtractBqyMetaDaily1 brio1, pth2
End If
Next
do_cleanup:
Set fils1 = Nothing
Set fldOb1 = Nothing
Set fso1 = Nothing
brio1.ActiveDocument.Close
brio1.Application.Quit
Set brio1 = Nothing
End Sub
Private Sub ExtractBqyMetaDaily1(brio As Object, sBqyFilename As String)
On Error GoTo err_ExtractBqyMeta
brio.Documents.Open (sBqyFilename)
brio.ActiveDocument.Sections("SQL Results").Copy
Workbooks.Open Filename:="C:\Documents and Settings\ashok_theagarajan\Desktop\Brio\DailyCE.xls"
Workbooks("DailyCE").Sheets("RawBase").Select
Windows("DailyCE.xls").Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").PasteSpecial
Sheets("Index").Select
Exit Sub
err_ExtractBqyMeta:
MsgBox Err.Description
Exit Sub
End Sub
Make sure that you add Brio Query from the Tools References for this to work.