VBAサンプル集
GoogleスプレッドシートをExcelにインポートする

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日:2020-04-28

GoogleスプレッドシートをExcelにインポートする


Googleスプレッドシートをエクセルに取り込みたい場合、
手動のコピペでも出来ますが、もう少し自動化したくなります。
スプレッドシートをエクセルに取り込む場合の方法について解説します。


スプレッドシートのURLを取得

スプレッドシートを開いたら、右上にある「共有」をクリックします。

VBA マクロ スダレッドシートのダウンロード

「共有可能なリンクを取得」をクリックします。

VBA マクロ スダレッドシートのダウンロード

VBA マクロ スダレッドシートのダウンロード

クリップボードにURLがコピーされるので、メモ帳等に貼り付けてください。
https://docs.google.com/spreadsheets/d/1_xxxxxxxxxxxxxxxxxxxx/edit?usp=sharing
このような文字列が貼り付けられます。
これをもう1行はりつけて、最後の部分を変更してください。
https://docs.google.com/spreadsheets/d/1_xxxxxxxxxxxxxxxxxxxx/export?format=xlsx
これは後ほど使用します。

ブラウザで直接ダウンロードする

ブラウザのURLに、以下のURLを直接入力すると、Excelファイルがダウンロードされます。
https://・・・/export?format=xlsx

取り急ぎ使いたいなら、これが一番簡単です。

format
以下の指定が可能です。

xlsx

csv
pdf

他にもあるはずですが未確認です。

csvはUTF-8のBOM無しになります。
BOM無しなので、そのままエクセルで開くと文字化けします。
VBAでのCSVの扱い方まとめ
・本サイトにあるCSV関連記事一覧 ・CSVの読込方法 ・CSVの読み込み方法(改) ・CSVの読み込み方法(改の改) ・CSVの読み込み方法(ジャグ配列) ・CSVの読み込み方法(ジャグ配列)(改) ・CSVの出力(書き出し)方法 ・UTF-8でCSVの読み書き(ADODB.Stream) ・ADOでCSVの読み込み(SQL)

シート指定
https://・・・/export?format=xlsx&gid=シートのid
このようにすることで、シートを指定できますが、そもそもシートidを調べるのが面倒です。
シートを一つ一つcsvにするのは、いかにも効率が悪いので、ここでは省力します。

Power Queryで取り込む

リボン「データ」→「Webから」

VBA マクロ スダレッドシートのダウンロード

VBA マクロ スダレッドシートのダウンロード

ここに、先ほど作成した、
https://・・・/export?format=xlsx
これを貼り付けてください。
「OK」で進むと、

VBA マクロ スダレッドシートのダウンロード

左側でシートを選択すると、プレビューが表示されます。

VBA マクロ スダレッドシートのダウンロード

「読み込み」をクリックすると、シートにテーブルとして出力されます。

複数シートがある場合は、1シートずつ実行する必要があります。
少し面倒ですね。

VBAでのPower Query利用方法については、以下を参照してください。
PowerQueryの強力な機能をVBAから利用する方法
・説明に使用するデータ ・PowerQueryの操作方法 ・PowerQueryのマクロの記録 ・PowerQueryのVBA文法 ・PowerQueryの機能を利用したマクロVBA ・PowerQueryについて

VBAでGoogleスプレッドシートをインポートする

Excelファイルとしてダウンロードして、全シートを取り込みます。

Option Explicit

Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
  (ByVal pCaller As Long, _
   ByVal szURL As String, _
   ByVal szFileName As String, _
   ByVal dwReserved As Long, _
   ByVal lpfnCB As Long) As Long

'Googleスプレッドシートをインポートする
Sub MainSample()
  Dim strURL As String
  strURL = "https://docs.google.com/spreadsheets/d/1_xxxxxxxxxxxxxxxxxxxx/"
  
  Dim strFile As String
  strFile = GetSpreadsheet(strURL)
  
  Application.ScreenUpdating = False
  Call GetAllSheets(ThisWorkbook, strFile)
  Application.ScreenUpdating = True
  
  MsgBox "インポートしました。"
End Sub

'APIのURLDownloadToFileでxlsxをダウンロード
Function GetSpreadsheet(ByVal argURL As String) As String
  Dim outFile As String
  outFile = ThisWorkbook.Path & "\" & "TEMP_" & Format(Now(), "yyyymmddhhmmss") & ".xlsx"
  
  If argURL Like "*edit?usp=sharing" Then
    argURL = Replace(argURL, "edit?usp=sharing", "")
  End If
  argURL = argURL & "export?format=xlsx"
  
  Call URLDownloadToFile(0, argURL, outFile, 0, 0)
  GetSpreadsheet = outFile
End Function

'ダウンロードしたxlsxの全シートの取込
Sub GetAllSheets(targetBook As Workbook, ByVal strFile As String)
  Dim wb As Workbook
  Set wb = Workbooks.Open(FileName:=strFile, ReadOnly:=True)
  
  Dim ws As Worksheet
  For Each ws In wb.Sheets
    ws.Copy after:=targetBook.Sheets(targetBook.Sheets.Count)
  Next
  
  wb.Close SaveChanges:=False
  Kill strFile
End Sub

VBAは特に難しい部分は無いと思います。
ダウンロードしたファイルの削除等は、適宜修正してください。



同じテーマ「マクロVBAサンプル集」の記事

ハイパーリンクからファイルのフルパスを取得する
ボタンに表示されているテキストを取得(Application.Caller)
Excelの表をPowerPointへ図として貼り付け
VBAで表やグラフをPowerPointへ貼り付ける
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除
Shift_JISのテキストファイルをUTF-8に一括変換
VBAコードの全プロシージャー・プロパティ一覧を取得
数式バーの高さを数式の行数で自動設定
図形オートシェイプ(Shape)の複数選択
GoogleスプレッドシートをExcelにインポートする
多階層フォルダ(ディレクトリ)の作成


新着記事NEW ・・・新着記事一覧を見る

抜けている数値を探せ|エクセル雑感(2022-07-01)
.Net FrameworkのSystem.Collectionsを利用|VBA技術解説(2022-06-29)
迷路ネコが影分身の術を体得したら…|エクセル雑感(2022-06-27)
迷路にネコが挑戦したら、どうなるかな…|エクセル雑感(2022-06-26)
サロゲートペアに対応した自作関数(Len,Left,Mid,Right)|エクセル雑感(2022-06-24)
「マクロの登録」で登録できないプロシージャーは?|エクセル雑感(2022-06-23)
オブジェクトのByRef、ByVal、Variant|エクセル雑感(2022-06-22)
コメントから特定形式の年月を取り出す|エクセル雑感(2022-06-19)
4,9を使わない連番作成|エクセル雑感(2022-06-17)
連番を折り返して出力|エクセル雑感(2022-06-16)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.マクロって何?VBAって何?|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ