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

[µo°Ý] ¦p¦ó¦b¤£¥´¶}ÀɮפU¡A±NXMLÀÉ©ÎCSVÀɶפJEXCEL¤¤

¦^´_ 10# GBKEE

GBKEE¤j¡A¦pªG¥Ø«e¤u§@ªí¸Ì­±¨S¦³SHEET1©MSHEET2¡A«ç¿ì?
¦]¬°§Ú¦b°µ´ú¸Õªº®É­Ô¬å¥ú¤FSHEET¡A¥u³Ñ¤USHEET13
­n«ç¼Ë­×§ïµ{¦¡½X?

TOP

¦^´_  GBKEE

GBKEE¤j¡A¦pªG¥Ø«e¤u§@ªí¸Ì­±¨S¦³SHEET1©MSHEET2¡A«ç¿ì?
¦]¬°§Ú¦b°µ´ú¸Õªº®É­Ô¬å¥ú¤FSHEE ...
paul3063 µoªí©ó 2018-1-17 17:06


¥u³Ñ¤USHEET13¡Aª½±µ±N SHEET13 ªí³æ¦WºÙ§ï¦¨ SHEET1 «K¥i¥H¤F¡A¥H¦¹Ãþ±À¡C

TOP

ÁÙ¯u¬OºC§r

­è¦³¸Õ¹L¤@­Ó¤èªk
¥i¥H§â­Ë¶i sheet ªº¯Ó®É­°¨ì 1/6
¤£¹L­ì½X¨S¦³´£¨Ñ ­Ë¶i¦hsheet

TOP

¥»©«³Ì«á¥Ñ jackyq ©ó 2018-1-17 19:58 ½s¿è

Public Sub «ó§Ú______()
   
    Dim rsData As Object

    Csv = "C:\Documents and Settings\Administrator\My Documents\Downloads\36_2.csv\36_2.csv"   ' ¦Û¤v§ï¦¨Àɮׯu¹ê¸ô®|

    Set rsData = Get_CSVFile_Object(Csv)
   
   
    ¨C­¶©Ó¸üµ§¼Æ = 10000
    page_count = (rsData.RecordCount \ ¨C­¶©Ó¸üµ§¼Æ) + Sgn(rsData.RecordCount Mod ¨C­¶©Ó¸üµ§¼Æ)
   
    For w = 1 To page_count
        On Error Resume Next
        s = Sheets("Data_" & w).Name
        If Err Then
           Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Data_" & w
        End If
        On Error GoTo 0
        Sheets("Data_" & w).Cells.Clear
    Next
    Excel.Application.DisplayAlerts = 0
    On Error Resume Next
    For w = page_count + 1 To 10 ^ 9
        Sheets("Data_" & w).Delete
        If Err Then Exit For
    Next
    On Error GoTo 0
    Excel.Application.DisplayAlerts = 1

    If rsData.RecordCount > 0 Then
        ReDim ¼ÐÃD(0 To rsData.Fields.Count) As String
        For w = 0 To rsData.Fields.Count - 1
            ¼ÐÃD(w) = rsData.Fields(w).Name
        Next

        For page = 1 To page_count
            Sheets("Data_" & page).Select
            Sheets("Data_" & page).Cells(1, 1).Resize(, UBound(¼ÐÃD) - LBound(¼ÐÃD) + 1) = ¼ÐÃD


            Table = rsData.GetRows(¨C­¶©Ó¸üµ§¼Æ)
            Table = Transpose2(Table)
            Sheets("Data_" & page).Cells(2, 1).Resize(UBound(Table, 1) - LBound(Table, 1) + 1, UBound(Table, 2) - LBound(Table, 2) + 1) = Table
        Next
    End If
    rsData.Close

End Sub

Public Function Get_CSVFile_Object(ByVal File_FullPathName As String) As Object
      Set rs = CreateObject("ADODB.Recordset")
    Set conn = CreateObject("ADODB.Connection")
   
    strFileName = CreateObject("Scripting.FileSystemObject").GetFileName(File_FullPathName)
    strFilePath = CreateObject("Scripting.FileSystemObject").GetParentFolderName(File_FullPathName)
    aa = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & strFilePath & ";" & _
         "Extended Properties='text;HDR=Yes;FMT=Delimited(,);CharacterSet=65001'"
    conn.Open aa, "", ""
   
    Const adOpenStatic = 3, adLockReadOnly = 1, adCmdText = 1
   
    rs.Open "select * from [" & strFileName & "]", conn, adOpenStatic, adLockReadOnly, adCmdText
    Set Get_CSVFile_Object = rs
End Function


Public Function Transpose2(ar2d)
    Dim c1 As Long, c2 As Long
    ReDim out(LBound(ar2d, 2) To UBound(ar2d, 2), LBound(ar2d, 1) To UBound(ar2d, 1)) As String
    For c1 = LBound(ar2d, 1) To UBound(ar2d, 1)
    For c2 = LBound(ar2d, 2) To UBound(ar2d, 2)
        If Not IsNull(ar2d(c1, c2)) Then
           out(c2, c1) = ar2d(c1, c2)
        End If
    Next
    Next
    Transpose2 = out
End Function

TOP

§Ö³t­Ë¶i sheet
¦]¨S¦³´£¨Ñ ­Ë¶i >= sheet2 ...  
©Ò¥H´N¤£§Ë³o­Ó¤F

TOP

¦^´_ 11# paul3063

8#³o¬qµ{¦¡½X´N¬O ³B²z¤u§@ªí¤£¦s¦b ,½Ð½m²ß¬Ý¬Ý
  1. XER:
  2.     Dim ii
  3.      '³B²zµ{¦¡¤¤ Sheets(i).Cells.Clear £¾ªº¿ù»~
  4.     If Err = 9 Then ThisWorkbook.Sheets.Add , Sheets(Sheets.Count): Resume
  5.      '³B²zµ{¦¡¤¤ .Resize(, UBound(Ar) + 1) = Ar £¾ªº¿ù»~
  6.    '2003 **°}¦Cªº¤¸¯À¦r¤¸¼Æ¤j©ó255 ­Ó¦r¤¸,·|¦³¿ù»~
  7.    '2003 ¥H«áªºª©¥»¨S¦³¦¹¿ù»~
  8.     For ii = 0 To UBound(Ar)
  9.         Sheets(Sh).Cells(i + Sh - X, ii + 1) = Ar(ii)
  10.         If Len(Ar(ii)) > 456 Then
  11.         Debug.Print Sh, i + 1 - X, ii, Len(Ar(ii))
  12.         End If
  13.     Next
  14.     Resume Next
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 14# jackyq
2003 ª©
Public Sub «ó§Ú______() ¹ï·Ó  #8 ªº Sub Ex_CVSªº¤À³Î()  ¤¤ §ä¥X¤@¨Ç®t²§


[Book1]Sheet1!$L$979 ¦r¤¸¼Æ1000        [Book1]Sheet3!$L$3692 ¦r¤¸¼Æ1000
[Book1]Sheet1!$L$2160 ¦r¤¸¼Æ1000        [Book1]Sheet3!$L$3693 ¦r¤¸¼Æ1000
[Book1]Sheet1!$L$2161 ¦r¤¸¼Æ1000        [Book1]Sheet3!$M$5833 ¦r¤¸¼Æ988
[Book1]Sheet1!$Q$2529 ¦r¤¸¼Æ1042        [Book1]Sheet3!$M$5834 ¦r¤¸¼Æ988
[Book1]Sheet2!$M$3438 ¦r¤¸¼Æ988        [Book1]Sheet3!$L$6259 ¦r¤¸¼Æ1000
[Book1]Sheet2!$M$3474 ¦r¤¸¼Æ988        [Book1]Sheet3!$L$6260 ¦r¤¸¼Æ1000
[Book1]Sheet2!$AD$4019 ¦r¤¸¼Æ1058        [Book1]Sheet3!$Q$6409 ¦r¤¸¼Æ1067
[Book1]Sheet2!$M$4027 ¦r¤¸¼Æ988        [Book1]Sheet3!$M$7768 ¦r¤¸¼Æ988
[Book1]Sheet2!$M$4083 ¦r¤¸¼Æ988        [Book1]Sheet3!$M$9390 ¦r¤¸¼Æ988
[Book1]Sheet2!$AD$4090 ¦r¤¸¼Æ1058        [Book1]Sheet3!$M$9391 ¦r¤¸¼Æ988
[Book1]Sheet2!$M$4578 ¦r¤¸¼Æ988        [Book1]Sheet4!$Q$2957 ¦r¤¸¼Æ1067
[Book1]Sheet2!$M$4615 ¦r¤¸¼Æ988        [Book1]Sheet4!$Q$3005 ¦r¤¸¼Æ1067
[Book1]Sheet2!$M$4618 ¦r¤¸¼Æ988        [Book1]Sheet4!$Q$4840 ¦r¤¸¼Æ1028
[Book1]Sheet2!$M$4703 ¦r¤¸¼Æ988        [Book1]Sheet4!$Q$4841 ¦r¤¸¼Æ1028
[Book1]Sheet2!$M$4909 ¦r¤¸¼Æ988        [Book1]Sheet4!$Q$8504 ¦r¤¸¼Æ1028
[Book1]Sheet2!$M$4910 ¦r¤¸¼Æ988        [Book1]Sheet5!$AC$2302 ¦r¤¸¼Æ1759
[Book1]Sheet2!$M$4942 ¦r¤¸¼Æ988        [Book1]Sheet5!$AA$9490 ¦r¤¸¼Æ1282
[Book1]Sheet2!$M$4943 ¦r¤¸¼Æ988        [Book1]Sheet6!$Q$5208 ¦r¤¸¼Æ1204
[Book1]Sheet2!$AD$5049 ¦r¤¸¼Æ1058        [Book1]Sheet6!$Q$5563 ¦r¤¸¼Æ1024
[Book1]Sheet2!$AD$5050 ¦r¤¸¼Æ1058        [Book1]Sheet7!$AD$1329 ¦r¤¸¼Æ1058
[Book1]Sheet2!$AD$7966 ¦r¤¸¼Æ1759        [Book1]Sheet7!$AB$1333 ¦r¤¸¼Æ1058
[Book1]Sheet2!$AB$9480 ¦r¤¸¼Æ1759        [Book1]Sheet7!$AA$5423 ¦r¤¸¼Æ945
[Book1]Sheet2!$Q$9482 ¦r¤¸¼Æ1024
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 17# GBKEE

ÁÂÁ¤j®a

TOP

¦^´_ 17# GBKEE

¦p¦ó¦b¤£¥´¶}ÀɮפU¡A±NXMLÀÉ©ÎCSVÀɶפJEXCEL¤¤

§Ú¤]¦³¤W­z°ÝÃD¡Aª¦¤å«á¡A¦]µ{«×¤£¨¬µLªk¸Ñ¨M³B²z
·Q½Ð°Ý
¦p¦ó±Nºô­¶
http://financials.morningstar.com/ratios/r.html?t=UNP
¤¤ªºUNP Key Ratios.CSVÀÉ¡A(¹Ï)

¤£¥´¶}.CSVÀÉ¡A§ì¥X¸ê®Æ©ñ¸m¦ba1
½Ð¤j¤j ¼·¤¾À°¦£  ...·P®¦ ÁÂÁÂ


50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¬O«D·í±Ð¨|¡AÆg¬ü§@ĵ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD