エクセル マクロ ファイルを開きグラフ作成

VBAを使用して、エクセルファイルをユーダで選択し読み込み
読み込んだエクセルデータからグラフを作成したいと考えています。

コマンドボタンに下記の通り入力しファイルを読み込みました。

Sub ファイルを開いてセルに表示()
Dim OpenFileName As String
OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OpenFileName <> "False" Then
Filename = Dir(OpenFileName)
ActiveSheet.Cells(1, 7) = Filename
Else
MsgBox "キャンセルされました"
End If
End Suｂ

読み込んだエクセルファイル、Sheet1をデータとして下記の
マクロを実行してグラフを作成したいのですが、どのように手直しを
行ったらよいのか分からないので教えて頂けないでしょうか。

Sub グラフを作成し別シートに貼り付け()
'可変範囲折れ線グラフを作成
Dim hani As String
shname = ActiveSheet.Name 'シート名を記憶
rmax = Range("A2").End(xlDown).Row '最終行
hani = "C1:C" & rmax & ",E1:E" & rmax
Range(hani).Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.Location Where:=xlLocationAsObject, Name:=shname
ActiveChart.SeriesCollection(1).XValues = "='" & shname & "'!R2C1:R" & rmax & "C1"
'折れ線グラフを切り取り貼り付け
ActiveChart.Parent.Cut
Worksheets.Add(after:=Worksheets(Worksheets.Count)) _
.Name = Format(Now(), "グラフ1")
ActiveSheet.Paste
With Range("A1:F16")
ActiveSheet.ChartObjects("グラフ 1").Width = .Width
ActiveSheet.ChartObjects("グラフ 1").Height = .Height
End With
ActiveSheet.ChartObjects(1).Name = "全体グラフ"
End Sub

No.4 ベストアンサー 回答者： tsubuyuki 回答日時： 2013/04/26 02:51

> ファイルを開かずにグラフを作成出来ればと考えている

現段階では「開かずには作成できない」と覚えておいたほうが良さそうです。

なので次善の策として、
・一度グラフの元データ（ブック）を開いて、
・グラフを表示するブックにコピー、
・元データをすぐに閉じる、
と言う方法を提案です。

「Application.ScreenUpdating = False」
と指定してやると、画面更新を見せずに処理を進めることが可能です。


Sub ファイルを開いてグラフを作成()
Dim OpenFileName As String
Dim DataBook As Workbook, GraphBook As Workbook
Dim DataSheet As Worksheet
Dim hani As String
Dim rmax As Long

Application.ScreenUpdating = False　　　　'画面更新無効
Application.DisplayAlerts = False　　　　'確認メッセージ非表示

　　　　Set GraphBook = ThisWorkbook　　　'グラフを表示するブックを記憶

　　　　'グラフの元データにするブックを指定して、一度開く
　　　　OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls*")
　　　　　　　　If OpenFileName <> "False" Then
　　　　　　　　　　　　Set DataBook = Workbooks.Open(OpenFileName)
　　　　　　　　　　　　ActiveSheet.Cells(1, 7) = Filename
　　　　　　　　Else
　　　　　　　　　　　　MsgBox "キャンセルされました"
　　　　　　　　End If

　　　　'"グラフデータ"と言うシートがあったら削除
　　　　For i = 1 To GraphBook.Sheets.Count
　　　　　　　　If GraphBook.Sheets(i).Name = "グラフデータ" Then
　　　　　　　　　　　　GraphBook.Sheets(i).Delete
　　　　　　　　End If
　　　　Next i

　　　　'グラフの元データをコピーして、"グラフデータ"と言う名前に変更
　　　　DataBook.Sheets(1).Copy After:=GraphBook.Sheets(GraphBook.Sheets.Count)
　　　　DataBook.Close False
　　　　GraphBook.Sheets(GraphBook.Sheets.Count).Name = "グラフデータ"
　　　　Set DataSheet = GraphBook.Sheets("グラフデータ")

　　　　'既にグラフが有ったら削除しておく
　　　　With GraphBook.Sheets(1)
　　　　　　　　For i = .ChartObjects.Count To 1 Step -1
　　　　　　　　　　　　.ChartObjects(i).Delete
　　　　　　　　Next i
　　　　End With

　　　　'データの最終行を取得
　　　　rmax = DataSheet.Range("A2").End(xlDown).Row

　　　　'グラフを追加
　　　　Charts.Add
　　　　With ActiveChart
　　　　　　　　.ChartType = xlLine
　　　　　　　　.SetSourceData Source:=DataSheet.Range("C1:C" & rmax & ",E1:E" & rmax), _
　　　　　　　　　　　　　　　　PlotBy:=xlColumns
　　　　　　　　.Location Where:=xlLocationAsObject, Name:="Sheet1"
　　　　End With

　　　　'グラフの位置・大きさを設定
　　　　With ActiveSheet.Shapes(1)
　　　　　　　　.Top = Range("A1").Top
　　　　　　　　.Left = Range("A1").Left
　　　　　　　　.Width = Range("A1:F1").Width
　　　　　　　　.Height = Range("A1:A16").Height
　　　　End With

Application.ScreenUpdating = True　　　　'画面更新有効
Application.DisplayAlerts = True　　　　'確認メッセージ有効

End Sub


コレで、見た目では開いたように見えなく出来ます。
中身もちょっとだけいじってみましたので、参考になさってください。

この回答へのお礼

まるまる使わせていただきました。
ありがとうございます。

お礼日時：2013/05/11 07:50

No.5 回答者： xls88 回答日時： 2013/04/26 19:15

＞No.3 この回答へのお礼

拡張子も含め正しいブック名で試されましたか？
そのブックは開いていますか？
エラー内容はどうなっていましたか？

確認したいのですが
元ブックを開く
シートを挿入
挿入したシートにグラフ作成
元データはSheet1
といった流れでいいのですか？

この回答へのお礼

tsubuyakiさんで解決致しました。
今後ともご指導よろしくお願い致します。

お礼日時：2013/05/11 07:54

No.3 回答者： xls88 回答日時： 2013/04/26 00:20

Aブックが開いている、元データはSheet1にあると仮定

シートを挿入＞挿入したシートにグラフ作成
とやってみました。

Dim wbk As Workbook, wst As Worksheet
Dim drng As Range, crng As Range
Dim rmax As Long

Set wbk = Workbooks("A.xlsx")
With wbk
Set wst = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
End With
wst.Name = Format(Now, "yyyymmdd")

With wbk.Sheets("Sheet1")
rmax = .Range("A2").End(xlDown).Row
Set drng = .Range("A1:A" & rmax & ",C1:C" & rmax & ",E1:E" & rmax)
End With
Set crng = wst.Range("A1:F16")

With crng
With wst.ChartObjects.Add(.Left, .Top, .Width, .Height).Chart
.ChartType = xlLine
.SetSourceData Source:=drng, PlotBy:=xlColumns
End With
End With

SetSourceDataで元データをセットしています。
A1が空白でないと上手くグラフがプロットされません。

＞No.2 この回答への補足
ファイルを開かずにグラフ作成は無理だと思います。

この回答へのお礼

Aにブック名を入力しましたが、Set wbk = Workbooks("A.xlsx")の欄でエラーが出てしまい、無知ながらにも色々とがんばりましたが解決できませんでした。
ご回答ありがとうございました。
また質問させていただきますので、ご指導のほどよろしくお願いいたします。

お礼日時：2013/04/26 16:26

No.2 回答者： xls88 回答日時： 2013/04/25 17:10

現状のコードに少しだけ（★1～★2）手を入れてみました。

少し危なっかしいですが動きます。
条件が整っていればブックを開き挿入したシートにグラフが作成されます。
「ファイルを開いてセルに表示」を実行してみてください。

Sub ファイルを開いてセルに表示()
Dim OpenFileName As String
Dim Filename As String

OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OpenFileName <> "False" Then
Filename = Dir(OpenFileName)
ActiveSheet.Cells(1, 7) = Filename
Workbooks.Open OpenFileName '★1
Call グラフを作成し別シートに貼り付け　'★2
Else
MsgBox "キャンセルされました"
End If
End Sub

Sub グラフを作成し別シートに貼り付け()
'可変範囲折れ線グラフを作成
Dim hani As String
Dim shname As String
Dim rmax As Long

shname = ActiveSheet.Name 'シート名を記憶
rmax = Range("A2").End(xlDown).Row '最終行
hani = "C1:C" & rmax & ",E1:E" & rmax
Range(hani).Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.Location Where:=xlLocationAsObject, Name:=shname
ActiveChart.SeriesCollection(1).XValues = "='" & shname & "'!R2C1:R" & rmax & "C1"
'折れ線グラフを切り取り貼り付け
ActiveChart.Parent.Cut
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = Format(Now(), "グラフ1")
ActiveSheet.Paste
With Range("A1:F16")
ActiveSheet.ChartObjects("グラフ 1").Width = .Width
ActiveSheet.ChartObjects("グラフ 1").Height = .Height
End With
ActiveSheet.ChartObjects(1).Name = "全体グラフ"
End Sub

# VBAでは大概の場合Selectしなくても大丈夫です。
# SelectとかActiveSheetとかは止めましょう。
# できるだけシート名、更にはブック名を書くようにします。
≪参考≫
グラフ操作関連のテクニック
http://moug.net/tech/exvba/0021.htm
Excel 2007以降のグラフ
http://officetanaka.net/excel/vba/graph/index.htm

この回答への補足

ご回答ありがとうございます。
正常に動作致しました。

やはりファイルを開かないと、グラフ作成のマクロを実行するのは難しいのでしょうか？可変範囲設定しているグラフなので、ファイルを開いた方が良いとは思いますが。
ファイルを開かずにグラフを作成出来ればと考えているので、方法があれば教えて頂けないでしょうか。
よろしくお願いします。

補足日時：2013/04/25 21:12

この回答へのお礼

ご回答ありがとうございます。

お礼日時：2013/04/26 16:27

No.1 回答者： tsubuyuki 回答日時： 2013/04/25 16:05

このコードのままでも、

グラフを作成し、別シートに貼り付けるのは可能だと思いますが、
「最終的に何を目指して」手直ししたら良いでしょう？

補足くださいませ。

この回答への補足

マクロの名前では「ファイルを開いて」と書いてるので誤解を招いてしまいました。
ファイルを開くのではなく、読み込んでいる（今回初めての事なので読み込んでるのかもわかりませんが）のでグラフ作成の際、、読み込んだファイルのSheet1を参照にしなくてはいけないと思うのですが、その方法が分かりません。
うまく説明できませんがよろしくお願いいたします。

補足日時：2013/04/25 20:53

この回答へのお礼

ご回答ありがとうございます。

お礼日時：2013/04/26 16:27