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 ・・・新着記事一覧を見る

TOROW関数(配列を横1行の配列にして返す)|エクセル入門(2022-10-31)
TOCOL関数(配列を縦1列の配列にして返す)|エクセル入門(2022-10-31)
CHOOSECOLS関数(配列から複数の指定された列を返す)|エクセル入門(2022-10-29)
CHOOSEROWS関数(配列から複数の指定された行を返す)|エクセル入門(2022-10-29)
WorksheetFunctionの効率的な使い方とスピル新関数の利用|VBA入門(2022-10-27)
VSTACK関数(配列を縦方向に順に追加・結合)|エクセル入門(2022-10-25)
HSTACK関数(配列を横方向に順に追加・結合)|エクセル入門(2022-10-25)
LAMBDA以降の新関数の問題と解説(配列操作関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題集|エクセル入門(2022-10-24)


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

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




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


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



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