Attribute VB_Name = "gy01_corpgrp"
Sub gy01_corpgrp()
'
' gy01_corpgrp Macro
' マクロ記録日 : 2003/10/12  ユーザー名 : ana
'
    '警告の表示を停止
    Application.DisplayAlerts = False

'


    Dim classname01 As Variant
    classname01 = Array("forestry_fishery", "mining", _
                        "builiding1", "builiding2", "builiding3", "builiding4", "builiding5", _
                        "food1", "food2", "food3", "food4", _
                        "fiber1", "fiber2", "paper_pulp", _
                        "chemical1", "chemical2", "chemical3", "chemical4", "chemical5", _
                        "medicine", "petroleum_coal", "rubber", _
                        "glass_stone1", "glass_stone2", _
                        "iron1", "iron2", "metal_exiron", _
                        "metal1", "metal2", _
                        "machinery1", "machinery2", "machinery3", "machinery4", "machinery5", _
                        "electrical1", "electrical2", "electrical3", "electrical4", "electrical5", "electrical6", "electrical7", _
                        "transport_machinery1", "transport_machinery2", "transport_machinery3", "precision_machinery", _
                        "misc_machinery1", "misc_machinery2", "misc_machinery3", "electrocity_gas", _
                        "land_transport1", "land_transport2", _
                        "shipping", "sky_transport", "warehouse_conveyance", _
                        "communication1", "communication2", "communication3", "communication4", "communication5", "communication6", _
                        "wholesale1", "wholesale2", "wholesale3", "wholesale4", "wholesale5", "wholesale6", "wholesale7", "wholesale8", _
                        "retail1", "retail2", "retail3", "retail4", "retail5", "retail6", "retail7", _
                        "bank1", "bank2", "bank3", _
                        "bill_broaker", "insurer", _
                        "financial1", "financial2", _
                        "estate1", "estate2", _
                        "service1", "service2", "service3", "service4", "service5", "service6")
                        

    Dim class_idx As Integer
    Dim corp_row_idx As Integer
    Dim start_point01 As Integer
    Dim even_idx As Integer
    corp_row_idx = 1


    'For class_idx = 0 To 89
    For class_idx = 0 To 9     ' テスト用
    
        ' シートのクリア
        Sheets("Sheet1").Cells.ClearContents


        ' WEBクエリの発行
        With ActiveSheet.QueryTables.Add(Connection:= _
            "FINDER;C:\mysql\mysqldata\excel\webquery\" & classname01(class_idx) & ".iqy" _
            , Destination:=Range("A1"))
            '.Name = "corp_info"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = False
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .Refresh BackgroundQuery:=False
        End With

        
        
        ' 文字列「コード」の検索
        Range("A6:A14").Select
        Selection.Find(What:="コード", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Activate
        
        start_point01 = ActiveCell.Row + 1 '最初の銘柄コードの行を取得

        Range(Cells(start_point01, 1), Cells(start_point01, 3)).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Cells(corp_row_idx, 1).Select
        ActiveSheet.Paste


        even_idx = 2
    
        Sheets("Sheet1").Select
    
        Do While Cells(start_point01 + even_idx, 1) > 0 And Cells(start_point01 + even_idx, 1) < 10000
            Range(Cells(start_point01 + even_idx, 1), Cells(start_point01 + even_idx, 3)).Select
            Selection.Copy
            Sheets("Sheet2").Select
            Range(Cells(corp_row_idx + (even_idx / 2), 1), Cells(corp_row_idx + (even_idx / 2), 3)).Select
            ActiveSheet.Paste

            even_idx = even_idx + 2
            Sheets("Sheet1").Select
        Loop

        corp_row_idx = corp_row_idx + (even_idx / 2)


    Next
    
    ' 企業の銘柄コード、企業名、簡単な情報をCSVファイルで保存
    Sheets("Sheet2").Select
    ChDir "C:\mysql\mysqldata\excel\webquery"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\mysql\mysqldata\excel\webquery\gy01_corpgrp.csv", FileFormat:=xlCSV, CreateBackup:=False
        
    Sheets("gy01_corpgrp").Name = "Sheet2"
        

End Sub