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

[µo°Ý] ¦p¦óŪ¨ú¥t¤@­Óxls¸Ìªºsheets¸ê®Æ¨ìarray¸Ì¥H«K³B²z

[µo°Ý] ¦p¦óŪ¨ú¥t¤@­Óxls¸Ìªºsheets¸ê®Æ¨ìarray¸Ì¥H«K³B²z

½Ð°Ý¦p¦ó¦b¤£¶}Àɪº±¡ªp¤U ¥h¶}±Ò¥t¤@­Óxls¸Ìªº¥|­Ósheets(open,close,high,low)
¦s¨ì¥|­Ó¤£¦Pªºarray¸Ì ¥H«K°µ«áÄòªº³B²z ÁÂÁÂ
§Ú§ä¨ì¤@­Ó¤ñ¸û¹³ªº¨Ò¤l ¦ý§Ú§ï¤£¥X¨Ó
·Ð½Ð¦U¦ì«ü±Ð

Sub get_word_table( )
Dim wrdApp As Object
Set wrdApp = CreateObject("Word.Application") '«Ø¥ß¤Þ¥ÎWordÀ³¥Îµ{¦¡ªºª«¥ó
Set wrdDoc = wrdApp.Documents.Open("D:\Temp\ole_test.doc") '¤Þ¥ÎWord¤å¥ó
With wrdDoc.Tables(1)
¡@For r = 1 To .Rows.Count
¡@¡@For c = 1 To .Columns.Count
¡@¡@Cells(r, c) = .Cell(r, c)
¡@¡@Next c
¡@Next r
End With
wrdDoc.Close 'close the document
wrdApp.Quit 'close Word
Set wrdDoc = Nothing 'ÄÀ©ñª«¥óÅܼÆ
Set wrdApp = Nothing
End Sub
¤pµæ³¾¤@­Ó ½Ð¦h«ü±Ð~

¦^´_ 1# kasl

§Ú¶K¤§«e¨Ó³oµo°Ýªº½d¨Ò¡A±z¦A§ï§ï¬Ý

¤£¦PÀɮ׶¡ªº½Æ»s
Sub ex()
Dim Ar(), fd$, fs$, s%
fd = "D:\10¤ë\" '§ó§ï¦¨§Aªº10¤ë¸ê®Æ§¨¥Ø¿ý    *****³oÃä§A´N§ï§A·Q¶}ªºÀɮתº¸ê®Æ§¨¥Ø¿ý
fs = Dir(fd & "*.xls")
Do Until fs = ""
With Workbooks.Open(fd & fs)
   With .Sheets("¼Ò»s")
   ReDim Preserve Ar(s)
      Ar(s) = Array(.[AP7].Value, .[AP10].Value, .[AP12].Value, .[G7].Value, .[G12].Value)
      s = s + 1
   End With
   .Close 0
End With
fs = Dir
Loop
[A2].Resize(s, 5).Value = Application.Transpose(Application.Transpose(Ar))
End Sub


§Ú·Q­«ÂI¦b
With Workbooks.Open(fd & fs)   
fd¬O³]¸ê®Æ§¨¥Ø¿ý   (¨Ò¦p  C:\test\abc  --->C¼Ñ¸Ì¡Atest¸ê®Æ§¨¸Ì¡Aabc¸ê®Æ§¨)
fs¬O³]ÀɦW+.xls  (¨Ò¦p  001.xls)  
¨ºfd&fs´N·|¬OC:\test\abc\001.xls
µM«áWith Workbooks.Open(fd & fs)´NÅܦ¨With Workbooks.Open(C:\test\abc\001.xls)
´N·|¥h¶}001.xlsÀɤF

With .Sheets("¼Ò»s")ªº¥\¯à¬OÅý§A¨Ï¥Î¸Ó¤u§@ªí
©Ò¥H§A¥i¥H§ï¼g¦¨With .Sheets(" ")     ¨â­Ó"¤¤¶¡©ñ¤J§Aªº¤u§@ªíªº¦W¦r

µM«á¦A¥hŪ¸ê®Æ¡A½Æ»s¨ì²{¥ÎªºÀɮ׸̭±ªº¬Y¤@­Ó¤u§@ªí(sheet)¤¤ªº°}¦C
³oÃä§Ú´N¤£¤Ó·|¤F¡A¤£¦n·N«ä¡A§Ú¤]¬O·s¤â

§Æ±æ¹ï§A¦³¤@ÂIÀ°§U
«¢Åo~¤j®a¦n§r

TOP

¤p§Ì¦Û°Ý¦Ûµª¤@¤U

Sub Get_Stock_Table()

Dim ExcApp As Object

Set ExcApp = CreateObject("Excel.Application") '«Ø¥ß¤Þ¥ÎWordÀ³¥Îµ{¦¡ªºª«¥ó
Set ExcDoc = ExcApp.Workbooks.Open("D:\¶}°ª§C¦¬.xls") '¤Þ¥ÎWord¤å¥ó

Dim AR_Open, AR_High, AR_Low, AR_Close

With ExcDoc.Worksheets("Open")
  AR_Open = .UsedRange.Value
End With

With ExcDoc.Worksheets("High")
  AR_High = .UsedRange.Value
End With

With ExcDoc.Worksheets("Low")
  AR_Low = .UsedRange.Value
End With

With ExcDoc.Worksheets("Close")
  AR_Close = .UsedRange.Value
End With


ExcDoc.Close 'close the document
ExcApp.Quit 'close Excel
Set ExcDoc = Nothing 'ÄÀ©ñª«¥óÅܼÆ
Set ExcApp = Nothing

End Sub

¤£ª¾¤¤¶¡¨º´X­Ó¦æ­«Âаµªº «ç»ò¥Îloopªº¤è¦¡§â¥L­Ì¦X°_¨Ó¼g
ÁÂÁÂ~
¤pµæ³¾¤@­Ó ½Ð¦h«ü±Ð~

TOP

¦^´_ 3# kasl
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Get_Stock_Table()
  3. Dim ExcApp As Workbook, AR(0 To 3), Sh, i As Integer

  4. 'Set ExcApp = CreateObject("Excel.Application") '«Ø¥ß¤Þ¥ÎWordÀ³¥Îµ{¦¡ªºª«¥ó
  5. '??? Excel.Applicationg ¬O ExcelÀ³¥Îµ{¦¡ªºª«¥ó****
  6. 'Set ExcDoc = ExcApp.Workbooks.Open("D:\¶}°ª§C¦¬.xls") '¤Þ¥ÎWord¤å¥ó
  7. '??? ³oxls¬OExcelÀÉ

  8. '**** ¶}±Òªº¬Oxls ****
  9. Set ExcApp = Workbooks.Open("D:\¶}°ª§C¦¬.xls") '¶}±ÒexcelÀÉ
  10. Sh = Split("Open,High,Low,Close", ",")
  11. With ExcApp
  12.     For i = 0 To UBound(Sh)
  13.         AR(i) = .Worksheets(Sh(i)).UsedRange
  14.     Next
  15. End With
  16. ExcApp.Close 'Close the Workbook
  17. 'Set ExcApp = Nothing 'ÄÀ©ñª«¥óÅܼÆ
  18. 'ExcApp:µ{¦¡ªº¨p¥ÎÅܼÆ,µ{¦¡µ²§ô´N³QÄÀ©ñ±¼,¤£¥²¦A Set ExcApp = Nothing
  19. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÁÂÁª©¥D±zªº¦^µª
¤£¹L "¶}°ª§C¦¬.xls" ¸Ì­±©ñªº¬O200°¦­ÓªÑªñ¤T¦~¨Ó¨C¤Ñªº¶}½L¡B³Ì°ª¡B³Ì§C¡B¦¬½L»ù¡A¥´ºâ¥Î¥|­Ó¤Gºû°}¦C¨Ó¦s©ñ
§Ú¦^®a«á¦A¨Ó·Q«ç»òÅý¥L work¡AÁÂÁ ~
¤pµæ³¾¤@­Ó ½Ð¦h«ü±Ð~

TOP

¸Õ¤F¤@¤U±z´£¨Ñªºµ{¦¡ ¥i¥H¥Î
ÁÂÁÂ
§Ú¦A¨Ó¼g¨ä¥¦ªº¥\¯à
¤pµæ³¾¤@­Ó ½Ð¦h«ü±Ð~

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD