Excel - 将 Google 的云端硬盘超链接替换为本地文件夹路径

Question

我有一个Excel文档，里面有谷歌的驱动器超链接到照片，我想把它们改为链接我下载到文件夹中的本地照片。这可能不需要手动操作吗？

超链接：https://drive.google.com/open?id=1yCSptfKRkbkN39Lkbz2yXLM0CI332_DC

图像名称：_storage_emulated_0_odk_instances _CASA_2018-06-22_15-29-52_152967882622.jpg

Answer 1

在我看来，你使用的是谷歌驱动器中的可共享链接-这意味着图像的文件名在链接中不可见，因此你需要打开链接来打开文件名。我们可以在VBA中通过调用浏览器对象来实现这一点，在这里，使用InternetExplorer:

Sub GetFileName()
Dim ie As Object
Set ie = CreateObject("Internetexplorer.Application")
ie.Navigate "https://drive.google.com/open?id=1yCSptfKRkbkN39Lkbz2yXLM0CI332_DC"
While ie.busy = True 'Allow the website to load
Application.Wait (Now + TimeValue("0:00:01"))
Wend
Debug.Print (ie.Document.Title)
ie.Quit
End Sub

这将为我们获得您所拥有的链接的文件名/storage/emulated/0/odk/instances/CASA_2018-06-22_15-29-52/1529678182622.jpg。正如您所说，您计算机上的文件名为：_storage_emulated_0_odk_instances_CASA_2018-06-22_15-29-52_1529678182622.jpg，我们使用replace-函数将替换为_。我们还需要删除文件名末尾的"-Google Disk"文本：

Sub GetFileName()
Dim ie As Object
Dim fname As String 'Saving filename as string for later use
Set ie = CreateObject("Internetexplorer.Application")
ie.Navigate "https://drive.google.com/open?id=1yCSptfKRkbkN39Lkbz2yXLM0CI332_DC"
While ie.busy = True 'Allow the website to load the image (wait for 1 second if browser is busy)
Application.Wait (Now + TimeValue("0:00:01"))
Wend
fname = ie.Document.Title
ie.Quit
fname = Replace(fname, "/", "_") 'Changing filename to fit your local file
fname = Replace(fname, " - Google Disk", "") 'Removing the additional text from the filename
Debug.Print (fname)
End Sub

现在我们已经完成了这项工作，我们可以循环浏览excel工作表中保存超链接的区域。我们还将确保Excel使用Hyperlinks.Add:将本地文件的路径识别为超链接

Sub GetFileName()
Dim ie As Object
Dim fname As String, wlink As String, lpath As String
lpath = "C:UsersLocalAccountDownloads" 'The folder where you have the images saved
Set ie = CreateObject("Internetexplorer.Application")
For i = 1 To 10 'Replace 1 and 10 with your first and last row of hyperlinks
wlink = Cells(i, 2).Value 'My links are in column B, hence "2". Change this to fit your sheet (1 for column A, 3 for Column C, etc.)
ie.Navigate wlink
While ie.busy = True 'Allow the website to load the image (wait for 1 second if browser is busy)
Application.Wait (Now + TimeValue("0:00:01"))
Wend
fname = ie.Document.Title
fname = Replace(fname, "/", "_")
fname = Replace(fname, " - Google Disk", "") 'Removing the additional text from the filename
fname = lpath + fname
Cells(i, 2).Value = fname 'Replaces the hyperlink with the local filename
Cells(i, 2).Hyperlinks.Add Cells(i, 2), Cells(i, 2).Value
Next i
ie.Quit
End Sub

这应该能解决你的问题——如果你有任何麻烦，请告诉我。

PS：记住将lpath变量设置为本地图像所在的文件夹路径