个人中心
gpt4 book ai didi

excel - 使用 .bat 文件和任务调度程序执行 .vbs 文件

转载 作者：可可西里 更新时间：2023-11-01 11:23:12 26 4
gpt4 key购买 nike

<分区>

我下面的代码运行正常。

1.) 谁能帮我知道如何使用任务调度器来调度这个 VBS 文件？

2.) 我还想了解编写用于执行此脚本的 .bat 文件。

请看下面的代码:

    Macro1
Private Sub Macro1()
Set objExcel  = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\kursekar\Documents\Work\Dailytasks\January\ReferralStrApp\StdztnRefRepTrial.xlsx")
objExcel.Visible = True
Dim Conn
Dim RS
Dim SQL
SQL = "WITH cte_REFERRALS_REPORTS(referralnum, refer_from, refer_from_name, refer_from_id, refer_to, refer_to_name, refer_to_id) AS (SELECT referralnum, refer_from, CASE WHEN refer_from_id = 'R' THEN RdicF.refname WHEN refer_from_id = 'P' THEN PdicF.provname END AS refer_from_name, refer_from_id, refer_to, "
SQL = SQL & "CASE WHEN refer_to_id = 'R' THEN RdicT.refname WHEN refer_to_id = 'P' THEN PdicT.provname END AS refer_to_name, refer_to_id FROM referral_t r Left Join refcode_t RdicF ON  r.refer_from = CASE WHEN r.refer_from_id='R' THEN RdicF.refcode ELSE NULL END Left Join refcode_t RdicT ON  r.refer_to = CASE WHEN r.refer_to_id = 'R' THEN RdicT.refcode ELSE NULL END "
SQL = SQL & "Left Join provcode_t PdicF ON r.refer_from  = CASE WHEN r.refer_from_id = 'P' THEN PdicF.provcode ELSE NULL END Left Join provcode_t PdicT ON r.refer_to = CASE WHEN r.refer_to_id = 'P' THEN PdicT.provcode ELSE NULL END ) SELECT chgslipno , a.acctno, patfname, patlname, appt_date, a.enccode, pr.provname "
SQL = SQL & ",a.provcode, rfc.refname, a.refcode, r1.refer_from as r1_ref_from, r1.refer_from_id as r1_ref_from_id, r1.refer_from_name as r1_ref_from_name, a.referral1 as r1_refnum, r2.refer_from as r2_ref_from, r2.refer_from_id as r2_ref_from_id, r2.refer_from_name as r2_ref_from_name,a.referral2, prgrc.provgrpdesc,s.specdesc, a.prov_dept, pos.posdesc,pr.cred "
SQL = SQL & "FROM apptmt_t a Left JOIN patdemo_t p ON a.acctno = p.acctno LEFT JOIN provcode_t pr ON pr.provcode = a.provcode LEFT JOIN refcode_t rfc ON a.refcode = rfc.refcode LEFT JOIN (SELECT*FROM cte_REFERRALS_REPORTS) r1 ON a.referral1 = r1.referralnum LEFT JOIN (SELECT*FROM cte_REFERRALS_REPORTS) r2 "
SQL = SQL & "on a.referral2 = r2.referralnum LEFT JOIN provgrpprov_t prgrpr on a.provcode = prgrpr.provcode LEFT JOIN provgrpcode_t prgrc on prgrpr.provgrpcode = prgrc.provgrpcode LEFT JOIN specialty_t s on pr.speccode = s.speccode LEFT JOIN poscode_t pos on a.poscode = pos.poscode "
SQL = SQL & "WHERE UPPER(a.enccode) in ('CON','APE','COB','CONZ','HAC','HFUI','MMN','NCG','NCHF','NCPF','NHFU','NMC','NOB','NP','NP15','NPE','NPI','NPOV','NPS','NPSV','NPV','OVN','IMC','NP30') AND UPPER(a.appt_status)='ARR' AND appt_date >= '2017-01-01' "
SQL = SQL & "ORDER BY a.acctno"
Set Conn = CreateObject("ADODB.Connection")
Conn.Open = "Provider=SQLOLEDB.1;Password='25LaurelRoad';User ID='CPSMDIT\kursekar';Data Source='analyzer';Initial Catalog='analyzer_str';Integrated Security=SSPI; Persist Security Info=True;"
Set RS = Conn.Execute(SQL)
Set Sheet = objWorkbook.ActiveSheet
Sheet.Activate
Dim R
R = 2
While RS.EOF = False
  Sheet.Cells(R, 1).Value = RS.Fields(0)
  Sheet.Cells(R, 2).Value = RS.Fields(1)
  Sheet.Cells(R, 3).Value = RS.Fields(2)
  Sheet.Cells(R, 4).Value = RS.Fields(3)
  Sheet.Cells(R, 5).Value = RS.Fields(4)
  Sheet.Cells(R, 6).Value = RS.Fields(5)
  Sheet.Cells(R, 7).Value = RS.Fields(6)
  Sheet.Cells(R, 8).Value = RS.Fields(7)
  Sheet.Cells(R, 9).Value = RS.Fields(8)
  Sheet.Cells(R, 10).Value = RS.Fields(9)
  Sheet.Cells(R, 11).Value = RS.Fields(10)
  Sheet.Cells(R, 12).Value = RS.Fields(11)
  Sheet.Cells(R, 13).Value = RS.Fields(12)
  Sheet.Cells(R, 14).Value = RS.Fields(13)
  Sheet.Cells(R, 15).Value = RS.Fields(14)
  Sheet.Cells(R, 16).Value = RS.Fields(15)
  Sheet.Cells(R, 17).Value = RS.Fields(16)
  Sheet.Cells(R, 18).Value = RS.Fields(17)
  Sheet.Cells(R, 19).Value = RS.Fields(18)
  Sheet.Cells(R, 20).Value = RS.Fields(19)
  Sheet.Cells(R, 21).Value = RS.Fields(20)
  Sheet.Cells(R, 22).Value = RS.Fields(21)
  Sheet.Cells(R, 23).Value = RS.Fields(22)
  RS.MoveNext
  R = R + 1
Wend
RS.Close
Conn.Close
Application.DisplayAlerts = False
'Release memory
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
ActiveWorkbook.Save
'objWorkbook.SaveAs Filename:="C:\\Users\kursekar\Documents\Work\Dailytasks\January\ReferralStrApp\StdztnRefRepTrial.xlsx", FileFormat:=51
Application.DisplayAlerts = True
objWorkbook.Close
objExcel.Workbooks.Close
objExcel.Quit
Workbooks.Close
Set objExcel = Nothing
MsgBox ("Saved")
End Sub

谢谢你们提前!!!请随时输入。我对 VB 完全陌生。

26 4 0
文章推荐： windows - 如何读取 .txt 文件中的文本及其在 powershell 中的每个值？
文章推荐： memory - Redis 哈希结构并没有像描述的那样提高内存效率
文章推荐： windows - .bat 文件中的管道分隔符
文章推荐： c - 如何捕获数据包(NPF； WinPcap)？
可可西里
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com