Access VBAãå§ããç¬¬äžæ©
Accessã¯ãããšããšExcelãšã¯éãããã°ã©ãã³ã°ããããšããããçšåºŠã¢ããªã±ãŒã·ã§ã³ãšããŠã®äœè£ãäœããããã«æ©èœãåãã£ãŠããŸããããããã¯ãæ©èœïŒExcelã®ãããšã¯ã¡ãã£ãšéããŸãïŒãããã°ã©ãã³ã°ã®æé ãã¬ãŽãããã¯ã®ããã«ã€ãªããŠãåŠçãäœãæ©èœã§ãã
ãŸããã¯ãšãªã®èšç®åã§ã¯Accessçšã®é¢æ°ã䜿ããŸãïŒExcelã§ãããªãã¿ã®é¢æ°ããããŸãããDLookup颿°ãªã©ã®DBæäœé¢æ°ã远å ã§çšæãããŠããŸãïŒã
ããããVBAãã§ããã°æŽã«ãã®å¯èœæ§ã¯åºãããŸããä»åã¯ç¬¬äžæ©ãªã®ã§ãåºæ¬çãªåŠçãã¯ãšãªåã§äœ¿ã颿°ãèªäœããŠã¿ãŠãã©ããªäºãåºæ¥ãã®ããèŠãŠãããããšæããŸãã
ç®æ¬¡
- 1 ä»å䜿çšããAccessãã¡ã€ã«
- 2 ãŸãã¯ãã¯ãæ©èœãã䜿ã£ãŠã¿ã
- 3 èªåã§é¢æ°ãäœã£ãŠèŠã
- 4 ç°¡åãªåŠçãæžããŠã¿ã
- 5 é¢é£ãªã³ã¯
ä»å䜿çšããAccessãã¡ã€ã«
â»ä»åã®ãµã³ãã«ã«ã€ããŠããã¯ãæ©èœã¯ãããŸã§ãèªåã®PCã®ç°å¢ã§ã®ã¿åããŸãïŒãã¹ã人ã«ãã£ãŠç°ãªãçºïŒããã£ãŠãïŒããå®éã«äœã£ãŠã¿ãã®ã»ã¯ã·ã§ã³ã«ããããã«ãã€ã³ããŒãå®çŸ©ãçµãã§ãããå¿ èŠããããŸããVBAã®ã»ãã¯ããã¹ãéžã¹ãã®ã§ããã®ãŸãŸåäœããŸãã
ãŸãã¯ãã¯ãæ©èœãã䜿ã£ãŠã¿ã
Accessãã¯ãã®æŠèŠ
Accessã®ãã¯ãæ©èœã¯Excelã®ãããªæäœãèšé²ããŠåçãããããªãã®ã§ã¯ãããŸãããã¬ãŽãããã¯ã®ããã«äžããäžãžåŠçãäœã蟌ã¿ã人ã€ãªãã®åŠçã«ããŠèªååãå³ããã®ã§ãããã®ãããExcelãšéãåŠçã®æµããèŠããŸãããŸããVBAã®ããã«ã³ãŒããæžãããã§ã¯ãªãã®ã§ãé£ããçå±ãªã©ã¯æ®ã©äžèŠã§ãã
èªåã¯VBAã§å šéšæžããŠããŸãã®ã§ãããŸãäœ¿ãæ©äŒã¯ãªãã®ã§ããããã䜿ããã¯ãã®æ©èœã¯ä»¥äžã®æ°åã
- ãã©ãŒã ãéã
- ã¯ãšãªãéã
- ãã£ã«ã¿ãŒã®èšå®ããã³å®è¡ã»è§£é€
- ããã·ãŒãžã£ã®å®è¡
- ã¡ãã¥ãŒã³ãã³ãã®å®è¡
- ã¬ã³ãŒãã®ç§»åã远å ãåé€
- æžåŒèšå®ãä¿æãããŸãŸãšã¯ã¹ããŒã
ãããªæãã§ããä»åã¯ãCSVããããŒã¿ãåã蟌ãã§æŽåœ¢ãããšã¯ã¹ããŒããŸã§ãã£ãŠã¿ãããšæããŸãã
ãã¯ãã®äœææé
ãã¯ããæ©éã€ãã£ãŠã¿ãŸããããç°¡åãªæ¡ä»¶åå²ïŒç¹å®ã®æ¡ä»¶ã®æã ããã¯ãã®å®è¡ãèš±å¯ãããªã©ïŒãå¯èœã
- äœæã¿ããéã
- å³ã®ã»ãã«ããã¯ãããããã®ã§ãã¯ãªãã¯ãã
- æ°ããã¢ã¯ã·ã§ã³ã®è¿œå ã§ããã¯ãåŠçãæ¬¡ã ã«äžŠã¹ãŠãã
- æåŸã«ååãã€ããŠä¿åãã
- 4.ã§ä¿åãããã¯ããããã«ã¯ãªãã¯ãããããã©ãŒã ã®ãã¿ã³ã«å²ãåœãŠããšãã¯ããå®è¡ãããã
å³ïŒãã¯ãäœæç»é¢ã®æ§å
å®éã«äœã£ãŠã¿ã
ä»åã®ç®çã§ãããCSVããããŒã¿ãåã蟌ãã§æŽåœ¢ããExcelã·ãŒãã§ãšã¯ã¹ããŒãããããšããã®ãå®çŸããŠã¿ãŸããæ·»ä»ã®ãã¡ã€ã«ã«å«ãŸããŠããã€ã³ããŒãããŒã¿CSVãšãããã¡ã€ã«ãå©çšããŸãïŒSmartDataã®ãããŒããŒã¿çæãµãŒãã¹ãå©çšããŸããïŒããµã³ãã«ãã¡ã€ã«ã«ã¯åãæ§é ã®ä»å ¥ããŒã¿ãšããããŒãã«ãçšæããŠãããŸãããŸãã¯ãã¯ããäœãäºåæºåã
- ãŸãã¯ãæ®éã©ããCSVããŒã¿ãããŒãã«ã«ã€ã³ããŒãããŸãã
- å€éšããŒã¿ã¿ããéããããã¹ããã¡ã€ã«ãã¯ãªãã¯ããŸãã
- ã¬ã³ãŒãã®ã³ããŒã次ã®ããŒãã«ã«è¿œå ãããéžãã§ãä»åã¯ã€ã³ããŒãããŒã¿ããŒãã«ãæå®ããŸãã
- 次ã®ç»é¢ã§ã¯åºåããæå®ããŸãããããã©ã«ãã®ã«ã³ãåºåãã§è¡ããŸãã
- ãŸããå é è¡ããã£ãŒã«ãåãšããŠäœ¿ãã«ãã§ãã¯ãå ¥ããŠãããã¹ãåºåãèšå·ã§ã¯ããã«ã³ãŒããŒã·ã§ã³ãæå®ããŸãïŒä»åã®ãµã³ãã«CSVã¯ããã¹ãã¯ããã«ã³ãŒããŒã·ã§ã³ã§æ¬ã£ãŠããçºïŒ
- æåŸã®ç»é¢ã§ã¯å®äºãæŒããã«ãèšå®ãã¿ã³ãã¯ãªãã¯ããŸãã
- ããã§æ³šæãããã®ã¯ãããŒãã«ã®IDåã¯ãªãŒããã³ããŒãªã®ã§ãCSVã®IDåã¯å ¥ããããªããšããäºããã®ãŸãŸã€ã³ããŒããå®è¡ãããšå€±æããŸãããªã®ã§ãIDã¯ã¹ãããã«ãã§ãã¯ãå ¥ããŠãããŸãã
- ã€ã³ããŒãå®çŸ©ã®ç»é¢ã§ã¯ãä¿åãã¯ãªãã¯ããŠã€ã³ããŒãå®çŸ©ã«ååãä»ããŸããïŒCSVããŒã¿åã蟌ã¿ãšåœåããŸããïŒ
- ãããŠãå®äºãã¿ã³ãæŒããŠå®äº
- ã€ã³ããŒãæäœã®ä¿åã«ãã§ãã¯ãå ¥ããŠçµããããŸã
- ãšãããããã€ã³ããŒãããŒãã«ã®äžèº«ã¯å šéšåé€ããŠããã
å³ïŒã€ã³ããŒãäœæ¥æé ãä¿åãã
å³ïŒã€ã³ããŒãå®çŸ©ãä¿åããç»é¢ãããããã¯ãã§åŒã³åºã
ããŠãããã§ãã¯ããäœããŸãã以äžã®æé ã§äœã£ãŠã¿ãŸãããã
- ãã¯ãã1åæ°èŠäœæããŠç·šéç»é¢ã«å ¥ã
- ãã¹ãŠã®ã¢ã¯ã·ã§ã³ã衚瀺ãã¯ãªãã¯ããïŒããã§ãã¯ãã¢ã¯ã·ã§ã³ãå šãŠåºãŠããŸãïŒ
- ä¿åæžã¿ã®ã€ã³ããŒã/ãšã¯ã¹ããŒãæäœã®å®è¡ãéžã¶
- ã€ã³ããŒãå®çŸ©ã¯ãCSVããŒã¿åã蟌ã¿ããéžæ
- ã€ã¥ããŠãæžåŒãä¿æãããŸãŸãšã¯ã¹ããŒãã远å ãã
- ãªããžã§ã¯ãã®çš®é¡ã¯ãããŒãã«ãããªããžã§ã¯ãåã¯ãã€ã³ããŒãããŒã¿ããåºåãã¡ã€ã«åœ¢åŒã¯ãxlsxããã¯ããèšå®ãã
- ãã¯ããä¿åããïŒã€ã³ããŒããã¯ããšããååãä»ããŸããïŒ
- é©åœãªãã©ãŒã ã«ãã¿ã³ãé 眮ãã
- ãã¿ã³ã¯äœã£ãŠãã³ãã³ãã¯å²ãåœãŠããã®ãŸãŸããã¿ã³ãå³ã¯ãªãã¯ããŠãããããã£ãéã
- ããããã£ã·ãŒãã®ãã¯ãªãã¯æãã®å³åŽãè§ŠãããVããã¯ãªãã¯ããã
- äœã£ãã€ã³ããŒããã¯ãããªã¹ãã«åºãŠããã®ã§éžæãã
- ããã§ãã¿ã³ãã¯ãªãã¯ãããšãã¯ããå®è¡ãããŸãã
â»äœããã®æ¹æ³ã ãšã決ãŸã£ãå Žæã«ããxlsxãæ±ºãŸã£ãããŒãã«ã«å ¥ãããšãããã¯ããªã®ã§ãåã蟌ã¿ãã¡ã€ã«ã®éžæçã¯åºæ¥ãŸããã
â»ã¡ãã£ãšè€éãªããšãããããããšãããšããéçãæ¥ããããã®ã§ãå²ãšå°èŠæš¡ãªãã®ã§äœ¿ãã«ã¯åããŠãããšæããŸãïŒã¬ã³ãŒãã®ç§»åãã¿ã³ãä»åã®ãããªåçŽãªåºåãªã©ãªã©ïŒ
èªåã§é¢æ°ãäœã£ãŠèŠã
æŠèŠ
VBAãšããããã°ã©ãã³ã°ãå匷ããäžã§äžçªæåã«å ¥ãã¹ããã®ã¯ãèªäœã®é¢æ°ãã§ããExcelãªã©ã§ã¯ããªãã¿ã®é¢æ°ã§ããããŠãŒã¶å®çŸ©é¢æ°ãšèšããèªåã§ãäœããããã«ãªã£ãŠããŸãããã®é¢æ°ããŸããVBAã§äœãã®ã§ãããå®éã«äœã£ãŠã¿ããšãã€ã¡ãŒãžããŠãããã®ãšã¯éãé£ãããã®ã§ã¯ãªããšæããã¯ãã§ãã
åŒãåºãã®æ°ãå¢ããã°å¢ããã»ã©ã«ããã®é¢æ°ãããã°ã©ãã³ã°ã®å¹ ã¯åºãããŸãããã²ãšã€ã²ãšã€ã¯æ¥µç«¯ã«é£ãããã®ã§ã¯ãããŸãããããããExcelãªã©ã§ã¯åçŽãªé¢æ°ãå ¥ãåã«ããŠçµãããŠè¡ããšãäžäœäœãããŠããã®ãããããªãæ°åŒã«ãªã£ããããŸãããããã·ã³ãã«ã«ãªããèªåã§æ¬²ããæ©èœã远å ããŠãããã®ã¯åŒ·ã¿ã§ãã
ããã°ã©ãã³ã°ã®åºç€
ããã°ã©ãã³ã°ãšãããšããªã«ããæªãã颚äœã®äººããã¢ã³ã¹ã¿ãŒãšããžãŒãããã飲ã¿ãªãããé»ãç»é¢ã§ç·è²ã®æåã§ãåªæãæžããŠãããã«æ³åãã人ã¯å€ããšæããŸãïŒæŠãåã£ãŠãŸããïŒããããããã®äžèº«ãšãããã®ã¯èšãã»ã©åªæã§ã¯ãããŸãããæµææãæããçç±ã¯ãããããè±èªãã§ããããããé«åºŠãªæžãæ¹ããããAPIãšåŒã°ãããã®ãåŒã³åºãããšãã£ãäœæ¥ãããããããã®ã§ããããå®éã«ã¯å ¥éã¬ãã«ãªãã°ã以äžã®æ°ç¹ãæããŠããŸãã°ãæ§ã ãªåŠçãæžãããããŸãã
- IFãSelectæã«ããæ¡ä»¶åå²ïŒifã¯Excelã®é¢æ°ã§ãã銎æã¿ã§ããïŒ
- Forã«ãŒãã§ç¹°ãè¿ãåŠçïŒ1ã»ã«ãã€éãçããæžããŠãããªã©ã§ã¯ãã䜿ããŸããïŒ
- ã¡ã¢ãªäžã§ããŒã¿ã®å¡ãé ååŠçïŒãããå€åãšã£ã€ãã«ãããã§ããæ £ãããšæ¬ ãããªãïŒ
- ååæŒç®
- å€ã®äžèº«ãæ¯èŒæŒç®
- ããããããã¡ãœããïŒç°¡åã«ããã°ãVBAäžã§äœ¿ãExcelã®é¢æ°ã¿ãããªãã®ïŒ
ããã ãã§ãããã§ã«ExcelãããçšåºŠäœ¿ããŠãããªãã°ãå®ã¯ããVBAã®åºç€ã®åºç€ã¯ç¿åŸæžã¿ã ã£ããããŸãïŒç¹ã«ã6.ãšãIFãªã©ã¯ïŒ
ããã°ã©ãã³ã°ã®å§ãæ¹
以äžã®æé ã§ããã°ã©ãã³ã°ãéå§ã§ããŸããä»åã¯Access VBAãªã®ã§ãã®æé ã«ãªããŸãã
- ããŒã¿ããŒã¹ããŒã«ã¿ãã®äžã«ãããVisual Basicããã¯ãªãã¯ããŸãã
- ä»åã¯é¢æ°ãäœãã®ã§ãå·ŠåŽã®ããããžã§ã¯ããã®äžã§å³ã¯ãªãã¯âæ¿å ¥âæšæºã¢ãžã¥ãŒã«ãã¯ãªãã¯
- ãããããšãModule1ãšããååã®ãã¡ã€ã«ãäœãããŸããäžã®ããããã£ãšããå Žæã«ãããªããžã§ã¯ãåã«ãŠãã¢ãžã¥ãŒã«ã®ååãå€ããŠèµ·ããŸããããåºæ¬ã¯ããŒãåã§åœåããŸãã
- å³åŽã®ããã«ã«ã³ãŒããæžããŠãããŸãã
- å³äžã®ã€ããã£ãšã€ããšã¯ããããã°äœæ¥ã«äœ¿ãçºã®ãã®ã§ãDebug.print 倿°ã®ååãšããã³ãŒããæžããŠå®è¡ãããšã倿°ã®äžèº«ã衚瀺ãããã§ããŸããã³ãŒãã®éäžã§èšç®ãæ£ããããã§ãã¯ãããããŸãã
- ã³ãŒããæžããŠå®è¡ããæã«ãéäžãŸã§å®è¡ãããæããããŸãããã®æã¯ã³ãŒãã®ã©ã€ã³ã®å·ŠåŽã®ç°è²éšåãã¯ãªãã¯ãããšãâãç»é²ãããŠãããã§ã³ãŒãã®å®è¡ãäžæäžæããŠãããŸããããããã¬ãŒã¯ãã€ã³ããšåŒã³ãŸãã
- äžã®ã»ãã«ããåçãã¿ã³ã忢ãã¿ã³çãªãã®ããããã€ã³ã¹ã¿ã³ãã«é¢æ°ãå®è¡ããçºã®ãã®ã§ããäœã£ã颿°ã®ã³ãŒããã¯ãªãã¯ããŠããããã¿ã³ãæŒããšå®è¡ããããã忢ããŸããã³ãŒãã®åç·šéã¯åæ¢ãããŠããã§ãªããšåæ ãããŸããã
å³ïŒãããã³ãŒããæžãã¡ã€ã³ç»é¢ãExcelãåãã§ãã
ããã°ã©ãã³ã°ã®å
ããã°ã©ãã³ã°ã§é¢æ°ãäœãæã¯ã決ãŸã£ãåãšãããã®ããããŸãããããæ¯åäœãæã«ãã³ãã¬ãŒãã«ããŠäœãããšã«ãªããŸããããŸããªãã¿ãããªãã®ã§ãã颿°åãåŒæ°åã¯åºæ¬ããŒãåã§èšè¿°ããŸãã
- 颿°ã®å Žåã¯ãããªããPublic Function 颿°å()ã§å§ãã
- 颿°ã®çµããã¯ãEnd FunctionãšãªããŸãã1.ãš2.ã®éã«ã³ãŒããæžããŠãããŸãã
- 颿°ãªã®ã§åŠçããã ããããªããçããè¿ãå¿ èŠããããŸããã³ãŒãã®äžçªæåŸã«ã颿°å = çãããšãã£ãã³ãŒããæžããŸãã
- 颿°ãªã®ã§ãèšç®ããçºã®å€ãåãåãå¿ èŠããããŸãããããåŒæ°ãšåŒã³ãŸããããã¯1.ã®æ¬åŒ§å ã«èšè¿°ããŸããåŒæ°å As Variantãšæžãã°è¯ãã§ããããã«ã³ãã§åºåãã°ãããã§ãåŒæ°ã远å å¯èœã§ããïŒäŸïŒtomato颿°ãªãã° Public Function tomato(test1 As Variant, test2 As Variant) As VariantïŒ
- è¿ãçãã«ã4.åæ§ã«æåŸã«As Variantãšä»ããŠãããŸãã
- èšç®ãããçºã«ã¯ãäžæçãªå ¥ãç©ãå¿ èŠã§ããX = Y + Zãšããèšç®åŒãªãã°ãX, Y, Zããããã®å ¥ãç©ãçšæããŠãããªããã°ãããŸããããã®å ¥ãç©ã倿°ãšåŒã³ãŸããDim 倿°å As Variantãšãã£ã圢ã§çšæããŸãã
- 倿°ã«å€ãå ¥ããŠãããäœæ¥ãä»£å ¥ãšåŒã³ãŸãã倿°å = å€ããšãã£ãå ·åã«æ°åŒãæžãã ãã§ããããã§å€æ°åã«æå®ã®å€ãæ ŒçŽãããŸãã
- 7.ã®åŸã§èšç®ãããæã«ã¯ãçããæ ŒçŽãã倿° = 倿°å * 1.08ã¿ããã«å€æ°åã䜿ã£ãŠèšç®ãè¡ããŸãã
- äœã®åŠçãããŠããã®ããã³ã¡ã³ãã«æ®ãããæã¯ãã·ã³ã°ã«ã³ãŒããŒã·ã§ã³ãæé ã«ä»ãããšãã³ã¡ã³ãã«ãªããŸãã
- åäžãïŒã€ã³ãã³ãïŒããããšã³ãŒããèªã¿ããããªããŸããTABããŒã§åäžããå¯èœã§ãã
- Variantãªã©ã®ã¡ãœããåãªã©ãå ¥ãããšãå ¥åè£å®ãåºãŠããŸããéäžã§TABããŒãæŒãã°ãããéžæããŠå ¥åè£å®ãããŸãã
â»ãã®As Variantãããã¯å€æ°ã®åãšåŒã°ãããã®ã§ãããã¹ããªãã°Stringãæ°å€ãªãã°Integerãå°æ°ç¹ä»æ°å€ãªãã°Doubleãšãã£ãåãæå®ããå¿ èŠããããŸããã¡ãªã¿ã«ãVariantã¯äœã§ãåãšãªãã©ããªå€ãå ¥ããããŸããåã«å¿ããŠã¡ã¢ãªæ¶è²»éãéããŸãã
â»å€æ°ã«åãæå®ããçç±ã¯ãã¡ã¢ãªæ¶è²»éãå°ãªãããçºä»¥å€ã«ãããã®å€æ°ã«ã¯æ°å€ä»¥å€å ¥ããããªãã§ãããšããæååã ããèš±å¯ãããšãã£ãå¶éãå ããããšã§ããªã«ã·ãèšç®çµæãåºããªãããã«ããæå³ããããŸãã
å®éã«äœã£ãŠã¿ã
ããŠãå®éã«äœã£ãŠã¿ãŸããããä»åã¯ä»¥äžã®ãããªé¢æ°ãäœã£ãŠã¿ãŸãã
- é£å¡©æ°Žã®æ¿åºŠãèšç®ãã颿°
- åæã«é£å¡©ã®éããèšç®ããæ©èœãä»ãå ãããïŒåŒæ°ã®å€ã§åå²ïŒ
- 1.ã®å Žåã«ã¯ã1ã€ç®ã®åŒæ°ã¯ãæ°Žã®éãããïŒã€ç®ã®åŒæ°ã¯ã溶液ã®éãããšãã
- 2.ã®å Žåã«ã¯ã1ã€ç®ã®åŒæ°ã¯ãæ¿åºŠããïŒã€ç®ã®åŒæ°ã¯ã溶液ã®éãããšãã
- 1.ãš2.ã®åŠçãåå²ãããã©ã°ãšããŠã0ãªãã°1.ã®åŠçã1ãªãã°2.ã®åŠçãå®è¡ããããã«ãã
- 5.ã®çºã®åŒæ°ãçšæããŠãã
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
'é£å¡©æ°Žã®æ¿åºŠã®èšç®ããã³é£å¡©ã®éããèšç®ãã颿° 'ãã©ã°ã0ãªãã°é£å¡©æ°Žã®æ¿åºŠã®èšç®ã1ãªãã°é£å¡©ã®éããèšç®ãã Public Function soltman(value As Variant, youeki As Variant, flg As Variant) As Variant '倿°ãèšå®ãã Dim answer As Variant 'æ¿åºŠã®èšç®ãªã®ãïŒéãã®èšç®ãªã®ãïŒ If flg = 0 Then 'æ¿åºŠã®èšç®ãè¡ã(åŒæ°valueã¯é£å¡©ã®éã) answer = value / youeki * 100 Else 'é£å¡©ã®éãã®èšç®ãè¡ãïŒåŒæ°valueã¯æ¿åºŠïŒ answer = value / 100 * youeki End If 'çããè¿ã soltman = answer End Function 'äžèšã®é¢æ°ããã¹ãããããã®é¢æ° Public Sub test() Debug.Print soltman(30, 100, 0) End Sub |
ïŒã€ã®é¢æ°ã§ãã©ã°åå²ã§2ã€ã®èšç®ãå®çŸãã颿°ãããã§å®æããŸãããPublicã«ããŠãããšã©ãããã§ãåŒã³åºããå¯èœã§ããPrivateã«ãããšãã®é¢æ°ãæžããã¢ãžã¥ãŒã«å ããã ãåŒã³åºããå¯èœã§ãã
颿°ã§å°äžã€åŒæ°ãåã£ãŠããã®ã§ããã®ãŸãŸã§ã¯é¢æ°ã®å®è¡ãã¹ããåºæ¥ãŸãããããã§ãtestãšããäžã«ãããsubããšãããã®ã§ãããã°ã©ã ãçµãã§ããŸãïŒããã¯é¢æ°ã§ã¯ãªããSubããã·ãŒãžã£ãšåŒã°ãããã®ã§ããæ¬æ¥ããã°ã©ã ã¯Subã䜿ããŸãïŒDebug.Printã«ãŠsoltmanãšããèªåãäœã£ã颿°ãåŒã³åºããã30ãš100ããããŠ0ãšãã3ã€ã®åŒæ°ãæž¡ããŠããŸããèšç®çµæã¯ãã€ããã£ãšã€ãã®äžã«è¡šç€ºãããŸãïŒçãã¯é£å¡©æ°Žã®æ¿åºŠã¯ã30(%)ãšããçãããã€ããã£ãšã€ãã«åºãŠããŸãïŒ
å³ïŒãããªæãã§é¢æ°ãäœã
ã¯ãšãªã§äœ¿ã£ãŠã¿ã
ããŠãä»åäœã£ããã®ç¬èªã®é¢æ°ãããã°ã©ã å ã§ãåŒã³åºããŸãããå®ã¯ã¯ãšãªã§ãå©çšãåºæ¥ãŸããID, é£å¡©ã®éã, 溶液ã®éããšãã3ã€ã®åã§åºæ¥ãããŒãã«ã§ã¯ãšãªãäœãã4ã€ç®ã«æ¿åºŠãšããåãèšããŸãããã®åã¯ä»åã®é¢æ°ã§äœã£ãã¯ãšãªäžã®åã«ãªããŸãã
- ã¯ãšãªç·šéç»é¢ã«ãŠã3ã€ã®åããŸãã¯è¿œå ã4ã€ç®ã®åã®ãã£ãŒã«ãã«ãŠå³ã¯ãªãã¯âãã«ããã¯ãªãã¯
- ãã€ã¢ãã°å ã«åã®ååãšã:ããèšè¿°
- ç¶ããŠãæ°åŒãšããŠãsoltman([é£å¡©ã®éã],[溶液ã®éã],0)ãšèšè¿°ããã[]ãå«ããŠãã£ãŒã«ãåãèšè¿°ããç¹ã«æ³šæãããã§åã®å€ãåŒæ°ã«æå®ããŠããäºã«ãªããŸãã
- ã¡ãªã¿ã«ãè€æ°ã®ããŒãã«ãã¯ãšãªäžã«ããå Žåã«ã¯ã[ããŒãã«å].[ãã£ãŒã«ãå]ã§æå®ããŠãããŸãã
- é£å¡©æ¿åºŠèšç®ãšããååã§ã¯ãšãªãä¿åããã
- å®éã«ããŒãã«ã«å€ãå ¥ããŠããã®ã¯ãšãªãéããŠã¿ããšããã¡ããšé£å¡©æ°Žã®æ¿åºŠãèšç®ãããŠããŸãã
å³ïŒãããªæãã§æ°ããåã¯æ°åŒã§äœã
å³ïŒç¡äºã«èªäœé¢æ°ã§æ¿åºŠã®èšç®ãåºæ¥ã
ç°¡åãªåŠçãæžããŠã¿ã
æŠèŠ
ããŠããããŸã§ã§Accessã®ãã¯ãæ©èœãšèªäœã®ãŠãŒã¶å®çŸ©é¢æ°ããã³ããã䜿ã£ãã¯ãšãªã§ã®èšç®ãå®çŸããŸããããã®ææ³ã ãã§ãAccessã®å©çšç¯å²ã¯æ Œæ®µã«åºãããŸããã³ãŒããåæ°è¡çšåºŠã§ãã®ã§ããã¬ãŒãã³ã°ã«ãæã£ãŠããã§ãã
ä»åºŠã¯åŠçãæžããŠã¿ãŸããããããã¯æ¬æ Œçãªããã°ã©ãã³ã°ãšãªããŸãããââãããããã«ã¯ïŒããâ â ãå®çŸããæ¹æ³ã¯ïŒããšãã£ã課é¡ã«å¯ŸããŠä»åŸã¯ãã²ãšã€ã²ãšã€åŒãåºããå¢ãããŠããäºã«ãªããŸããVBAã¯ãã§ã«20幎以äžå©çšãããããããã®è³æãã³ãŒãããããäžã«å ¬éãããŠããã®ã§ãå¿ ããã1ããäœãå¿ èŠã¯ãããŸããã
ä»åã¯ãã®ç¬¬äžæ©ã«ãªããŸãã
ã³ãŒããæžãçºã«åŠçãæŽçãã
ä»åã¯æããã䜿ãã·ãŒã³ãããã§ããããã®ã®ïŒã€ãšããŠããã¡ã€ã«éžæãã€ã¢ãã°ã§xlsxãã¡ã€ã«ãéžæãããªã³ã¯ããŒãã«ã貌ã£ãŠãäžã®ããŒã¿ãããŒãã«ã«åã蟌ãããšãããã®ããã£ãŠã¿ãããšæããŸãããã®åŠçã¯ä»¥äžã®ãããªå¡ã«åãããããšæããŸãã
- ãã¡ã€ã«éžæãã€ã¢ãã°ã§xlsxãã¡ã€ã«ãéžæãããã¡ã€ã«ã®ãã¹ãååŸãã
- ãªã³ã¯ããŒãã«ã貌ãïŒxlsxèªäœãèªã¿åãå°çšã®ããŒãã«åãããã®ïŒ
- 2.ãå ã«ããŒãã«ã«å¯ŸããŠã®è¿œå ã¯ãšãªãçšæããããVBAããå®è¡ãã
- 远å ããããããªã³ã¯ããŒãã«ãè§£é€ãã
ãã®åŠçã ãã§ãããã ãã®ã³ãŒããèšè¿°ããªããã°ãªããªããšæããšã倧å€ãããã£ãŠæããããããŸããããããããã³ãŒãã¯å€ã§ãã¡ãã£ãšæ¹å€ããã ãã§æµçšãåºæ¥ãã®ã§ã倧å€ãªã®ã¯æåã®1åã ãã§ãïŒãããã£ãæµçšåºæ¥ãã³ãŒãã®å¡ããã³ãŒãã¹ãããããšåŒã³ãŸãïŒãããããã³ãŒãã®æçãããã€ãçšæããŠããããšã§ãåãéããæ¯ã«ããã°ã©ã äœæé床ã¯ã©ãã©ãåäžããŠãããŸãã
äºåæºå
åç §èšå®ã远å ãã
ä»åã®ã³ãŒãã§ã¯ãAccessæšæºã®æ©èœã§ã¯ãªããOfficeèªäœãæã£ãŠãæ©èœãå©çšãããŠããããŸãããªã®ã§ãã³ãŒãæžãåã«ãåç §èšå®ããšãããã®ãããå©çšããã¢ãžã¥ãŒã«ãéžæããå¿ èŠããããŸãããããã¢ãŒãªãŒãã€ã³ãã£ã³ã°ãšåŒã³ãŸãã
- Visual Basicãéã
- ã¡ãã¥ãŒãããããŒã«ãâãåç §èšå®ããéããŸãã
- èªåã®å Žåã¯Microsoft Office 15.0 Object Libraryãããã«ãªããŸããã䜿ãã®ç°å¢ã«ãã£ãŠã15.0ã®éšåãç°ãªãã®ã§ãããã£ãœããã®ãèŠã€ããŸãã
- ããã«å¯ŸããŠãã§ãã¯ãå ¥ããŠãOKãæŒããŸãã
- ããã§å®äº
å³ïŒãã®ã©ã€ãã©ãªã¯çµæ§ããå©çšããŸãã
ã¯ãšãªãçšæããŠãã
ä»åã®ã³ãŒãã§ã¯ãã€ã³ããŒãããŒã¿ãšããxlsxãã¡ã€ã«ã«å¯ŸããŠãã€ã³ããŒãããŒã¿ãšããã·ãŒãåã®ã·ãŒããtestxlsxãšããååã§ãªã³ã¯ããŒãã«ã貌ããŸãããã®ããŒã¿ãã€ã³ããŒãããŒã¿ãšããããŒãã«ã«å ¥ããã®ã§ãã³ãŒããæžãåã«äºåã«ä»¥äžã®æé ã§xlsxãã¡ã€ã«ã«å¯ŸããŠãªã³ã¯ããŒãã«ã貌ã£ãŠã¯ãšãªãäœã£ãŠãããŸãããã
- Accessã®å€éšããŒã¿ã¿ãã«ããã€ã³ããŒããšãªã³ã¯ã®ãExcelããã¯ãªãã¯
- ãã€ã¢ãã°ãåºãŠããã®ã§ãã€ã³ããŒãããŒã¿ãšããååã®xlsxãã¡ã€ã«ãéžæãã
- ãªã³ã¯ããŒãã«ãäœæããŠãœãŒã¹ããŒã¿ã«ãªã³ã¯ãããéžæããŠãOKãæŒãã
- 次ã®ç»é¢ã§ã¯ãå é è¡ããã£ãŒã«ãåãšããŠå©çšããã«ãã§ãã¯ãèªåã§å ¥ã£ãŠããšæãã®ã§ã次ãžãã¯ãªãã¯
- ãªã³ã¯ããŒãã«åã¯testxlsxãšãã
- testxlsxãšããããŒãã«ãåºçŸããã®ã§ãããã䜿ã£ãŠãã€ã³ããŒãããŒã¿ããŒãã«ã«ããŒã¿ã远å ããã远å ã¯ãšãªããäœãã
- 远å ã¯ãšãªã®ååã¯ããŒã¿è¿œå ã¯ãšãªãšããIDã®åã ãã¯å ¥ããªãã
- ãŸããã€ã³ããŒãããŒã¿ããŒãã«ã®ããŒã¿ãåé€ããåé€ã¯ãšãªãäœã£ãŠãããæ¯åæŽãæ¿ãã§åã蟌ãçºïŒããããªããšãã©ãã©ãããŒãã«ã«ããŒã¿ã远å ãããŠããŸãïŒãååã¯ãã€ã³ããŒãããŒã¿åé€ãšãã
- ããã§æºåãå®äºããã®ã§ãtestxlsxãå³ã¯ãªãã¯ããŠåé€ãããªã³ã¯ããŒãã«ãè§£é€ããã
4.ã®ç»é¢ã§è€æ°ã®ã·ãŒããããå Žåã·ãŒããéžæããé ç®ããããšæãã®ã§ããããéžãã§ããå¿ èŠããããŸãããã®ãŸãŸé²ããš1ã€ç®ã®ã·ãŒãã«ãªã³ã¯ããŒãã«ã貌ãããŠããŸããŸãã
å³ïŒãªã³ã¯ããŒãã«äœæç»é¢
å³ïŒtestxlsxãšããããŒãã«ãåºæ¥ãïŒèªã¿åãå°çšïŒ
å³ïŒIDå以å€ã®ããŒã¿ã远å ãã远å ã¯ãšãªãäœã
å®éã«ã³ãŒããæžããŠã¿ã
æåã¯äœãããŠããã®ãããµãããªãªã³ãŒãã§ãã£ãŠããããããããã£ããçŽè§£ããŠè¡ããšãã ãã ãäœãããŠããã®ããããã£ãŠããŸãããŸãããããç¹°ãè¿ãäºã§èªç¶ãšããããã®ã³ãŒããæžããããã«ãªã£ãŠããŸãããŸããäžåºŠæžããŠå¿ããŠãããã®å¡ãäœãããŠãããã®ãªã®ãããçè§£åºæ¥ãŠããã°ãã³ããã§ãååããã°ã©ã ã¯æžããŸãã
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
'ãã¡ã€ã«éžæãã€ã¢ãã°ã衚瀺ããŠãã¡ã€ã«ãéžæãã Public Sub selectbox() '倿°ãçšæãã Dim strpath As String 'ãã¡ã€ã«ã®ãã¹ãæ ŒçŽãã Dim ret As Integer 'åã蟌ã¿åŠçããã®åŠççµæãåãåã 'ãã¡ã€ã«ãéããã€ã¢ãã°çšã®å€æ° Dim dlg As Object 'ãã¡ã€ã«éžæãã€ã¢ãã°ã®æºå Set dlg = Application.FileDialog(msoFileDialogSaveAs) 'ãã¡ã€ã«éžæãã€ã¢ãã°ããªãŒãã³ãã With Application.FileDialog(msoFileDialogFilePicker) 'è€æ°éžæã¯ã§ããªãããã«èšå®ãã .AllowMultiSelect = False 'ãã¡ã€ã« ãã€ã¢ãã° ããã¯ã¹ã®ã¿ã€ãã«èšå® .title = "Excelãã¡ã€ã«ã®ã€ã³ããŒã" '衚瀺ãããåæãã¹ãŸãã¯ãã¡ã€ã«åãèšå® .InitialFileName = CreateObject("WScript.Shell").SpecialFolders.Item("Desktop") & "\" 'åæãã¥ãŒãèšå®(ããŒãžã§ã³ã«ãã£ãŠç¡èŠããã) .InitialView = msoFileDialogViewWebView 'xlsx圢åŒã ããéžæã§ããããã«ãã£ã«ã¿ãã With .filters .Clear .Add "", "*.xlsx" End With 'Showã宣èšããæã§ãã€ã¢ãã°ãéããããæ»ãå€ã¯ä»¥äžã®éã 'ãã¡ã€ã«ããã©ã«ããéžæãâãTrue 'ãã£ã³ã»ã«ãæŒããããâãFalse If .Show = True Then strpath = .SelectedItems(1) Else strpath = "" MsgBox "ãã£ã³ã»ã«ãããŸããã" Exit Sub End If End With 'å®éã«åŠçãæ
åœãã颿°ïŒimportxlsxïŒã«æž¡ããŠåŠççµæãåãåã ret = importxlsx(strpath) 'è¿ã£ãŠããã®ã§ã³ãŒãå¥ã«ã¡ãã»ãŒãžã衚瀺 If ret = 0 Then 'ãšã©ãŒãè¿ã£ãŠããã®ã§ããšã©ãŒã衚瀺 MsgBox "ãšã©ãŒã¿ããã§ããã" Else 'ç¡äºã«åŠçãå®äºããã®ã§ãåŠçå®äºã¡ãã»ãŒãžã衚瀺ãã MsgBox "åŠçã¯ç¡äºã«å®äºããŸããã" End If End Sub 'ãã¡ã€ã«ã®ãã¹ãããšã«ãªã³ã¯ããŒãã«ã貌ããã€ã³ããŒãããããŠè§£é€ãã Public Function importxlsx(path As Variant) As Variant 'ãšã©ãŒãçºçãããææãã On Error GoTo error_importxlsx 'testxlsxãšããååã§ãªã³ã¯ããŒãã«ãæ¥ç¶ãã 'xlsxã®ã€ã³ããŒãããŒã¿ãšããã·ãŒãããªã³ã¯ãã DoCmd.TransferSpreadsheet acLink, , "testxlsx", path, True, "ã€ã³ããŒãããŒã¿!" '远å ã¯ãšãªãå®è¡ãã DoCmd.SetWarnings False 'èŠåãã€ã¢ãã°ãäžæçã«OFF DoCmd.OpenQuery "ã€ã³ããŒãããŒã¿åé€" 'ããŒã¿åé€ã¯ãšãªãå®è¡ããŠããŒãã«ããŒã¿ãã¯ãªã¢ããŠãã DoCmd.OpenQuery "ããŒã¿è¿œå ã¯ãšãª" 'ããŒã¿åã蟌ã¿ã®è¿œå ã¯ãšãªãå®è¡ãã DoCmd.SetWarnings True 'èŠåãã€ã¢ãã°ãäžæçã«ONã«æ»ã 'ãªã³ã¯ããŒãã«ãè§£é€ãã DoCmd.DeleteObject acTable, "testxlsx" 'ç¡äºã«å®äºããã®ã§æåãšããŠåŒã³åºãå
ãžè¿ã importxlsx = 1 Exit Function error_importxlsx: 'ãšã©ãŒãšããŠåŒã³åºãå
ãžè¿ã importxlsx = 0 Exit Function End Function |
- selectboxãšããsubãä»ååŒã³åºãåŠçãããããã¿ã³ããåŒã³åºããããã«ãã¿ã³ã®ã¯ãªãã¯æã€ãã³ãã«èšè¿°ãå¿ èŠã§ãã
- éåžžã¯ãã¯ãã®äœæã§ãããã·ãŒãžã£ã®å®è¡ãâ颿°åãšå ¥ããŠããã¡ã€ã«ã®åã蟌ã¿ãšããååãä»ããããã®åŸãã¿ã³ã«ãã®ãã¯ããå²åœãŠãŠäžããã°OKã§ããäœãä»åã®ã³ã¬ã¯ã颿°ãã§ã¯ãªãSubãªã®ã§ãã³ãŒããçŽæ¥ãã¿ã³ã«æžããŸãã
- ãã¿ã³ã®ãã¯ãªãã¯æããšããéšåã§ãâŠããã¯ãªãã¯ããã³ãŒããã«ããŒãéžæããŸãã
- Visual Basicã®ç»é¢ã§ã¯1è¡ã ããcall selectboxããšã ãèšè¿°ããã°ãSubãåŒã³åºããŸãã
- ã¡ã€ã³ã®åŠçã¯ãã¡ã€ã«ãžã®ãã¹ãåãåã£ãimportxlsxãåŒãç¶ããŸãããªã³ã¯ããŒãã«ã貌ããããŒã¿ã®åé€ãããŒã¿ã®è¿œå ããªã³ã¯ããŒãã«ã®è§£é€ãæ åœããŸãã
- importxlsxããè¿ã£ãŠããå€ãå ã«å ã®selectboxã§ã¯ã¡ãã»ãŒãžã衚瀺ããŸãããšã©ãŒæãšããã§ãªãæã0ãš1ã§å€å®ãããŠããŸãã
ã³ãŒãã®è§£èª¬
ãã¡ã€ã«ã®éžæãã€ã¢ãã°ãšãã®ãªãã·ã§ã³
selectboxã®ã¯ããã®ã»ãã§ã¯ãäœããåªæã®ãããªã³ãŒããæžããŠãããŸãããããã¯ããããããã®ããšã ãèŠããŠããã°è¯ããšæããŸãããããããããŸããªãããšãããã®ã§ãå®åæã§ããããã§ãã¡ã€ã«ã®éžæãã€ã¢ãã°ãåŒã³åºããŠããŸããŸãã
次ã«With Application.FileDialog(msoFileDialogFilePicker)以äžã§ãããããã¯ããã¡ã€ã«éžæãã€ã¢ãã°ã«å¯Ÿãããªãã·ã§ã³èšå®ãä»åã¯
- è€æ°ãã¡ã€ã«ã®éžæã¯ãªãã«ãã
- ãã€ã¢ãã°ã®ã¿ã€ãã«åãèšå®ãã
- ãã€ã¢ãã°ãéãäžçªæåã®ãã©ã«ãããã¹ã¯ãããã«ããïŒã³ãŒãéšåããããããã®ã ãšæã£ãŠãã ããïŒ
- initialviewã¯ãã¡ã€ã«éžæãã€ã¢ãã°ãšããæå®ã«ããŠããŸãïŒä»ã«ããã©ã«ãéžæãã€ã¢ãã°ãšãããã®ãããã®ã§ïŒ
- xlsxãã¡ã€ã«ä»¥å€ã¯éžæã§ããªãããã«æ¡åŒµåã§ãã£ã«ã¿ãããŠããŸããŸãïŒéžæãã€ã¢ãã°ã«ã¯ãxlsx以å€åºãŠããŸããïŒ
- ãã¡ã€ã«ãéžæãããšãstrpathã«ãã®ãã¡ã€ã«ãžã®ãã¹ãæ ŒçŽãããŸãã
- ãããæ¬¡ã®åŠçã§ããimportxlsxã®åŒæ°ã«æž¡ããŠãè¿ã£ãŠããã®ãåŸ ã¡ãŸãã
- importxlsxããåŠççµæãè¿ã£ãŠãããå 容ãå ã«å€å®ããŠã¡ãã»ãŒãžã衚瀺ããŸãã
ãªã³ã¯ããŒãã«ã貌ã
ããã¯ãšãŠãç°¡åããã¡ã€ã«ã®ãã¹ã¯selectboxããéãããŠããã®ã§ã以äžã®ãããª1è¡ãæžãã ãã§ãªã³ã¯ããŒãã«ãèšå®ãããŸããäœãåãååã®ãªã³ã¯ããŒãã«ãããå Žåã«ã¯ãtestxlsx1ãšãã£ãããã«æ°åãã€ããŠå¥ã®ããŒãã«æ±ãã«ãªã£ãŠããŸãã®ã§ãå¿ ãtestxlsxãç¡ãç¶æ ã«ããªããšå ·åãæªãã§ãã
DoCmd.TransferSpreadsheet acLink, , "testxlsx", path, True, "ã€ã³ããŒãããŒã¿!"
ãã®1è¡ã§ãpathã«å ¥ã£ãŠããxlsxãã¡ã€ã«ãžã®ãã¹ããã€ã³ããŒãããŒã¿ãšããã·ãŒãåïŒæåŸã«!ãä»ããã®ããçŽæïŒãtestxlsxãšããååã®ãªã³ã¯ããŒãã«ã«ããããšããåŠçãäžæ°ã«è¡ãããŸãã
ã¯ãšãªãå®è¡ãã
VBAããäœã眮ãã®ã¯ãšãªãå®è¡ããäºãå¯èœã§ããäœããDoCmd.SetWarnings Falseãå®è¡ããŠãããªããšæ¯åã远å ããŸããïŒãçãªã¡ãã»ãŒãžãåºãã®ã§ãèªåã®å Žåã¯DoCmd.SetWarnings Falseãä»ããŠããŸãã
ã¯ãšãªã®å®è¡èªäœã¯ã以äžã®ãããªæžãæ¹ã§ç°¡åã«å®è¡ãããŸãã
DoCmd.OpenQuery "å®è¡ããã¯ãšãªå"
ãããŠå®è¡åŸã«ã¯ãDoCmd.SetWarnings TrueãšããŠèšå®ãå ã«æ»ããŠããŸãã
ãªã³ã¯ããŒãã«ãè§£é€ãã
ãªã³ã¯ããŒãã«ã®è§£é€ãããŠãããŸããããããããªããšã次å以éã©ãã©ãããããªãªã³ã¯ããŒãã«ãããŒã¿ããŒã¹ãã¡ã€ã«å ã«ã§ããŠããŸããŸãã以äžã®1è¡ãæžãã ãã§ãã
DoCmd.DeleteObject acTable, "testxlsx"
ããã§testxlsxãšãããªã³ã¯ããŒãã«ãåé€ãããŸããå°ããã®åŠçã¯ããšã©ãŒãçºçããå Žåã«ããªããŠã次é ã®ãšã©ãŒãã³ããªã³ã°ãšãããšããã«ãèšè¿°ããŠãããšå°è¯ãã§ãããã
selectboxãžå€ãè¿ã
ç¡äºã«æåŸãŸã§åŠçãå®äºããããimportxlsx = 1ãšããŠå€ãè¿ããŠããŸããäœãããã®ã³ãŒãã®ã©ããã§ãšã©ãŒãçºçãããšãselectboxã¯å€ãåãåããã«çµãã£ãŠããŸããŸããããã§ããšã©ãŒãã³ããªã³ã°ãšåŒã°ããã³ãŒãã远å ããŸãïŒäœããã®ã³ãŒãã¯ãã³ãŒããç·šéäžã¯ã³ã¡ã³ãã¢ãŠãããŠãããã»ããè¯ãã§ããçç±ã¯ãšã©ãŒã§æ¢ãŸã£ãŠãããªãããããšã©ãŒå 容ãããããªããªãããã§ãïŒã
ä»åã®importxlsxã®ã³ãŒãã§ã¯ãåé ã«On Error GoTo error_importxlsxãšããã³ãŒããå ¥ã£ãŠããŸãããããããšã©ãŒãã³ããªã³ã°ã®ã³ãŒãã§ããããã¯ããšã©ãŒãèµ·ããããerror_importxlsxãšããè¡ãŸã§ãžã£ã³ããããšããã³ãŒãã§ãããžã£ã³ãå ã§ã¯ãimportxlsx = 0ãšãã圢ã§å€ãå€ããããŠããã®ã§ããšã©ãŒã§æ¢ãŸãããšãªããselectboxåŽã§å€ãåãåã£ãŠå€å®ãå¯èœã§ãã