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

[µo°Ý] ²Ä¤@¦¸¾Ç¥ÎVBA§ì¨ú¸ê®Æ ¹J¨ì¤@¨Ç°ÝÃD

[µo°Ý] ²Ä¤@¦¸¾Ç¥ÎVBA§ì¨ú¸ê®Æ ¹J¨ì¤@¨Ç°ÝÃD

«e¨¥: ¤p§Ì§Ú¦³¸ÕµÛ¥Î²³æªºiqy¥h¼¶¼g¤]¦¨¥\¤F,¦ý¥u­n¶×¤J4­Ó¥H¤W(§t),µ{¦¡´N·|¶]¥XµLªk§ì¨úweb¸ê®Æ¨S¿ìªk¥u¦n¦A¨Ó¦Û¾Çvba

¥H¤U¬O§Úª¦¤F3¤Ñªº¦¨ªG  ¥H¤Uµ{¦¡½X³£¬O¥Î¿ý»s¨úªº Á`¦@¦³5­Ó°Ê§@»Ý­n°µ  «e­±¥|­Ó§ì¥X¨Ó³£¬O¶Ã½X¦ý¬O­«­nªº¼Æ­È³£¦³ºI¨ú¥X¨Ó,¦ý¬O¥L³£·|¦b´¡¤J¦æ¦b¹Bºâµ²ªG «Ü§xÂZ§Ú

³Ì«á¤@¬qµ{¦¡½X·Q¼g¥X¥uÅã¥Ü¥X·í¤éªí³æ ¥u­n­×§ï¬õ¦â¤é´Á¬Û¹ï´N·|±a¥X¨º­Ó¤é´Áªºªí³æ

µ{¦¡§Æ±æ¥i¥H¦ba¤À­¶°õ¦æ ¦bb¤À­¶±a¥Xµ²ªG¥X¨Ó  

¥H¤W·PÁ¬ݧ¹¦¹½gªº¦³½t¤H ÁÂÁ   °²¦p¦³¨ä¥L»Ý­n§Ú¸É¥R»¡©úªºÅwªï´£¥X  ¦]¬°¨Ã¤£¯àª½±µ¶}ºô­¶¥X¨Ó¬Ýµ²ªG
  1. Sub ¥¨¶°1()
  2. '
  3. ' ¥¨¶°1 ¥¨¶°
  4. '

  5. '
  6.     With ActiveSheet.QueryTables.Add(Connection:= _
  7.         "URL;http://cagweb02.hct.com.tw/pls/cagweb/CAGWEB.C_PCTHA272?pArea=000&pSCd=6006&pDayFlg=0&pDayFlg1=1&pSearchFlg=2&pCustFlg=03897810001" _
  8.         , Destination:=Range("$A$1"))
  9.         .Name = _
  10.         "CAGWEB.C_PCTHA272?pArea=000&pSCd=6006&pDayFlg=0&pDayFlg1=1&pSearchFlg=2&pCustFlg=03897810001"
  11.         .FieldNames = True
  12.         .RowNumbers = False
  13.         .FillAdjacentFormulas = False
  14.         .PreserveFormatting = True
  15.         .RefreshOnFileOpen = False
  16.         .BackgroundQuery = True
  17.         .RefreshStyle = xlInsertDeleteCells
  18.         .SavePassword = False
  19.         .SaveData = True
  20.         .AdjustColumnWidth = True
  21.         .RefreshPeriod = 0
  22.         .WebSelectionType = xlAllTables
  23.         .WebFormatting = xlWebFormattingNone
  24.         .WebPreFormattedTextToColumns = True
  25.         .WebConsecutiveDelimitersAsOne = True
  26.         .WebSingleBlockTextImport = False
  27.         .WebDisableDateRecognition = False
  28.         .WebDisableRedirections = False
  29.         .Refresh BackgroundQuery:=False
  30.     End With
  31.         With ActiveSheet.QueryTables.Add(Connection:= _
  32.         "URL;http://cagweb02.hct.com.tw/pls/cagweb/CAGWEB.C_PCTHA272?pArea=000&pSCd=6006&pDayFlg=0&pDayFlg1=1&pSearchFlg=2&pCustFlg=04413850008" _
  33.         , Destination:=Range("$A$7"))
  34.         .Name = _
  35.         "CAGWEB.C_PCTHA272?pArea=000&pSCd=6006&pDayFlg=0&pDayFlg1=1&pSearchFlg=2&pCustFlg=04413850008"
  36.         .FieldNames = True
  37.         .RowNumbers = False
  38.         .FillAdjacentFormulas = False
  39.         .PreserveFormatting = True
  40.         .RefreshOnFileOpen = False
  41.         .BackgroundQuery = True
  42.         .RefreshStyle = xlInsertDeleteCells
  43.         .SavePassword = False
  44.         .SaveData = True
  45.         .AdjustColumnWidth = True
  46.         .RefreshPeriod = 0
  47.         .WebSelectionType = xlAllTables
  48.         .WebFormatting = xlWebFormattingNone
  49.         .WebPreFormattedTextToColumns = True
  50.         .WebConsecutiveDelimitersAsOne = True
  51.         .WebSingleBlockTextImport = False
  52.         .WebDisableDateRecognition = False
  53.         .WebDisableRedirections = False
  54.         .Refresh BackgroundQuery:=False
  55.     End With
  56.             With ActiveSheet.QueryTables.Add(Connection:= _
  57.         "URL;http://cagweb02.hct.com.tw/pls/cagweb/CAGWEB.C_PCTHA272?pArea=000&pSCd=6006&pDayFlg=0&pDayFlg1=1&pSearchFlg=2&pCustFlg=03909690001" _
  58.         , Destination:=Range("$A$13"))
  59.         .Name = _
  60.         "CAGWEB.C_PCTHA272?pArea=000&pSCd=6006&pDayFlg=0&pDayFlg1=1&pSearchFlg=2&pCustFlg=03909690001"
  61.         .FieldNames = True
  62.         .RowNumbers = False
  63.         .FillAdjacentFormulas = False
  64.         .PreserveFormatting = True
  65.         .RefreshOnFileOpen = False
  66.         .BackgroundQuery = True
  67.         .RefreshStyle = xlInsertDeleteCells
  68.         .SavePassword = False
  69.         .SaveData = True
  70.         .AdjustColumnWidth = True
  71.         .RefreshPeriod = 0
  72.         .WebSelectionType = xlAllTables
  73.         .WebFormatting = xlWebFormattingNone
  74.         .WebPreFormattedTextToColumns = True
  75.         .WebConsecutiveDelimitersAsOne = True
  76.         .WebSingleBlockTextImport = False
  77.         .WebDisableDateRecognition = False
  78.         .WebDisableRedirections = False
  79.         .Refresh BackgroundQuery:=False
  80.     End With
  81.              With ActiveSheet.QueryTables.Add(Connection:= _
  82.         "URL;http://cagweb02.hct.com.tw/pls/cagweb/CAGWEB.C_PCTHA272?pArea=000&pSCd=6006&pDayFlg=0&pDayFlg1=1&pSearchFlg=2&pCustFlg=02074160008" _
  83.         , Destination:=Range("$A$19"))
  84.         .Name = _
  85.         "CAGWEB.C_PCTHA272?pArea=000&pSCd=6006&pDayFlg=0&pDayFlg1=1&pSearchFlg=2&pCustFlg=02074160008"
  86.         .FieldNames = True
  87.         .RowNumbers = False
  88.         .FillAdjacentFormulas = False
  89.         .PreserveFormatting = True
  90.         .RefreshOnFileOpen = False
  91.         .BackgroundQuery = True
  92.         .RefreshStyle = xlInsertDeleteCells
  93.         .SavePassword = False
  94.         .SaveData = True
  95.         .AdjustColumnWidth = True
  96.         .RefreshPeriod = 0
  97.         .WebSelectionType = xlAllTables
  98.         .WebFormatting = xlWebFormattingNone
  99.         .WebPreFormattedTextToColumns = True
  100.         .WebConsecutiveDelimitersAsOne = True
  101.         .WebSingleBlockTextImport = False
  102.         .WebDisableDateRecognition = False
  103.         .WebDisableRedirections = False
  104.         .Refresh BackgroundQuery:=False
  105.     End With
  106.     Dim URL As String, xDate As Date
  107.    
  108.     With ActiveSheet.QueryTables.Add(Connection:= _
  109.         "URL;http://hct-apdev1.hct.com.tw:11235/HCT.AS400.Web/CargoTracking/CTGPS01R2.aspx?ScanDay=[color=Red]20131218[/color]&Stor=6006&StorDesc=%e5%8f%b0%e5%8d%97&CseNo=All&OpeCd=All" _
  110.         , Destination:=Range("$A$1"))
  111.         .Name = _
  112.         "CTGPS01R2.aspx?ScanDay=[color=Red]20131218[/color]&Stor=6006&StorDesc=%e5%8f%b0%e5%8d%97&CseNo=All&OpeCd=All"
  113.         .FieldNames = True
  114.         .RowNumbers = False
  115.         .FillAdjacentFormulas = False
  116.         .PreserveFormatting = True
  117.         .RefreshOnFileOpen = False
  118.         .BackgroundQuery = True
  119.         .RefreshStyle = xlInsertDeleteCells
  120.         .SavePassword = False
  121.         .SaveData = True
  122.         .AdjustColumnWidth = True
  123.         .RefreshPeriod = 0
  124.         .WebSelectionType = xlSpecifiedTables
  125.         .WebFormatting = xlWebFormattingNone
  126.         .WebTables = "1"
  127.         .WebPreFormattedTextToColumns = True
  128.         .WebConsecutiveDelimitersAsOne = True
  129.         .WebSingleBlockTextImport = False
  130.         .WebDisableDateRecognition = False
  131.         .WebDisableRedirections = False
  132.         .Refresh BackgroundQuery:=False
  133.     End With
  134. End Sub
½Æ»s¥N½X

¥»©«³Ì«á¥Ñ s93213241 ©ó 2013-12-21 13:53 ½s¿è

¥H¦Û¦æ¸Ñ¨M

1480625_717490708269016_1593430672_n.jpg (92.79 KB)

1480625_717490708269016_1593430672_n.jpg

TOP

¦^´_ 2# s93213241


    ¨ä¹ê¦³¬Ý¨S¦³À´

½Ð°Ýª©¤j¥i§_¤W¶ÇÀÉ®×¥H¯÷¤j®a¾Ç²ß»â®©©O??

TOP

¥»©«³Ì«á¥Ñ s93213241 ©ó 2013-12-24 14:24 ½s¿è

§ÚÀɮפÀ¨É¥X¨Ó¤]¨S¦³¥Î@@  ¦]¬°§Ú¦bPO¤å¤@¶}©l´N»¡©ú §Ú¤½¥q«Ç¤ººô  ¤@¯ë¥Áºô³s¤£¤W¥h
  1.     Dim xDate As Date
  2.     xDate = Date
  3.     Range("Sheet2!$E$6").Value = (xDate)

  4.     With ActiveSheet.QueryTables.Add(Connection:= _
  5.         "URL;http://hct-apdev1.hct.com.tw:11235/HCT.AS400.Web/CargoTracking/CTGPS01R2.aspx?ScanDay=" & Format(xDate, "YYYYMMDD") & "&Stor=6006&StorDesc=%e5%8f%b0%e5%8d%97&CseNo=All&OpeCd=All" _
  6.         , Destination:=Range("Sheet1!$A$25"))
  7.         .Name = _
  8.         "CTGPS01R2.aspx?ScanDay=" & Format(xDate, "YYYYMMDD") & "&Stor=6006&StorDesc=%e5%8f%b0%e5%8d%97&CseNo=All&OpeCd=All"
  9.         .FieldNames = True
  10.         .RowNumbers = False
  11.         .FillAdjacentFormulas = False
  12.         .PreserveFormatting = True
  13.         .RefreshOnFileOpen = False
  14.         .BackgroundQuery = True
  15.         .RefreshStyle = xlInsertDeleteCells
  16.         .SavePassword = False
  17.         .SaveData = True
  18.         .AdjustColumnWidth = True
  19.         .RefreshPeriod = 0
  20.         .WebSelectionType = xlSpecifiedTables
  21.         .WebFormatting = xlWebFormattingNone
  22.         .WebTables = "1"
  23.         .WebPreFormattedTextToColumns = True
  24.         .WebConsecutiveDelimitersAsOne = True
  25.         .WebSingleBlockTextImport = False
  26.         .WebDisableDateRecognition = False
  27.         .WebDisableRedirections = False
  28.         .Refresh BackgroundQuery:=False
  29.     End With
½Æ»s¥N½X
¥H¤W³o¬q§Ú¥d¦í¤ñ¸û¤[
" & Format(xDate, "YYYYMMDD") & " ¥Îªk

»P

Dim xDate As Date
    xDate = Date

«Å§i·í¤é®É¶¡   

³Ñ¤Uµn¤Jµe­±§Ú¤Wºô§ä¤H®aPO¥X¨Óªº¦¨«~¦Û¤v­×§ï¶i¥hªº

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD