ßê çàïèñàòè äàí³ â 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 â òåêñòîâîìó ðåäàêòîð³.
Sub test ()
End Sub
ßêùî ðîçðîáëÿºòüñÿ ñàìîñò³éíèé ñêðèïò (òîáòî â³í áóäå ïðàöþâàòè ï³ä óïðàâë³ííÿì Windows Script Host), òî ïðîñòî ñòâîð³òü ôàéë ç ðîçøèðåííÿì vbs â òåêñòîâîìó ðåäàêòîð³.
2
Ó âïðîâàäæåíèé â äîêóìåíò ñêðèïò äîäàéòå îãîëîøåííÿ çì³ííèõ â ïî÷àòîê ïðîöåäóðè:
Dim oWorkbook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Ïåðøà ç íèõ ïðèçíà÷åíà äëÿ çáåð³ãàííÿ ïîñèëàííÿ íà îá`ºêò êíèãè Excel, à äðóãà - íà ëèñò.
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")
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"
oSheet.Cells (1, 1) = "Ðÿäîê çàïèøåòüñÿ â êë³òèíêó A1"
5
Ó vbs-ñêðèïò³ äîäàéòå êîä äëÿ çáåðåæåííÿ äàíèõ ³ çàâåðøåííÿ ðîáîòè ïðîãðàìè Excel:
oWorkbook.Save
oApplication.Quit
oWorkbook.Save
oApplication.Quit
6
Çáåðåæ³òü ³ âèêîíàéòå ñêðèïò. Ó ðåäàêòîð³ Visual Basic íàòèñí³òü Ctrl + S, à ïîò³ì ïîì³ñò³òü êóðñîð â ò³ëî ïðîöåäóðè ³ íàòèñí³òü F5. Ñöåíàð³é vbs çáåðåæ³òü íà äèñê, à ïîò³ì çàïóñò³òü ÿê çâè÷àéíèé ôàéë.
Ïîä³ëèñÿ â ñîö ìåðåæàõ:
Ñõîæ³
- ßê çì³íèòè ïàðàìåòðè â excel
- ßê ïåðåâåñòè òàáëèöþ ç excel â word
- ßê îá`ºäíàòè êîì³ðêè â excel áåç âòðàòè äàíèõ
- ßê â³äíîâèòè íåçáåðåæåíèé ôàéë excel
- ßê â³äêðèòè ôîðìàò ods
- ßê â excel â³äí³ìàòè
- ßê â excel âñòàâèòè ôîðìóëó
- ßê â excel çàô³êñóâàòè êë³òèíêó
- ßê âèâåñòè íà åêðàí ïîâ³äîìëåííÿ
- ßê â excel ïðèõîâàòè ðÿäêè
- ßê â excel çðîáèòè óìîâà
- ßê â excel âèâåñòè ôîðìóëè
- ßê ñòâîðèòè ä³àãðàìó
- ßê ïîáóäóâàòè excel òàáëèöþ
- ßê â excel ñêîï³þâàòè òàáëèöþ
- ßê ïîðàõóâàòè â åêñåëÿ ç â³äñîòêàìè
- ßê â excel â³äíîâèòè äàí³
- ßê çàïèñàòè ìàêðîñè â excel
- ßê ïîñòàâèòè ïàðîëü íà excel äîêóìåíò
- ßê çíàéòè íåçáåðåæåíèé äîêóìåíò
- ßê çðîáèòè excel â³äêðèòèì