dbhagwat@gmail.com wrote:
> Dejan:
>
> Thanks for the script. Now, I want to flush the details from the
> database into all the excel files that need to be created per the
> number of users. I am not sure how to do this...Here is my code...
>
> Dim cn
> Dim rsUsers
> Dim rsDetails
> Dim sSQLStatement
>
> 'Declaration for file system objects
> Dim oFSO
> Dim sSourceFile
> Dim sDestinationFile
>
> 'Declaration for excel objects
> Dim xlApp
> Dim xlWb
> Dim xlWs
>
> 'Create an instance of Excel and add a workbook
> Set xlApp = CreateObject("Excel.Application")
> Set xlWb = xlApp.Workbooks.Add
> Set xlWs = xlWb.Worksheets("Sheet1")
>
> Set cn = CreateObject("ADODB.Connection")
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> function main()
>
> 'Set ADO connection properties.
>
> cn.Provider = "sqloledb"
> cn.Properties("Data Source").Value = "REI-HRSA-DB01\DEV_DB"
> cn.Properties("Initial Catalog").Value = "GEMS"
> cn.Properties("Integrated Security").Value = "SSPI"
> cn.Open
>
> Set rsUsers = CreateObject("ADODB.Recordset")
> sSQLStatement = ""
> sSQLStatement = "select distinct io.OrgCode +'_'+
> isnull(p.FirstName,'') + isnull(P.LastName,'')+'_' as FileName,
> isnull(p.FirstName,'') + isnull(P.LastName,'') as Name "
> sSQLStatement = sSQLStatement & "from granttermactuals gta "
> sSQLStatement = sSQLStatement & "join awards aw on gta.grantid =
> aw.grantid and gta.awardnumber = aw.awardnumber "
> sSQLStatement = sSQLStatement & "inner join grants gr on gr.grantid
> = aw.grantid and aw.awardnumber = gr.LastAwdNoCurrBP "
> sSQLStatement = sSQLStatement & "inner join
> internalorganizationsPrograms ip on ip.ProgramId = gr.ProgramId and
> ip.ProgramInstance = gr.ProgramInstance "
> sSQLStatement = sSQLStatement & "inner join internalorganizations io
> on io.InternalOrgId = ip.InternalOrgId and io.InternalOrgTypeCode = 4 "
> sSQLStatement = sSQLStatement & "join
> dbo.VW_GrantsUserRoleAssignments_DNRM vw on vw.grantId = aw.grantid
> and roleid = 6 "
> sSQLStatement = sSQLStatement & "Join People p on p.PersonId =
> vw.PersonId where termtypecode in (4,5) and budgetperiodstartdate >
> '10/01/2005' "
>
> 'msgbox sSQLStatement
>
> rsUsers.Open sSQLStatement, cn
>
> If Not rsUsers.EOF Then
>
> Do While Not rsUsers.EOF
>
> sSourceFile = "Y:\Monitor Conditions Export.xls"
> DTSGlobalVariables("FileName").Value = "Y:\Terms and
> Conditions\" & rsUsers("FileName") & Month(Now) & "-" & Day(Now) & "-"
> & Year(Now) & ".xls"
> sDestinationFile = DTSGlobalVariables("FileName").Value
> oFSO.CopyFile sSourceFile, sDestinationFile
>
> Set rsDetails = CreateObject("ADODB.Recordset")
>
> sSQLStatement = "SELECT TOP 5 GrantTermId, substring(aw.GrantNumber,
> 3, 10) GrantNumber, aw.BudgetPeriod, aw.revisionNumber, GranteeName,
> ls.shortDisplayValue, "
> sSQLStatement = sSQLStatement & "Case when gta.termtypecode = 4
> then 'Program Specific Condition' When gta.termtypecode = 5 then
> 'Grant Specific Condition' "
> sSQLStatement = sSQLStatement & "end as ConditionType,
> gta.DisplayText as ConditionText, DueDate, p.FirstName + P.LastName as
> Name from granttermactuals gta "
> sSQLStatement = sSQLStatement & "join awards aw on gta.grantid =
> aw.grantid and gta.awardnumber = aw.awardnumber "
> sSQLStatement = sSQLStatement & "join awardaddresses awaddr on
> aw.grantid = awaddr.grantid and aw.awardnumber = awaddr.awardnumber "
> sSQLStatement = sSQLStatement & "and awaddr.addresspurposecode = 4
> Join LookupState ls on ls.lookupcode = awaddr.statecode "
> sSQLStatement = sSQLStatement & "join
> dbo.VW_GrantsUserRoleAssignments_DNRM vw on vw.grantId = gta.grantid
> and roleid = 6 "
> sSQLStatement = sSQLStatement & "Join People p on p.PersonId =
> vw.PersonId "
> sSQLStatement = sSQLStatement & "where termtypecode in (4, 5) and
> budgetperiodstartdate > '10/01/2005' and "
> sSQLStatement = sSQLStatement & " isnull(p.FirstName,'') +
> isnull(P.LastName,'') = '" & rsUsers.Fields("Name") & "' "
>
> ' Display Excel and give user control of Excel's lifetime
> xlApp.Visible = True
> xlApp.UserControl = True
>
> ' Copy field names to the first row of the worksheet
> fldCount = rst.Fields.Count
> For iCol = 1 To fldCount
> xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
> Next
>
> ' Copy the recordset to the worksheet, starting in cell A2
> xlWs.Cells(2, 1).CopyFromRecordset rst
> 'Note: CopyFromRecordset will fail if the recordset contains
> an OLE object field or array data such as hierarchical recordsets
>
>
> ' Auto-fit the column widths and row heights
> xlApp.Selection.CurrentRegion.Columns.AutoFit
> xlApp.Selection.CurrentRegion.Rows.AutoFit
>
> ' Close ADO objects
> rst.Close
> cnt.Close
> Set rst = Nothing
> Set cnt = Nothing
>
> ' Release Excel references
> Set xlWs = Nothing
> Set xlWb = Nothing
>
> Set xlApp = Nothing
>
>
> 'msgbox sSQLStatement
>
>
> rsDetails.open sSQLStatement, cn
>
>
> rsUsers.MoveNext
> Loop
> End If
> ' Clean Up
> 'Set oFSO = Nothing
>
> Main = DTSTaskExecResult_Success
>
> end function
>
>
> In this code I want that for every user, a excel file should be created
> and the details for that user need to be populated and this should be
> done for all the other distinct users....Where am I going wrong... Can
> you help me out?
>
> Thanks in advance.
>
>
>
> Dejan Sarka wrote:
> > For a start - here is a quick VB Script for creating Excel files.
> >
> > Dim ExcelApp
> > Dim ExcelBook
> >
> > 'Create the app
> > Set ExcelApp = CreateObject("Excel.Application")
> > 'and make it visible
> > ExcelApp.Application.Visible = False
> >
> > 'Add a workbook, write something
> > Set ExcelBook = ExcelApp.Workbooks.Add
> > ExcelBook.Worksheets(1).Cells(1, 1).Value = "This is a test - visible
> > false!"
> >
> > 'Save and quit
> > ExcelBook.SaveAs "c:\test.xls"
> > ExcelApp.Application.Quit
> >
> > 'Clean-up
> > Set ExcelApp = Nothing
> > Set ExcelBook = Nothing
> >
> > --
> > Dejan Sarka
> > http://www.solidqualitylearning.com/blogs/
> >
> > <dbhagwat@gmail.com> wrote in message
> > news:1161624707.558215.211550@m73g2000cwd.googlegroups.com...
> > > Hi,
> > >