ßê çàïèñàòè äàí³ â excel ç vba

³äåî - ßê íàïèñàòè ìàêðîñ â Excel - ñòâîðåííÿ ³ çàïèñ VBA ìàêðîñ³â â Excel 2013. Óðîê 3

Âñ³ îô³ñí³ äîäàòêè â³ä Microsoft ï³äòðèìóþòü àâòîìàòèçàö³þ. Âîíè ìîæóòü çàïóñêàòèñÿ ÿê ñåðâåðè COM ³ âèêîðèñòîâóâàòèñÿ ç âïðîâàäæåíèõ â äîêóìåíòè àáî çîâí³øí³õ ñöåíàð³¿â. Òàê, ìîæíà çàïèñàòè äàí³ â Excel-äîêóìåíò ç vba-ñêðèïòà.
Âàì çíàäîáèòüñÿ
  • - âñòàíîâëåíå äîäàòîê Microsoft Excel;
  • - ðåäàêòîð Visual Basic / òåêñòîâèé ðåäàêòîð.
²íñòðóêö³ÿ
1



Ñòâîð³òü êîíòåéíåð äëÿ ðîçì³ùåííÿ vba-êîäó. ßêùî ñêðèïò ïîâèíåí áóòè âïðîâàäæåíèé â äîêóìåíò, çàâàíòàæòå â³äïîâ³äíèé ôàéë â Microsoft Excel. ³äêðèéòå ðåäàêòîð Visual Basic, íàòèñíóâøè êëàâ³ø³ Alt + F11. ßêùî íåîáõ³äíî, ñòâîð³òü íîâèé ìîäóëü (ïóíêò Module â ìåíþ Insert). ³äêðèéòå îäèí ç ìîäóë³â àáî ôîðì. Ñòâîð³òü îáðîáíèê åëåìåíòà óïðàâë³ííÿ ôîðìè àáî ïðîñòî äîäàéòå ïðîöåäóðó â ìîäóëü. íàïðèêëàä:
Sub test ()
End Sub
ßêùî ðîçðîáëÿºòüñÿ ñàìîñò³éíèé ñêðèïò (òîáòî â³í áóäå ïðàöþâàòè ï³ä óïðàâë³ííÿì Windows Script Host), òî ïðîñòî ñòâîð³òü ôàéë ç ðîçøèðåííÿì vbs â òåêñòîâîìó ðåäàêòîð³.
2
Ó âïðîâàäæåíèé â äîêóìåíò ñêðèïò äîäàéòå îãîëîøåííÿ çì³ííèõ â ïî÷àòîê ïðîöåäóðè:
Dim oWorkbook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Ïåðøà ç íèõ ïðèçíà÷åíà äëÿ çáåð³ãàííÿ ïîñèëàííÿ íà îá`ºêò êíèãè Excel, à äðóãà - íà ëèñò.
3
²í³ö³àë³çóéòå çì³íí³ ïîñèëàííÿìè íà îá`ºêòè. Ó vbs-ñêðèïò³ ñòâîð³òü îá`ºêò äîêëàäàííÿ Excel (ïðè öüîìó Excel áóäå çàïóùåíèé ÿê COM-ñåðâåð):
Set oApplication = CreateObject ("Excel.Application").
Ó çàïðîâàäæåíèé â äîêóìåíò ñöåíà𳿠çàì³ñòü çì³ííî¿ oApplication áóäå âèêîðèñòîâóâàòèñÿ ãëîáàëüíèé îá`ºêò Application, ÿêèé ïîñèëàºòüñÿ íà ïîòî÷íèé îá`ºêò äîêëàäàííÿ. Âèáåð³òü ³ñíóþ÷ó àáî â³äêðèéòå íîâó êíèãó Excel. íàïðèêëàä:
Set oWorkbook = Application.Workbooks (1)
Set oWorkbook = Application.Workbooks ("Êíèãà 1")
Set oWorkbook = oApplication.Workbooks.Open ("D: vic relevant tmp test.xls")
Îòðèìàéòå ïîñèëàííÿ íà ïîòð³áíèé ëèñò êíèãè:
Set oSheet = oApplication.Sheets ("Ëèñò1")
4
Çàïèø³òü äàí³ â Excel ç vba-ñöåíàð³þ. Âèêîðèñòîâóéòå êîëåêö³þ Cells, ÿêà º âëàñòèâ³ñòþ îá`ºêòà ëèñòà êíèãè, ïîñèëàííÿ íà ÿêèé áóëà îòðèìàíà íà ïîïåðåäíüîìó åòàï³, â çì³ííó oSheet. Ïðèêëàä çàïèñó ðÿäêà â îäíó êë³òèíêó ìîæå âèãëÿäàòè òàê:
oSheet.Cells (1, 1) = "Ðÿäîê çàïèøåòüñÿ â êë³òèíêó A1"
5
Ó vbs-ñêðèïò³ äîäàéòå êîä äëÿ çáåðåæåííÿ äàíèõ ³ çàâåðøåííÿ ðîáîòè ïðîãðàìè Excel:
oWorkbook.Save
oApplication.Quit
6
Çáåðåæ³òü ³ âèêîíàéòå ñêðèïò. Ó ðåäàêòîð³ Visual Basic íàòèñí³òü Ctrl + S, à ïîò³ì ïîì³ñò³òü êóðñîð â ò³ëî ïðîöåäóðè ³ íàòèñí³òü F5. Ñöåíàð³é vbs çáåðåæ³òü íà äèñê, à ïîò³ì çàïóñò³òü ÿê çâè÷àéíèé ôàéë.

Ïîä³ëèñÿ â ñîö ìåðåæàõ:

Óâàãà, ò³ëüêè ÑÜÎÃÎÄͲ!
Ñõîæ³
» » ßê çàïèñàòè äàí³ â excel ç vba