Te whakahaere tonotono i te waa

He take tino noa i roto i te mahi: me whakahaere koe i tetahi, neke atu ranei o to tonotono i te waa kua whakaritea, i tetahi waa ranei. Hei tauira, he purongo nui, taumaha hoki e whakahou ana i te hawhe haora ka hiahia koe ki te whakahaere i te whakahou i te hawhe haora i mua i to taenga ki te mahi i te ata. Kei a koe he tonotono me tuku aunoa nga imeera ki nga kaimahi i te waa kua tohua. Ranei, ina mahi ana koe me te RipangaKaurori, ka hiahia koe ki te whakahou i runga i te rere ia 10 hēkona, me era atu.

Kia titiro tatou he aha te kaha o Excel me Windows ki te whakatinana i tenei.

Te whakahaere tonotono i te auau

Ko te huarahi ngawari ki te mahi i tenei ko te whakamahi i te tikanga hanga-i roto i te VBA Taupānga.OnTimeHe whakahaere i te tonotono kua tohua i te waa kua tohua. Kia mohio tatou ki tenei me tetahi tauira mahi.

Whakatuwheratia te ētita Visual Basic me te paatene o taua ingoa i runga i te ripa kaiwhakawhanake (Kaiwhakawhanake) he pokatata papapātuhi ranei alt+F11, kōkuhu kōwae hōu mā te tahua Kōkuhu – Kōwae ka kape i te waehere e whai ake nei ki reira:

Dim TimeToRun 'taurangi ao kei te penapena te wa oma e whai ake nei 'koinei te tonotono matua Sub MyMacro() Application.Calculate 'tātaihia te pukapuka Awhe("A1").Interior.ColorIndex = Int(Rnd() * 56) 'whakakī pūtau A1 me te tae matapōkere :) Karangatia NextRun 'whakahaerehia te tonotono NextRun ki te tautuhi i te wa oma e whai ake nei Whakamutunga Sub 'ma tenei tonotono e whakarite te wa mo te rere o te tonotono matua Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'taapirihia te 3 hēkona ki te wa o naianei Application.OnTime TimeToRun, "MyMacro" 'whakaritea te oma e whai ake nei Whakamutunga Sub' tonotono hei timata i te raupapa tukurua Sub Start() Waea NextRun End Sub' tonotono ki te whakamutu i te raupapa tukurua Sub Mutu() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Kia mohio tatou he aha kei konei.

Tuatahi, me hiahia he taurangi hei pupuri i te wa o te rere o to tatou tonotono - ka karanga ahau TimeToRun. Kia mahara ko nga ihirangi o tenei taurangi me watea ki o tatou tonotono katoa o muri mai, no reira me mahi tatou ao, ara, whakapuaki i te timatanga o te kōwae i mua i te tuatahi Sub.

Ka haere mai to maatau tonotono matua TakuMacro, ka mahi i te mahi matua – ki te tatau ano i te pukapuka ma te whakamahi i te tikanga Taupānga.Tia. Kia marama ake ai, i tapirihia e ahau te tauira =TDATE() ki te rau i roto i te pūtau A1, e whakaatu ana i te ra me te wa - ka tatauhia ano, ka whakahouhia nga ihirangi ki mua tonu i o tatou kanohi (whakahokia noa te whakaaturanga o nga hēkona i roto i te pūtau. hōputu). Mo te ngahau ake, i tapirihia ano e ahau ki te tonotono te whakahau kia whakaki i te pūtau A1 ki te tae matapōkeretia (ko te waehere tae he tauoti i te awhe 0..56, he mea hanga e te mahi. Rnd me te whakaawhiwhi ki runga ki te mahi tauoti Int).

Tonotono Rere Panuku ka taapiri atu ki te uara o mua TimeToRun 3 hēkona anō ka whakarite i te oma o te tonotono matua TakuMacro mo tenei wa hou. Ko te tikanga, i roto i te mahi, ka taea e koe te whakamahi i etahi atu waahi e hiahia ana koe ma te whakarite i nga tautohetohe mahi Uara Wā i te whakatakotoranga hh:mm:ss.

Ka mutu, mo te waatea noa, kua taapirihia etahi tonotono whakarewa raupapa. Kāinga me tona otinga Mutu. Ko te whakamutunga ka whakamahi i te tohenga tikanga tuawha ki te wawahi i te raupapa. I te wa rite teka.

Tapeke ki te whakahaere koe i te tonotono Kāinga, ka huri tenei carousel katoa, ka kite tatou i te pikitia e whai ake nei i runga i te rau:

Ka taea e koe te aukati i te raupapa ma te whakahaere i te tonotono Mutu. Mo te waatea, ka taea e koe te tautapa i nga pokatata papapātuhi ki nga tonotono e rua ma te whakamahi i te whakahau Tonotono – Kōwhiringa ripa kaiwhakawhanake (Kaiwhakawhanake — Tonotono — Kōwhiringa).

Te whakahaere tonotono i runga i te waarangi

Ko te tikanga, ko nga mea katoa e whakaahuatia ana i runga ake ka taea anake mena kei a koe te whakahaere a Microsoft Excel ka tuwhera to maatau konae ki roto. Inaianei me titiro ki tetahi keehi uaua ake: me whakahaere koe i a Excel i runga i te raarangi kua whakaritea, hei tauira, ia ra i te 5:00, whakatuwheratia he purongo nui me te uaua ki roto ka whakahou i nga hononga me nga patai katoa kei roto kia pai ai. kia rite mo te wa ka tae ki te mahi 🙂

I tenei ahuatanga, he pai ake te whakamahi Pūhōtaka Windows – he papatono i hangaia ki roto i tetahi momo putanga o Windows ka taea te mahi i nga mahi kua tohua i runga i te waarangi. Inaa, kei te whakamahi kee koe me te kore e mohio, na te mea ka tirotirohia e to PC nga whakahoutanga, te tango i nga papaaarangi anti-huaketo hou, te tukutahi i nga kōpaki kapua, me era atu. Ko te mahi katoa a te Kaihōtaka. Na ko ta maatau mahi he taapiri atu ki nga mahi o naianei tetahi atu ka whakarewa i a Excel ka whakatuwhera i te konae kua tohua ki roto. A ka whakairihia ta tatou tonotono ki runga i te kaupapa Pukamahi_Tuwhera tenei kōnae – a kua whakatauhia te raruraru.

Kei te pirangi au ki te whakatupato i a koe i tenei wa ko te mahi tahi me te Kaihōtaka ka hiahia motika kaiwhakamahi matatau, na ki te kore e kitea e koe nga whakahau me nga mahi e whakaahuatia ana i raro nei i runga i to rorohiko mahi i te tari, whakapaa atu ki o tohunga IT mo te awhina.

Te whakarewa i te Kaihōtaka

Na kia timata a te Kaihōtaka. Ki te mahi i tenei, ka taea e koe:

• Pāwhiri matau ki te paatene Tīmata me te whiriwhiri Whakahaere Rorohiko (Whakahaere Rorohiko)
• Tīpakohia i te Paewhiri Mana: Whakahaerenga – Kaiwhakarite Mahi (Paewhiri Mana — Utauta Whakahaere — Kaihōtaka Mahi)
• Tīpakohia mai i te tahua matua Tīmata – Apatoko – Utauta Pūnaha – Kaihōtaka Mahi
• Patohia te pokatata papapihi Win+R, tomo Taumahi.dc me te pēhi E tomo

The following window should appear on the screen (I have an English version, but you can also have a version):

Hangaia he mahi

Hei waihanga i tetahi mahi hou ma te whakamahi i tetahi ruānuku taahiraa-i-taahiraa ngawari, paatohia te hono Waihangahia he mahi ngawari (Waihanga Mahi Taketake) i te pae matau.

I te taahiraa tuatahi o te ruānuku, whakauruhia te ingoa me te whakaahuatanga o te mahi ka mahia:

Pāwhiritia te pātene Panuku (Panuku) a hei te mahi e whai ake nei ka tohua e matou he keu - te tere o te whakarewatanga, he huihuinga ranei ka whakarewa i a maatau mahi (hei tauira, te huri i te rorohiko):

Mena i kowhiria e koe Daily (Ia rā), i te taahiraa e whai ake nei ka hiahia koe ki te kowhiri i te waa motuhake, te ra tiimata o te raupapa me te taahiraa (ia rua o nga ra, ra 2, me etahi atu):

Ko te mahi e whai ake nei ko te whiriwhiri i tetahi mahi - Whakahaerehia te papatono (Tīmatahia he hōtaka):

Ka mutu, ko te mea tino pai ko te mea e tika ana kia whakatuwherahia:

i roto i te Papatono, tuhinga tuhi ranei (Pataka/tuhi) me whakauru koe i te huarahi ki a Microsoft Excel hei kaupapa, ara ki te Excel executable. I runga i nga rorohiko rereke me nga momo putanga rereke o Windows me Office, kei roto pea tenei konae i nga kōpaki rereke, na konei etahi huarahi hei kimi i tona waahi:

• Pāwhiri-matau i runga i te ata (pokatata) hei whakarewa i te Excel ki runga papamahi, ki te paetaumahi ranei ka kowhiria te tono Materials (Ahuatanga), a, i te matapihi e tuwhera ana, kapehia te ara mai i te raina Target:

                       

• Whakatuwherahia tetahi pukamahi Excel, ka whakatuwhera Kaiwhakahaere Tūmahi (Kaiwhakahaere Mahi) koi Ctrl+alt+Mai a ma te panui matau i te raina Microsoft Excel, whiriwhiria he whakahau Materials (Ahuatanga). I te matapihi e tuwhera ana, ka taea e koe te kape i te ara, kaua e wareware ki te taapiri i te whakamuri ki a EXCEL.EXE i te mutunga:

               

• Whakatuwheratia te Excel, whakatuwheratia te ētita Visual Basic me te pokatata papapātuhi alt+F11, papa tuwhera tonu Tuhinga o mua Ctrl+G, tomo te whakahau ki roto:

  ? Taupānga.Ara

  … ka paato atu E tomo

  

  Tāruatia te ara hua, kaua e wareware ki te taapiri i te whakamuri ki a EXCEL.EXE i te mutunga.

i roto i te Tāpirihia he tautohe (he whiringa) (Taapirihia nga tohenga (he whiriwhiri)) me whakauru e koe te ara katoa ki te pukapuka me te tonotono e hiahia ana matou ki te whakatuwhera.

Ka uru nga mea katoa, katahi ka paato Panuku a ka Mutu (Whakaoti). Me taapiri te mahi ki te rarangi whanui:

He pai ki te whakahaere i te mahi i hangaia ma te whakamahi i nga paatene kei te taha matau. I konei ka taea e koe te whakamatautau i te mahi ma te whakahaere tonu (oma)me te kore e tatari mo te wa kua tohua. Ka taea e koe te whakakore i tetahi mahi mo te wa poto (Monokia)kia mutu te rere mo tetahi wa, penei i to hararei. Ana, ka taea e koe te whakarereke i nga tawhā (ra, wa, ingoa konae) ma te paatene Materials (Ahuatanga).

Tāpirihia he tonotono hei whakatuwhera i te konae

Inaianei kei te whakairihia ki roto i ta maatau pukapuka te whakarewatanga o te tonotono e hiahiatia ana mo te huihuinga tuwhera konae. Ki te mahi i tenei, whakatuwheratia te pukapuka ka haere ki te ētita Visual Basic ma te whakamahi i te pokatata papapātuhi alt+F11 patene ranei Visual Basic ripa kaiwhakawhanake (Kaiwhakawhanake). I te matapihi e tuwhera ana i te kokonga maui o runga, me rapu koe i ta maatau konae i runga i te rakau ka paato-rua ki te whakatuwhera i te waahanga. Tenei pukapuka (Tenei Pukamahi).

Ki te kore koe e kite i tenei matapihi i roto i te ētita Visual Basic, ka taea e koe te whakatuwhera ma te tahua Tirohanga — Project Explorer.

I te matapihi kōwae e tūwhera ana, tāpirihia he kaihautū takahanga pukapuka tuwhera mā te tīpako mai i ngā rārangi taka-iho i runga Pukapuka mahi и tuwhera, aua:

Me puta he tauira tikanga ki te mata. Pukamahi_Tuwhera, kei waenganui i nga rarangi Tangata Tangata и Sub whakamutunga a me whakauru e koe aua whakahau VBA me mahi aunoa ina whakatuwheratia tenei pukamahi Excel, ina whakatuwherahia e te Kaihōtaka kia rite ki te raarangi. Anei etahi whiringa whaihua mo te overclocking:

• ThisWorkbook.RefreshKatoa – Ka whakahou i nga patai raraunga o waho, nga patai Uiui Hiko, me nga RipangaKaurori. Ko te kōwhiringa tino whai kiko. Kaua e wareware ki te tuku hononga ki nga raraunga o waho ma te taunoa me te whakahou i nga hononga ma Kōnae – Kōwhiringa – Pokapū Whirinaki – Kōwhiringa Pokapū Whirinaki – Ihirangi o waho, ki te kore, ka whakatuwherahia e koe te pukapuka, ka puta mai he whakatupato paerewa me te Excel, me te kore e whakahou i tetahi mea, ka tatari mo to manaakitanga i te ahua o te panui i te paatene Whakahohe ihirangi (Whakahohe ihirangi):

  

• ActiveWorkbook.Connections("Ingoa_Hononga").Whakahou — te whakahou i nga raraunga mo te hononga Hononga_Ingoa.
• Rau(“Raurangi5“).RipangaKaurori(“RipangaKaurori1«).PivotCache.Refresh – whakahōu ana i te ripanga kaurori kotahi kua whakaingoatia RipangaKaurori1 i runga i te rau Rauemi5.
• Taupānga.Tia – te tataunga o nga pukamahi Excel tuwhera katoa.
• Application.CalculateFullRebuild – Me tatau ano i nga tauira katoa me te hanga ano i nga whakawhirinakitanga katoa i waenga i nga pūtau i roto i nga pukamahi tuwhera katoa (he rite ki te whakauru ano i nga tauira katoa).
• Pepamahi(“Ripoata”).Print Out – pepa tā Whakaahua.
• Karangatia takuMacro – whakahaere tonotono kua whakaingoatia TakuMacro.
• ThisWorkbook.Tiaki – tiakina te pukapuka o naianei
  
• ThisWorkbooks.SaveAs “D:ReportReport” & Whakakapi(Na, “:”, “-“) & “.xlsx” – tiakina te pukapuka ki te kōpaki D:Puranga i raro i te ingoa Whakaahua me te ra me te wa i apitihia ki te ingoa.

Mena kei te pirangi koe kia mahia te tonotono ina whakatuwherahia te konae e te Kaihōtaka i te 5:00 am, kaua i nga wa katoa ka whakatuwhera te kaiwhakamahi i te pukamahi i te ra mahi, he mea tika ki te taapiri i te haki taima, hei tauira:

Ki te Whakahōputu(Na, "hh:mm") = "05:00" Na ThisWorkbook.RefreshAll  

Heoi ano. Kaua e wareware ki te tiaki i to pukamahi ki te whakatakotoranga tonotono (xlsm, xlsb ranei) ka taea e koe te kati i a Excel ka hoki ki te kainga, ka noho tonu to rorohiko. I tetahi wa (ahakoa kua raka te PC), ka whakarewahia e te Kaihōtaka a Excel ka whakatuwhera i te konae kua tohua ki roto, ka mahia e to maatau tonotono nga mahi kua whakaritea. A ka harikoa koe i runga i te moenga i te wa e tatau aunoatia ana to ripoata taumaha - ataahua! 🙂

• He aha nga tonotono, me pehea te whakamahi, kei hea te whakauru i te waehere Visual Basic ki Excel
• Me pehea te hanga i to ake tonotono taapiri mo Excel
• Me pehea te whakamahi i te Pukamahi Tonotono Whaiaro hei whare pukapuka mo o tonotono i Excel