Microsoft Excel muaj ntau yam ntxwv thiab ib qho ntawm lawv tau tsim cov ntawv tshaj tawm. Koj tuaj yeem tsim cov ntawv sib tham sib tham kom yooj yim rau cov txheej txheem ntawm kev nkag mus rau lwm tus rau hauv phau ntawv ua haujlwm, thaum tseem siv cov ntawv tshaj tawm tsis siv neeg. Ob qho ntawm cov yam ntxwv no xav tau kev paub txaus ntawm Visual Basic. Cov kauj ruam los ua ob txoj haujlwm tau piav qhia hauv qab no.
Kauj ruam
Txoj Kev 1 ntawm 2: Tsim Cov Ntawv Sib Tham Sib Tham
Kauj Ruam 1. Txheeb xyuas qhov txheej txheem ntawm daim ntawv nthuav tawm
Cov txheej txheem ntawm daim ntawv yuav tsum tau ua kom lwm tus tuaj yeem nrhiav cov haujlwm xav tau los nkag rau cov ntaub ntawv.
Spreadsheet layouts tuaj yeem tso tawm kab rov tav lossis ntsug. Cov neeg siv feem ntau pom nws yooj yim los ua haujlwm nrog kev teeb tsa ntsug, tshwj xeeb tshaj yog tias daim ntawv nthuav tawm yuav luam tawm
Kauj Ruam 2. Tsim cov ntawv lo rau daim ntawv nthuav tawm
Sau ib daim ntawv lo rau saum toj ntawm txhua kab ntawv, thiab rau sab laug ntawm txhua lub xovtooj ntawm kab ntawv uas koj npaj yuav siv los ua cov ntaub ntawv nkag.
Kauj Ruam 3. Nias alt="Duab" thiab F11 tuav ua ke
Qhov tseem ceeb ua ke no yuav qhib Microsoft Visual Basic editor.
Kauj Ruam 4. Nyem ob npaug rau "Phau Ntawv Qhia Ua Haujlwm no" hauv "Project-VBA Project" pane ntawm sab saud
Lub qhov rai rau kev sau cov cai yuav tshwm nyob rau ntu tseem ceeb ntawm tus khoos phis tawj.
Kauj Ruam 5. Xaiv "Txheej Txheem" los ntawm Insert menu
Qhov Add Procedure dialog box yuav tshwm.
Kauj Ruam 6. Nkag mus rau lub npe txheej txheem hauv lub Npe Npe
Nkag mus rau lub npe muaj txiaj ntsig rau tus txheej txheem, xws li "SumExpenses" yog tias daim ntawv nthuav qhia yuav raug siv los tshaj qhia txog kev mus ncig. Nyem OK txhawm rau kaw lub thawv sib tham.
- Cov txheej txheem cov npe tsis tuaj yeem muaj qhov chaw, tab sis tuaj yeem siv tus cim (_) los hloov chaw.
- Tom qab qhov Add Procedure dialog box kaw, ib kab yuav tshwm sim sau tias "Public Sub" ua raws li lub npe txheej txheem. Hauv qab kab ntawv no yog qhov chaw thiab lo lus "End Sub."
Kauj Ruam 7. Sau tus lej rau txhua qhov nkag teb chaws hauv daim ntawv nthuav tawm
Koj yuav sau ob kab ntawm txoj cai rau txhua qhov nkag.
- Thawj kab ntawm cov cai yog nyob rau hauv daim ntawv ntawm "Range (" cellname "). Xaiv", "cellname" sawv cev rau ntawm tes uas nkag mus. Sau lub npe ntawm tes uas tam sim ntawd rau sab xis ntawm daim ntawv lo. Yog tias daim ntawv lo nyob hauv xov tooj ntawm tes A2, teb rau kev nkag tau yog cell B2 (Range ("B2". Xaiv). Cov lus hais ua ntej thiab tom qab lub npe ntawm tes tseem tau sau nyob rau theem no, tab sis tsis suav nrog hauv kab lus ua tiav.
- Cov cai hauv kab thib ob yog "ActiveCell. Value = InputBox (" InputPrompt ")". "InputPrompt" sawv cev rau cov ntawv uas yuav tshwm los ceeb toom rau tus neeg siv ntawm hom ntaub ntawv kom nkag mus rau hauv lub xov tooj ntawm tes. Piv txwv li, yog tias lub xov tooj ntawm tes yuav tau them nrog cov nqi khoom noj, hloov "InputPrompt" nrog "Nkag tag nrho cov nuj nqis rau pluas noj, suav nrog cov lus qhia." (Cov lus hais rau cov ntawv qhia kev qhia tseem suav nrog, thaum cov lus hais ua ntej thiab tom qab cov lus txib tsis tas yuav sau.)
Kauj Ruam 8. Sau tus lej rau txhua qhov kev suav teb
Ib zaug ntxiv ob kab siv yog zoo ib yam li ua ntej, tab sis lub sijhawm no ActiveCell. Value yog kev suav ntawm tus lej ua haujlwm, piv txwv li SUM, qhov twg ua haujlwm InputBox muaj nuj nqi siv los tso saib cov lus qhia.
Kauj Ruam 9. Ntxiv kab kab ntawv kom txuag tau koj daim ntawv sib tham sib tham
Hom ntawv yog "ActiveWorkbook. SaveAs Filename: =" Filename.xls "." Filename "yog lub npe ntawm daim ntawv sib tham sib tham. (Cov lus hais ua ntej thiab tom qab" Filename.xls "tseem sau, thaum cov lus hais rau txhua cov lus txib yog tsis xav tau.)
Yog tias koj siv Microsoft Excel version 2007 lossis tom qab, siv ".xlsx" hloov ".xls". Txawm li cas los xij, yog tias muaj kev sib tham sib tham cov neeg siv nrog Excel 2003 version thiab hauv qab no, lawv yuav tsis tuaj yeem siv daim ntawv nthuav tawm yam tsis muaj tus nyeem ntawv ntsaws rau
Kauj Ruam 10. Nias alt="Duab" thiab Q yuam sij ib txhij
Visual Basic editor yuav kaw.
Kauj Ruam 11. Nias alt="Duab" thiab F8 tuav ib txhij
Lub Macro dialog box yuav tshwm.
Kauj Ruam 12. Nyem rau npe txheej txheem hauv cov npe Macros
Yog tias cov txheej txheem tsim yog ib qho nkaus xwb hauv daim ntawv teev npe, nws yuav raug xaiv ua tus txiav.
Kauj Ruam 13. Nyem rau khawm Options
Koj yuav raug nug kom nkag mus rau tus lej keyboard los siv ua luv nrog Ctrl yuam sij. Xaiv tsab ntawv muaj txiaj ntsig uas tsis tau siv los ua tus lej luv, xws li "e" rau "nkag mus."
Kauj Ruam 14. Nyem "OK" kaw Macro Options dialog
Tam sim no koj tuaj yeem faib cov ntawv sib tham sib tham rau cov neeg siv. Tom qab qhib nws, tus neeg siv tuaj yeem siv cov yuam sij luv los sau thiab ua raws cov lus qhia uas koj tsim los sau cov ntaub ntawv.
Txoj Kev 2 ntawm 2: Automate Report Generation
Kauj Ruam 1. Tsim tsab ntawv ceeb toom hauv PivotTable
PivotTables tau tsim los sau cov ntaub ntawv yog li koj tuaj yeem sib piv cov lej thiab txheeb xyuas cov ncauj lus. PivotTable yuav tsum cuam tshuam nrog cov ntaub ntawv uas nyob hauv ib qho ntawm cov txheej txheem cov ntaub ntawv lossis xa los ntawm cov ntaub ntawv tshwj xeeb.
Kauj Ruam 2. Sau tsab ntawv Visual Basic kom qhib thiab kaw tsab ntawv ceeb toom
Tsab ntawv yuav tsum tuaj yeem ua tiav ntau yam haujlwm hauv qab no. Txhua txoj haujlwm yuav tau piav qhia ua raws li cov cai tau hais hauv kab ntawv los siv nws. Thaum sau tus lej tseeb, sau nws rau hauv ib qho thaiv, hloov lub npe hauv qhov piv txwv nrog koj tus kheej, thiab tsis suav nrog cov npe niam txiv thaum pib thiab xaus ntawm cov qauv piv txwv.
- Qhib daim ntawv nthuav tawm hauv hom nyeem nkaus xwb. [DIM XLAppSet XLApp = CreateObject ("Excel. App") xlapp.visible = falsexlapp.workbooks.open / excelloc / filename.xls, 3,]
- Rov qab qhib cov ntaub ntawv thiab khaws daim ntawv tshaj tawm, hauv qhov piv txwv no nws tau khaws tseg ua PDF nrog cov ntawv sau hnub tim. [Truexlapp.activeworkbook. RefreshAllxlapp.activeworkbook. ExportAsFixedFormat xlTypePDF, / pdfloc / reportname_ & DatePart ("yyyy, Tam sim no ()) &"-"& Txoj Cai (" 0 "& DatePart (" m ", Tam sim no ()), 2) & "-" Txoj cai ("0" & DatePart ("d", Tam sim no ()), 2) & ".pdf"] Yog tias cov ntawv tso tawm hom ntawv sib txawv, hloov qhov ".pdf" hom ntawv nrog kev txuas ntxiv kom raug raws li xav tau.
- Kaw daim ntawv nthuav qhia yam tsis txuag nws, tom qab ntawd kaw Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Siv ".xlsx" hloov ".xls" tom kawg ntawm daim ntawv nthuav tawm yog tias daim ntawv nthuav tawm tau khaws tseg hauv Excel 2007 thiab tom qab ntawd XML-based format.
Kauj Ruam 3. Sau ib tsab ntawv los pib ua Visual Basic tsab ntawv
Lub hom phiaj ntawm kev sau ntawv yog kom Visual Basic cov ntawv tuaj yeem ua haujlwm tau. Tsis muaj cov ntawv sau, VB cov ntawv yuav tsum tau khiav ntawm tus kheej.
Tsab ntawv tau sau rau hauv cov qauv hauv qab no, hloov nrog koj tus kheej lub npe lub npe thiab lub npe rau lub npe muab hauv qhov piv txwv no: [cscript /nologo / fileloc / script.vbs]
Kauj Ruam 4. Sau ib tsab ntawv kom ntseeg tau tias cov ntaub ntawv tso tawm muaj raws li tsim
Koj tsab ntawv yuav tsum ua cov haujlwm hauv qab no. Txhua txoj haujlwm yuav ua raws li cov cai tau hais hauv kab ntawv los siv nws. Thaum sau cov cai tiag, sau nws hauv ib qho thaiv, hloov lub npe hauv qhov piv txwv nrog lub npe koj xav tau, thiab tsis suav nrog cov npe niam txiv thaum pib thiab xaus ntawm tus qauv piv txwv.
- Txheeb xyuas yog tias muaj cov ntawv tso tawm. [Rau /f "tokens = 2-4 delims = /" %% a in ('date /t') do set rreport = reportname _ %% c-%% a-%% b.pdf)] Yog tias cov ntaub ntawv tso tawm tsis yog PDF, hloov ".pdf" nrog qhov tsim nyog txuas ntxiv.
- Yog tias cov ntaub ntawv tso tawm/ntawv tshaj tawm muaj, xa nws li email rau tus neeg xav tau. [Yog tias muaj / pdfloc%rreport%(xa email -f [email protected] -t tus txais @recipientdomain.com -u Teem Sijhawm Qhia -m Tshaj Tawm %% qhia%txuas nrog -a / pdfloc \%rreport%-s yourserver: chaw nres nkoj -xu username -xp password)]
- Yog tias cov ntaub ntawv tso tawm/ntawv tshaj tawm tsis muaj nyob hauv qhov chaw tshwj xeeb, tsim cov txheej txheem kom nws tuaj yeem xa xov rau koj tias kev xa khoom tsis ua tiav. [Lwm qhov (xa email -f [email protected] -t [email protected] -u Daim ntawv tshaj tawm tsis tau khiav -m cov ntaub ntawv % rreport % tsis muaj nyob hauv / pdfloc / -s koj li server: chaw nres nkoj -xu username -xp lo lus zais)]
Kauj Ruam 5. Nco ntsoov tias daim nplaub tshev "Desktop" nyob hauv computer
Koj yuav tsum txheeb xyuas qhov muaj nyob ntawm Desktop daim ntawv rau ob qho tib si 32-ntsis thiab 64-ntsis tshuab. Txwv tsis pub, Excel thiab daim ntawv nthuav tawm yuav tsum qhib ntawm tus kheej.
- 32-ntsis qhov chaw system: c: / windows / system32 / config / systemprofile
- 64-ntsis qhov chaw system: c: / windows / syswow64 / config / systemprofile
Kauj Ruam 6. Teem sijhawm ua haujlwm kom khiav cov ntawv sau raws li xav tau
Cov ntawv sau ua haujlwm yuav tsum ua tiav ua ntu zus thiab txuas mus ntxiv, txawm tias tsis muaj leej twg siv lub khoos phis tawj. Cov cai yuav tsum tau teeb tsa kom siab tshaj plaws.
