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

[µo°Ý] [¨D§U]¸ê®Æ¬d¸ß¨S¦³¦^À³

[µo°Ý] [¨D§U]¸ê®Æ¬d¸ß¨S¦³¦^À³

¦¹ÀɬO±q¶°«O¤¤¤ß¬d¸ßªÑÅv¤À´²ªí,¥Øªº¬O­n§âªÑÅv¦³¶°¤¤ªº­ÓªÑµ¹¿z¿ï¥X¨Ó,¥i¬Oµ{¦¡°õ¦æ¤@¬q®É¶¡«á³£·|¨S¦³¦^À³,¨S¦³¿ìªk¶¶§Q¶]§¹,½Ð±Ð¥ý¶i¸Ó¦p¦ó­×¥¿?·P¿E¤£ºÉ~

Query.zip (76.75 KB)

¦^´_ 1# herhsiu


    À³¸Ó¬O·¸¦ì°ÝÃD,§ï³o¨â­Ó
(1)Dim i As Long       'Integer  ¹Bºâ·¸¦ì,¬G§ï¬° Long
(2)idx As Long

TOP

¦^´_ 1# herhsiu

§AªºÀɮשǩǪº,
§Ú¥Î³æ¨B¼Ò¦¡¶] Workbook_Open() µ{§Ç,
°õ¦æ¨ì :
Range("C2") = Year(Date) & Format(Month(Date), "00") & Format(Day(Date), "00")
³o¤@¦æ®ÉVBA°õ¦æºü·|ª½±µ¸õ¨ì
Function Trans2Mon(str As String)   ~    End Function  ³o­Ó¥\¯à°Ï¶ô¤º,
¥B¤@ª½­«½ÆµÛ°õ¦æ¦¹¥\¯à,
§Y«K¤w¸g°õ¦æ¨ì Exit Function ©Î¬O End Function,
·Ó¼Ë¤U¤@¨BÁÙ¬O¶] Function Trans2Mon(str As String) ³o¦æ,(§Y¤S­«ÀY¶}©l¶]...µL­­°j°é?)
§Ú¤]¬Ý¤£¥X¨Ó¬°¤°»ò·|µo¥Í³o¼Ëªº²{¶H.

¦]¬°·j¹M¾ã­Ó±M®×³£¨S¬Ý¨ì©I¥s Trans2Mon ¥\¯àªº±Ô­z,
©Ò¥H§Ú¸ÕµÛ§â¸Ó¥\¯àªºµ{§Ç¾ã­Ó³£µ¹¥¦ Mark ±¼¤§«á,
¦A«ö "®ü¼´" «öÁäµ{¦¡¶]°_¨Ó´NÅܧ֤F.
¥t ¦]¬°§Úµo²{ÂàÀɹLµ{¤¤ªÑ²¼¥N¸¹³£¨SÀH¤§§ó·s,
©Ò¥H§Ú¹Á¸Õ­×§ï¤F¤@¤U GetMonData µ{§Ç :
  1.          If [a3] <> "" Then
  2.             [b1] = stkno
  3.             StkName = [a3]
  4.             Exit For
  5.          Else
½Æ»s¥N½X
Query-A.zip (93.38 KB)

TOP

To luhpro¤j¤j:
Trans2Mon ¦Û­q¸q¨ç¼Æ¬O¬°¤F±N¤U¸ü¦^¨Óªº¸ê®Æ¤é´Á®æ¦¡°µÂà´«
==> ±N ¸ê®Æ¤é´Á¡G99¦~02¤ë01¤é --> 99/02
¬Ý¨Óªº½T¬O³o­Ó¨ç¼Æ³y¦¨ªº,·PÁÂ~

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-10-2 15:51 ½s¿è

¦^´_ 4# herhsiu



¦³¿ìªk±N¨C¤é¸ê®Æ·|¦¨¤@¥÷
  1.     If .QueryTables.Count = 0 Then
  2. 29.                .QueryTables.Add "URL;" & Qur, .[A1]
  3. 30.            Else
  4. 31.                .QueryTables(1).Connection = "URL;" & Qur
  5. 32.                Msg = True
  6. 33.            End If
  7. 34.           With .QueryTables(1)
½Æ»s¥N½X
¦^´_ 3# luhpro
·j¹M¾ã­Ó±M®×³£¨S¬Ý¨ì©I¥s Trans2Mon ¥\¯àªº±Ô­z,¦b³o¸Ì



·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# GBKEE
³o¥÷ªÑÅv¸ê®Æ§@ªÌ¨Ó¦Û[¯º¸Ü¤@ÆYµ¨]³¡¸¨®æªº§õ¥ý¥Í,­ì©lÀɮצp¤U
¤jªÑªFªº³zµøÃè-­ì©lÀÉ.rar (43.53 KB)

-------------------------------
¦^´_ 1# herhsiu
®Ú¾Ú±z­×§ïªº³¡¥÷·½½X,§PÂ_±zÀ³¸Ó¬O·Q­n¤U¸ü©Ò¦³¥N¸¹ªºªÑÅv¸ê®Æ
    n = Sheet1.Range("A65536").Rows.End(xlUp).Row
    For id = 2 To n
        Range("D2") = id
        stkno = Sheet1.Cells(id, 1) '­ÓªÑ¥N½X

¦]¬°¥þ³¡ªÑ²¼¸ê®Æ¶q,¹Bºâ·|²£¥Í·¸¦ì,¬G´£¿ô Integer  ­n§ï¬° Long
ªÑÅv.rar (292.81 KB)

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2013-10-2 23:32 ½s¿è

¦^´_ 5# GBKEE
¶â...¦³¬Ý¨ì¤F,ÁÂÁ§A§i¶D§Ú.

¤£¹L´N¹³§Ú¤W­±©Ò»¡ªº,
§Ú¦A¦¸¥Î³æ¨B¶]¹LWorkbook_Open¤@¹M,
µo²{§Y¨Ï¸Ó¥\¯à¥u¦³¦b Sheets("¶i¶¥¾ã²z") ¤¤¤~¦³¤½¦¡¥h¤Þ¥Î¨ì,
µM¦Ó§Y«K¥u¬OÅܧó Sheets("ªÑ²¼¥N½X") ¸ÌªºÀx¦s®æ¤º®e(®Ú¥»»P¸ÓFunctionµLÃö),
¨º­Ó Function Trans2Mon ¤´µM¬O§â©Ò¦³¦³¤Þ¥Î¨ì¸Ó¤½¦¡ªºÀx¦s®æ¥þ³¡³£­«·s­pºâ¤@¦¸, (¥ô¤@Sheet¤¤ªº¥ô¦ó¤@­ÓÀx¦s®æ¤º®eÅܧó,¥¦³£·|¥þ³¡³£¦A­pºâ¤@½ü O.O" )
Ãø©Ç§â¨º­Ó Function ®³±¼«á³t«×®t¨º»ò¦h.

§Ú¸Õ¤F¤@¤U, §â¨º 12 ­ÓÀx¦s®æ¤½¦¡§ï¬° :
  1. =CONCATENATE(MID(INDIRECT(ADDRESS(221 - (COLUMN() - 2)  * 20,1,1,1,"ªñ¤@¦~¸ê®Æ"),1),6,3),"/",MID(INDIRECT(ADDRESS(221 - (COLUMN() - 2)  * 20,1,1,1,"ªñ¤@¦~¸ê®Æ"),1),10,2))
½Æ»s¥N½X
´N¥i¥H§â  Function Trans2Mon ®³±¼¤F,
·Pı¤W³t«×¦ü¥G¦³¤ñ¸û§Ö.
¬Æ¦Ü­Y§â INDIRECT(ADDRESS(221 - (COLUMN() - 2)  * 20,1,1,1,"ªñ¤@¦~¸ê®Æ"),1) ¤À³Î¥X¨Ó¥t¥ÎÀx¦s®æ¦s©ñ,
¤½¦¡¤]¯à§ó²¤Æ, ³t«×À³¸Ó¤]·|§ó§Ö.

§Ú·Q§Ú¥H«á·|¾¨¶qÁקK¦bÀx¦s®æ¤½¦¡¤¤¤Þ¥Î Function,
¦]¬°«D¥²­nªº­pºâ°Ê§@-loading ¤Ó­«¤Ó¦h¤F.

TOP

Sorry,,,¦Ê±K¤@²¨
§ï¤@¤U³o¸Ì
    Sheets("¸ê®Æ").Range("A" & CStr((idx - 2) * 17 + 1) & ":A" & CStr((idx - 2) * 17 + 17)) = ¥N¸¹
ªÑÅv.rar (517.81 KB)

TOP

¦^´_ 5# GBKEE

GBKEE¤j¤j,µ{¦¡­×§ï«á¸ê®Æ¬d¸ß¸I¨ì¨S¦³¸ê®Æªº¤é´Á(5/1³Ò°Ê¸`)·|¥X²{¿ù»~¤¤Â_,­ì¥»ªºcode«o¤£·|¦³³oºØ±¡ªp,½Ð°Ý¸Ó¦p¦ó¸Ñ¨M?ÁÂÁÂ~



    Qur = "http://www.tdcc.com.tw/smWeb/QryStock.jsp?SCA_DATE=" & strDate & "&SqlMethod=StockNo&StockNo=" & stkno & "&StockName=&sub=%ACd%B8%DF"
   
       If Sheet3.QueryTables.Count = 0 Then
            Sheet3.QueryTables.Add "URL;" & Qur, Sheet3.[A3]
            Else
                Sheet3.QueryTables(1).Connection = "URL;" & Qur
                Msg = True
       End If

         With Sheet3.QueryTables(1)
             .Name = "«ùªÑ¤À§G"
             .FieldNames = True
             .RowNumbers = False
             .FillAdjacentFormulas = False
             .PreserveFormatting = False
             .RefreshOnFileOpen = False
             .BackgroundQuery = True
             .RefreshStyle = xlOverwriteCells
             .SavePassword = False
             .SaveData = True
             .AdjustColumnWidth = False
             .RefreshPeriod = 0
             .WebSelectionType = xlSpecifiedTables
             .WebFormatting = xlWebFormattingNone
             .WebTables = "6,7,8"
             .WebPreFormattedTextToColumns = True
             .WebConsecutiveDelimitersAsOne = True
             .WebSingleBlockTextImport = False
             .WebDisableDateRecognition = False
             .WebDisableRedirections = False
             .Refresh BackgroundQuery:=False
         End With

TOP

§Aªº²{¶H§Ú¹J¹L¡A
«Øij§A±NªÑ²¼¥N¸¹±a¶i¥h­ì©lÀɤ¤¡A
·í­ì©lÀɦb§ì¨ú¨CÀɪѲ¼¸ê®Æ®É¡A¶ë¶i¥h1 or 2 ¬íÄÁ¡A
¦A±N§A­nªº¸ê®Æ§ì¥X¨Ó¡C

¦p¦¹À³¸Ó¥i¦æ¡ã

TOP

        ÀR«ä¦Û¦b : ¡i°±º¢¤£«e¡A²×µL©Ò±o¡j¤H³£°g©ó´M§ä©_ÂÝ¡A¦]¦Ó°±º¢¤£«e¡FÁa¨Ï®É¶¡¦A¦h¡B¸ô¦Aªø¡A¤]¤FµL¥Î³B¡A²×µL©Ò±o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD