[Solved] Download Multiple files from Google Drive

M

mfaisal.ce

Member
Dear,

Is there any way to download multiple files from Google drive when i open my workbook? I found one code from internet to download single file and it works but i can not use this code for multiple files in a single workbook.

Regards,
 
mfaisal.ce said:
Dear,

Is there any way to download multiple files from Google drive when i open my workbook? I found one code from internet to download single file and it works but i can not use this code for multiple files in a single workbook.

Regards,
Click to expand...
Hello Mfaisal.ce,

I understand you wish to download multiple files in a single workbook from google drive. I might be able to help you if you could share the code you are currently using to download a single file. Thanks in advance for your cooperation.

Regards,
Yousuf Shovon
 
mfaisal.ce said:
Dear,

Is there any way to download multiple files from Google drive when i open my workbook? I found one code from internet to download single file and it works but i can not use this code for multiple files in a single workbook.

Regards,
Click to expand...
Dear Mfaisal.ce,

You mentioned you used a code to download a single file. For multiple files, would you prefer another way? If you do, here is another easy way to download multiple files from Google Drive in a single workbook in Excel.

Utilize Google Drive for Desktop to mount a drive letter to it. To do so, follow the below steps.
  • Install Google Drive for Desktop on your computer and sign in to your Google account.
  • Go to Preferences in the settings to sync the folder that contains the files.
Downloading files from Google Drive-1.png
  • Go to the Google Drive folder in This PC and right-click on the files you wish to download.
  • In the menu bar, tap on Offline access > Available offline.
Downloading files from Google Drive-1.2.png
  • Open Microsoft Excel and create a new workbook.
  • Go to the Data tab > Get Data > From File > From Folder in Excel.
Downloading files from Google Drive-2.png
  • Afterward, select the files you wish to download from Google Drive on your desktop.
  • Click on Load to finally download files.
Downloading files from Google Drive-2.3.png
  • Thus, we download multiple files in a single worksheet at once.
Downloading files from Google Drive-3.png
I hope this works for you. Good luck!

Regards,
Yousuf Shovon
 
Dear,

Thanks for Help. but maybe it will not solve my issue; Following is the code which i am using to single file...


''code Start here


Private Sub Download()
Dim myOriginalURL As String
Dim myURL As String
Dim FileID As String
Dim Dt As String
Dim xmlhttp As Object
Dim FolderPath As String
Dim FilePath As String
Dim name0 As Variant
Dim oStream As Object
Dim wasDownloaded As Boolean


Application.ScreenUpdating = False
''URL from share link or Google sheet URL or Google doc URL

myOriginalURL = myOriginalURL = "https://docs.google.com/spreadsheet...ouid=103295798554857393913&rtpof=true&sd=true"
FileID = Split(myOriginalURL, "/d/")(1) ''split after "/d/"
FileID = Split(FileID, "/")(0) ''split before "/"
Const UrlLeft As String = "http://drive.google.com/u/0/uc?id="
Const UrlRight As String = "&export=download"
myURL = UrlLeft & FileID & UrlRight
Debug.Print myURL
Set xmlhttp = CreateObject("winhttp.winhttprequest.5.1")
xmlhttp.Open "GET", myURL, False ', "username", "password"
xmlhttp.Send

name0 = xmlhttp.getResponseHeader("Content-Disposition")
If name0 = "" Then
MsgBox "file name not found"
Exit Sub
End If

Debug.Print name0
name0 = Split(name0, "=""")(1) ''split after "=""
name0 = Split(name0, """;")(0) ''split before "";"
' name0 = Replace(name0, """", "") ' Remove double quotes
Debug.Print name0

FilePath = "D:\" & name0 & ".xlsx"



''This part is equvualent to URLDownloadToFile(0, myURL, FolderPath & "\" & name0, 0, 0)
''just without having to write Windows API code for 32 bit and 64 bit.
If xmlhttp.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write xmlhttp.responseBody
oStream.SaveToFile FilePath, 2 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If





' CLOSE THE SOURCE FILE.
src.Close False ' FALSE - DON'T SAVE THE SOURCE FILE.
Set src = Nothing



ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub



Function FileExists(FilePath As String) As Boolean
Dim TestStr As String
TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
FileExists = False
Else
FileExists = True
End If
End Function


"code ends here


Above code downloads the file. but i have 2 more files for download; links are below
1. https://docs.google.com/spreadsheet...ouid=103295798554857393913&rtpof=true&sd=true

2. https://docs.google.com/spreadsheet...ouid=103295798554857393913&rtpof=true&sd=true



Hope, i cleared by idea and problem.

Regards
 
mfaisal.ce said:
Dear,

Thanks for Help. but maybe it will not solve my issue; Following is the code which i am using to single file...


''code Start here


Private Sub Download()
Dim myOriginalURL As String
Dim myURL As String
Dim FileID As String
Dim Dt As String
Dim xmlhttp As Object
Dim FolderPath As String
Dim FilePath As String
Dim name0 As Variant
Dim oStream As Object
Dim wasDownloaded As Boolean


Application.ScreenUpdating = False
''URL from share link or Google sheet URL or Google doc URL

myOriginalURL = myOriginalURL = "https://docs.google.com/spreadsheet...ouid=103295798554857393913&rtpof=true&sd=true"
FileID = Split(myOriginalURL, "/d/")(1) ''split after "/d/"
FileID = Split(FileID, "/")(0) ''split before "/"
Const UrlLeft As String = "http://drive.google.com/u/0/uc?id="
Const UrlRight As String = "&export=download"
myURL = UrlLeft & FileID & UrlRight
Debug.Print myURL
Set xmlhttp = CreateObject("winhttp.winhttprequest.5.1")
xmlhttp.Open "GET", myURL, False ', "username", "password"
xmlhttp.Send

name0 = xmlhttp.getResponseHeader("Content-Disposition")
If name0 = "" Then
MsgBox "file name not found"
Exit Sub
End If

Debug.Print name0
name0 = Split(name0, "=""")(1) ''split after "=""
name0 = Split(name0, """;")(0) ''split before "";"
' name0 = Replace(name0, """", "") ' Remove double quotes
Debug.Print name0

FilePath = "D:\" & name0 & ".xlsx"



''This part is equvualent to URLDownloadToFile(0, myURL, FolderPath & "\" & name0, 0, 0)
''just without having to write Windows API code for 32 bit and 64 bit.
If xmlhttp.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write xmlhttp.responseBody
oStream.SaveToFile FilePath, 2 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If





' CLOSE THE SOURCE FILE.
src.Close False ' FALSE - DON'T SAVE THE SOURCE FILE.
Set src = Nothing



ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub



Function FileExists(FilePath As String) As Boolean
Dim TestStr As String
TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
FileExists = False
Else
FileExists = True
End If
End Function


"code ends here


Above code downloads the file. but i have 2 more files for download; links are below
1. https://docs.google.com/spreadsheet...ouid=103295798554857393913&rtpof=true&sd=true

2. https://docs.google.com/spreadsheet...ouid=103295798554857393913&rtpof=true&sd=true



Hope, i cleared by idea and problem.

Regards
Click to expand...
Dear Mfaisal.ce,

Thanks for the thorough explanation of the problem. It seems the given code extracts a file ID from the Google Drive URL and then creates a new URL to download the file using this ID. It then sends a GET request to this new URL and receives the response as a byte array.

However, to download multiple files at once, did you try to create an array of file URLs and loop through them to download each file? In the following code, I have created an array of file URLs called myOriginalURLs and then looped through each URL in the array using a For loop to download each file. The modified code:

Code: 
Private Sub DownloadFiles()
    Dim myOriginalURLs As Variant
    Dim myURL As String
    Dim FileID As String
    Dim Dt As String
    Dim xmlhttp As Object
    Dim FolderPath As String
    Dim FilePath As String
    Dim name0 As Variant
    Dim oStream As Object
    Dim wasDownloaded As Boolean
    Dim i As Long
    
    ' Array of file URLs
    myOriginalURLs = Array( _
        "https://docs.google.com/spreadsheet...ouid=103295798554857393913&rtpof=true&sd=true", _
        "https://docs.google.com/spreadsheet...ouid=103295798554857393913&rtpof=true&sd=true", _
        "https://docs.google.com/spreadsheet...ouid=103295798554857393913&rtpof=true&sd=true" _
    )
    
    Application.ScreenUpdating = False
    
    For i = 0 To UBound(myOriginalURLs)
        myOriginalURL = myOriginalURLs(i)
        FileID = Split(myOriginalURL, "/d/")(1)
        FileID = Split(FileID, "/")(0)
        Const UrlLeft As String = "http://drive.google.com/u/0/uc?id="
        Const UrlRight As String = "&export=download"
        myURL = UrlLeft & FileID & UrlRight
        Debug.Print myURL
        Set xmlhttp = CreateObject("winhttp.winhttprequest.5.1")
        xmlhttp.Open "GET", myURL, False ',
        xmlhttp.Send

        name0 = xmlhttp.getResponseHeader("Content-Disposition")
        If name0 = "" Then
            MsgBox "file name not found"
            Exit Sub
        End If

        Debug.Print name0
        name0 = Split(name0, "=""")(1)
        name0 = Split(name0, """;")(0)
        
        Debug.Print name0

        FilePath = "D:\" & name0 & ".xlsx"       
   
     If xmlhttp.Status = 200 Then
            Set oStream = CreateObject("ADODB.Stream")
            oStream.Open
            oStream.Type = 1
            oStream.Write xmlhttp.responseBody
            oStream.SaveToFile FilePath, 2
            oStream.Close
        End If
    Next i
    
    Application.ScreenUpdating = True
End Sub

Hope this works for you. Good luck!

Regards,
Yousuf Shovon
 
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
4
Total visitors
4
Totals may include hidden visitors.

Forum statistics

Threads
371
Messages
1,627
Members
705
Latest member
curioso

Share this page

Back
Top