ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¦p¦ó§Q¥ÎVBA¨Ó¹F¨ì "³sµ²" ®ÄªG?

¦p¦ó§Q¥ÎVBA¨Ó¹F¨ì "³sµ²" ®ÄªG?

°ÝÃD
¦p¦ó§Q¥ÎVBA¨Ó¹F¨ì "³sµ²" ®ÄªG?

¸Ô²Ó°ÝÃD¤º®e
¨Ï¥ÎÀɪº¤u§@ªía ©M ­×§ïÀɪº¤u§@ªíA¡A¤º®e¬O§¹¥þ¤@¼Ò¤@¼Ë¡A
¦]¬°¬O§Q¥Î='C:\Documents and Settings\®à­±\§Q¥ÎVBA§ó·s³sµ²\­×§ï¸ê®Æ§¨\[­×§ïÀÉ.xls]¤u§@ªíA'!A1
ªº¤½¦¡¨Ó³sµ²¨â­Ó¤£¦P¸ô®|ªº¬¡­¶Ã¯¤u§@ªí¤º®e¡A
¤£ª¾¦³¨S¿ìªk§ï§Q¥ÎVBA¨Ó¹F¨ì³oºØ®ÄªG¡A(VBA¸Ì­±½ÐÀ°§Úµù©ú­×§ï¸ô®|ªº¦a¤è)¡A
§Ú·Q­n¥´¶}¬¡­¶Ã¯´N·|¦Û°Ê§ó·s­×§ïÀɪº³sµ²¤º®e¦Ó¶Ç¨ì¨Ï¥ÎÀÉ¡F
¥t¥~¦pªG¨S¦³¤º®e¡A¥i¥H¤£­nÅã¥Ü0¦Ó¬Oºû«ùªÅ¥Õ¶Ü?

¥t¥~¦pªG¹³¬O¤u§@ªíB & b¡A¦³­­¨î½d³òªºÅª¨ú¨Ó·½¡AVBA¤S¸Ó¦p¦ó¼g©O?

¥H¤W°ÝÃD¡A½Ð¤j¤j«ü±ÐÀ°¸Ñ¤F¡IÁÂÁ¡I

¤U¸ü»¡©úÀÉ
¦p¦ó§Q¥ÎVBA§ó·s³sµ².rar (9.36 KB)

°ÝÃD
¦p¦ó§Q¥ÎVBA¨Ó¹F¨ì "³sµ²" ®ÄªG?

¸Ô²Ó°ÝÃD¤º®e
¨Ï¥ÎÀɪº¤u§@ªía ©M ­×§ïÀɪº¤u§@ªíA¡A¤º®e¬O§¹¥þ ...
RCRG µoªí©ó 2016-1-11 13:51



    ²³æ¨ÓÁ¿´N¬O¡A¬O§_¯à§Q¥ÎVBA¨Ó½Æ»s "¥t¤@­Ó ¬¡­¶Ã¯ªº¤u§@ªí" ¤º®e¡A¦Ó¥B¬O¨C¶}±Ò¬¡­¶Ã¯´N°õ¦æ¤@¦¸½Æ»s¤u§@ªíªº°Ê§@¡C

TOP

¦^´_ 1# RCRG


    ¶ã¶ã...! Ãø¹D¥u¯à§Q¥Î ¤u§@ªía'A1=¤u§@ªíA'!A1 ªº¤½¦¡¤âªk¨Ó³sµ²¨â¤u§@ªíªº¤º®e¶Ü? ÁÙ¬O¦³¬Æ»ò¦a¤è¬O§Ú±Ô­z¤£²Mªº¡A¨DVBA°ª¤â¸Ñ´b¡IÁÂÁ¡IQQ

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2016-1-15 14:32 ½s¿è

¦^´_ 3# RCRG
¦pªG¥u¬O­n³s½u¨ä¥LEXCEL¤ºªºTable
¥i¥Î ¸ê®Æ>(¨ú±o¥~³¡¸ê®Æ)±q¨ä¥L¨Ó·½>±qMicrosoft Query>
Excel files
«Ø¥ß¸ÓTableªº³s½u

https://support.office.com/zh-TW ... 3-9c38-4c62f252da2e
«Øij§A¦h¬Ý¬ÝOFFICEªº»¡©ú

¦ý¬O³o¸ò§A2#»¡ªº½Æ»s¶K¤W¬O¨â¦^¨Æ
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 4# stillfish00


    §Úªº¥Î·N¥u¬O­n¯à "³z¹L¬¡­¶Ã¯A¥h­×§ï¬¡­¶Ã¯B"¡A¦]¬°¬¡­¶Ã¯B¬O¼gµ¹¨Ï¥ÎªÌ¦b¥Îªº¡A©Ò¥H¥L­Ì¥i¯à·|½Æ»s¥h¨Ï¥Î¦Ó¥B©ñ¦b¥ô¦ó¸ô®|¡A¤£¹L¨SÃö«Y¡A§Ú¥u­n¦b¬¡­¶Ã¯B¼g¤JVBA(©ÎªÌ¹³¥Ø«e¥Î³sµ²§ó·sªº¤è¦¡)¡A´N¯àÀH®É³z¹L¬¡­¶Ã¯A¶¡±µ¹F¨ì­×§ï¬¡­¶Ã¯Bªº»Ý¨D¡F
¤]¦]¬°¬¡­¶Ã¯A©M¬¡­¶Ã¯B¨âªÌ¤º®e·|¤@¼Ò¤@¼Ë¡A©Ò¥H§Ú¤~·|¥Î¨ì "½Æ»s" ¦r²´¡A¤£¤Ó·|§Î®e»Pªí¹F¡A¤ï¶Õ¡IXD
§Ú¥u¬O¦b·Q¡A°£¤F¥Î§ó·s³sµ²¤è¦¡¡A¬O§_¯à³z¹LVBA¡A©ÎªÌ¦³§óÁo©úªº¿ìªk©O? ¦]¬°·Pı§ó·s³sµ²¸ê®Æ¶q·|¤ñ¸û¤j¡A²¦³º¥i¯à¬O´X¤d¦Cªº¤º®e¡C

TOP

¦^´_ 5# RCRG


    Microsoft Query¸ê®Æ³sµ²ªº¤è¦¡¦Û¤v§Ë¤F¥b¤ÑÁÙ¬O¤£·|¥Î¡A³£·|¥X²{ "¦¹¸ê®Æ¨Ó·½¤¤¨Ã¥¼¦s¦b¥i¨Ï¥Îªºªí®æ" ¡A©Ò¥H³o¤è­±´N©ñ±ó¤F...XD
µw¬Oµ¹¥L¥Î ¤u§@ªía'A1=¤u§@ªíA'!A1 ªº¤½¦¡¨Ó³sµ²¨â­Ó¬¡­¶Ã¯ªº¸ê®Æ¡A¹ê´úµ²ªG´N¬O¶}ÀɺC¡B¦sÀɤ]ºC¡A¨C¦¸®É¶¡´N¬O3¡B40¬í°_¸õ¡F
¸ÕµÛ¦Û¤v¦b"¨Ï¥ÎÀÉ"¿ý¥¨¶°¡A¦ý¬O"­×§ïÀÉ"¤@Ãö³¬Àɮשβ¾°Ê¸ô®|¡A¥¨¶°´N§¹¥þ¨S¥Î¤F¡I...¨D§UµLªù¤F....QQ

Sub ¥¨¶°1()
'
' ¥¨¶°1 ¥¨¶°
'

'
    Windows("­×§ïÀÉ.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("¨Ï¥ÎÀÉ.xls").Activate
    Cells.Select
    ActiveSheet.Paste
End Sub

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2016-1-23 23:51 ½s¿è

¦^´_ 6# RCRG
  1. 'VBAµ{¦¡©ñ¦b ¨Ï¥ÎÀÉ.XLS¤¤ ThisWorkbook
  2. Private Sub Workbook_Open()
  3.     xF = "'C:\Documents and Settings\®à­±\§Q¥ÎVBA§ó·s³sµ²\­×§ï¸ê®Æ§¨\[­×§ïÀÉ.xls]¤u§@ªíA'!R1C1:R999C7"
  4.     Workbooks("¨Ï¥ÎÀÉ.xls").Worksheets("¤u§@ªía").Range("A1:G999").FormulaArray = "=if(" & xF & "="""",""""," & xF & ")"
  5. End Sub
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089


    ÁÂÁÂML089¤j¤jªº¸Ñµª¡A§Ú­nªº§Î¦¡´N¬O³o¼Ë¡Aµ¥µ¥¦A´ú¸Õ¬Ý³z¹L±zªºVBA©M§Ú¥»¨Óªºª½±µ¤½¦¡³sµ²¡Aµ{¦¡¶]°_¨Ó·|¤£·|ÁÙ¬O«Üªá®É¶¡¡F
¹ï¤F¡I©Ò¥H³o¬O¶}Àɽƻs¥t¤@­Ó¬¡­¶Ã¯ªº"¼Æ­È"§a¡A¦]¬°¦rÅé(Àx¦s®æ)ÃC¦â¡B¦rÅé¤j¤p¡B®æ¦¡¡B¬Æ¦Üµù¸Ñ¦n¹³³£µLªk½Æ»s¡I

TOP

¦^´_ 7# ML089


    ½Ð±Ð¤@¤UM¤j¡A§Ú§â±zªº½d³ò"A1:G999"§ï¦¨¨ä¥L½d³ò(¦p¡G"A1:BB500" ©Î "U1:CC300"..µ¥)¡A¦n¹³´N·|¥X²{¿ù»~¡A
¦Û¤v¬ã¨s¤F¤@¤U¦n¹³¬O¤W­±³o­ÓR1C1:R999C7¤]­n¸òµÛ­×§ï¨S¿ù§a¡A¥i¬O§Ú¤£À´R1C1:R999C7¬O¤°»ò·N«ä¡A¤£ª¾¯à§_»¡©ú¤@¤U¡F
©ÎªÌ°£¤FR1C1:R999C7¡AÁÙ¦³¬Æ»ò¦a¤è¤]»Ý¤@°_­×§ï©O?

TOP

¦^´_ 9# RCRG

R1C1:R999C7

R1:²Ä1¦C
C1:²Ä1Äæ
µ¥©ó A1

R999:²Ä999¦C
C7:²Ä7Äæ
µ¥©ó G999

U1:CC300 = R1C21:R300C81
COLUMN(U1)=21
COLUMN(CC300)=81
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD