0

I am trying to convert a VBA code to VB Script so that it can be run from a file on the desktop. I am trying to automate a task which is done manually everyday. There is a MS Access form to which I would have to login and then click on a button which generates an Excel file as output.

Following are my questions.

  1. Can we put the username and password in the VB Script so that it does not prompt for password while executing the Script ?
  2. Can the Excel output be saved to a location and renamed with date ? Example - PODueReport01022014.xls

The VBA code is as follows.

    Public Function PODueReport() As ADODB.Recordset
      Dim strSQL As String, strFrom As String, strWhere As String

strSQL = "Select distinct case when pp.NRCCorDirect='NRSC' then cm.requested_date-27-vert.LeadTime ELSE cm.requested_date-6-vert.LeadTime END AS [PO Due Date]"
strSQL = strSQL & ",cm.ID AS ChangeNumber"
strSQL = strSQL & ",cm.title as [Project Title]"
strSQL = strSQL & ",cm.requested_date as [Set Date]"
strSQL = strSQL & ",trex.RetrofitLead AS [Project Lead]"
strSQL = strSQL & ",bgt.ProjectCode as [Project Code (Target)]"
strSQL = strSQL & ",bgt.ProjectCode2 as [Project Code (Vendor)]"
strSQL = strSQL & ",pp.ExistingPartNum as [Part #]"
strSQL = strSQL & ",pp.ItemDesc as [Fixture Description]"
strSQL = strSQL & ",pp.SOURCINGSPECIALIST as [Sourcing Specialist]"
strSQL = strSQL & ",vert.Vendor"
strSQL = strSQL & ",vert.EarlyCommitQty"
strSQL = strSQL & ",vert.CommitDate"
strSQL = strSQL & ",vert.ACTUALPOCUTDATE As [PO Cut Date]"
strSQL = strSQL & ",case when bgt.ProjectCode is null and bgt.ProjectCode2 is null and trex.ActProjApprovedDate is null then 'No project code; Project Code Assigned box not checked' when trex.ActProjApprovedDate is null then 'Project Code Assigned box not checked' else 'No project code' end as [Type Of Issue] "
strFrom = "Select distinct perfect_placement_id, vendor, leadtime, EarlyCommitQty, CommitDate, PONUM, ACTUALPOCUTDATE "
strFrom = strFrom & "From CSD.dbo.TBLREVIEWQUOTEVERTICAL "
strFrom = strFrom & "where not QUOTEAPPROVALDATE is null "  'filter 2. quote approved
strFrom = strFrom & "and (ponum is null or ponum=0) "   'filter 4. no PO or PO=0
strFrom = strFrom & "and not Perfect_Placement_ID is null and Perfect_Placement_ID <> ''"
strFrom = strFrom & "and not Vendor is null and Vendor <> ''"
strFrom = "(" & strFrom & ") vert left join CSD.dbo.TBLPERFECTPLACEMENTLOCAL pp on pp.Perfect_Placement_Id = vert.Perfect_Placement_Id"
strFrom = "(" & strFrom & ") left join CSD.dbo.Retro_tblChangeRequests_Local trex on trex.Changenumber=pp.changenumber"
strFrom = "(" & strFrom & ") left join CSD.dbo.TBLBUDGETS bgt on trex.Changenumber=bgt.changenumber"
strFrom = "(" & strFrom & ") left join CSD.dbo.PRCRM_DET_W cm on cm.change_request_id=trex.PrologRecordID"
strWhere = "cm.status = 'go' "
strWhere = strWhere & " and case when pp.NRCCorDirect='NRSC' then cm.requested_date-27-vert.LeadTime ELSE cm.requested_date-6-vert.LeadTime END >='" & Date - 150 & "'" 'filter 3. PO Due date between today -150 and today +30
strWhere = strWhere & " and case when pp.NRCCorDirect='NRSC' then cm.requested_date-27-vert.LeadTime ELSE cm.requested_date-6-vert.LeadTime END <='" & Date + 30 & "'"
strWhere = strWhere & " and ((bgt.ProjectCode is null and bgt.ProjectCode2 is null) OR trex.ActProjApprovedDate is null)"
strWhere = strWhere & " and not pp.changenumber is null and pp.changenumber <> ''"
strWhere = strWhere & " and not pp.Perfect_Placement_ID is null and pp.Perfect_Placement_ID <> ''"
strWhere = strWhere & " and not pp.ExistingPartNum is null and pp.ExistingPartNum <> ''"
strWhere = strWhere & " and trex.DispositionNeeded <> 1"
strSQL = strSQL & " From " & strFrom & " Where " & strWhere
strSQL = strSQL & " Order by case when pp.NRCCorDirect='NRSC' then cm.requested_date-27-vert.LeadTime ELSE cm.requested_date-6-vert.LeadTime END, cm.ID, pp.ExistingPartNum"
Set PODueReport = SQLSet(1, strSQL, True)
    End Function

Any help or thoughts on solving the problem is highly appreciated.

Improve this question

1 Answer 1

Reset to default
0

You may be best off making it so that the VBS file Opens the Database, and Execute the Function above. Since you want a specific filename you may be best getting that information in to the function in the VBA above.

Check out this answer to get you started.

Basically what you learn from that answer is that you need to create an "Access.Application" object in your VBS. Once you do that you have access to all of the Methods and Properties that you would have to access in VBA normally.

When you review the answer that I linked and the following two Methods more closely you will begin to understand that it is possible for you to open the database with a password and then run the functions or macros that you would like to execute.

The VBS file that you create may be executed at any time.

If you wish to add the VBS file to windows task scheduler it is usually best to do so with the c:\windows\syswow64\cscript.exe application and the full path to your .VBS file as an argument for the application as you are building the task.

Application.Run Method (Access)

Application.OpenCurrentDatabase Method (Access)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.