自分用メモ

エクセルで複数のCSVファイルを結合しシートごとにグラフを作成する

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

Sub ファイル結合()

' ファイル結合 Macro

' Keyboard Shortcut: Ctrl+Shift+A

'Sub CSVImportSheets()

Dim FileNames As Variant

Dim fn As Variant

Filename = Application.GetOpenFilename( _

FileFilter:="CSV (カンマ区切り)(*.csv),*.csv", _

Title:="Please select CSV files for integration", MultiSelect:=True)

For Each fn In Filename

Worksheets.Add After:=Worksheets(Worksheets.Count)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fn, Destination:=Range("A1"))

.Name = ActiveSheet.Name

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SaveData = True

.AdjustColumnWidth = False

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 932

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileCommaDelimiter = True

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

ActiveSheet.UsedRange.QueryTable.Delete

 Range("F1").Select

    ActiveCell.FormulaR1C1 = "=RC[-2]*1000000"

    Range("F1").Select

    Selection.AutoFill Destination:=Range("F1:F2500")

    Range("F1:F2500").Select

    Range("J7").Select

    ActiveSheet.Shapes.AddChart.Select

     ActiveChart.ApplyChartTemplate ( _

        "C:\Users\********.crtx")

    ActiveSheet.Shapes("グラフ 1").IncrementLeft 99.75

    ActiveSheet.Shapes("グラフ 1").IncrementTop -15.75

    ActiveChart.SeriesCollection(1).XValues = Range("F1:F2500")

    ActiveChart.SeriesCollection(1).Values = Range("e1:e2500")

Next fn

End Sub