麻辣家族討論版版 - Powered by Discuz! Board

標題: 如何大量連結sheet裡的儲存格 [打印本頁]

作者: syuan 時間: 2011-11-8 23:57 標題: 如何大量連結sheet裡的儲存格

本帖最後由 syuan 於 2011-11-9 00:00 編輯

版本：EXCEL 2003

各位先進

小弟在EXCEL的SHEET裡，已經有先列出了檔案的主檔名，想要用超連結的方式來連結檔案與儲存格，如下圖

[attach]8471[/attach]

(以上為示意的樣子）

但SHEET裡的主檔名太多（近一萬多筆，而且有好幾個sheet），一個一個去連結實在太花時間，

故而想請問各位先進，要如何一次搜尋sheet裡儲存格的主檔名來與檔案進行超連結呢？

以上，感謝各位先進～祝立冬愉快～～

作者: downling 時間: 2011-11-9 10:57

本帖最後由 downling 於 2011-11-9 10:59 編輯

假設你的檔名叫"N00-0000-2004螺絲.pdf"

可以試一下

f2=HYPERLINK("檔案所在資料夾名稱\"&B2&C2&".pdf","B2"&"c2")

複製代碼

作者: syuan 時間: 2011-11-9 22:06

假設你的檔名叫"N00-0000-2004螺絲.pdf"

可以試一下
downling 發表於 2011-11-9 10:57

感謝downling大的回覆

小弟試了一下，結果出現＂變數未定義＂的訊息！

而且Hyperlink的字串被系統給圈選了起來，不知道是哪出了問題！{:2_28:}

作者: Hsieh 時間: 2011-11-9 22:26

回復 3# syuan
樓上是教你使用函數做超連結，必須在儲存格內輸入公式

要使用VBA可依下列方式加入超連結
Sub nn()
Dim A As Range
For Each A In 檔案名稱的儲存格範圍(檔案名稱必須包含副檔名)
A.Hyperlinks.Add A, "E:\檔案目錄\" & A
Next
End Sub

作者: syuan 時間: 2011-11-11 22:19

回復 syuan
樓上是教你使用函數做超連結，必須在儲存格內輸入公式

要使用VBA可依下列方式加入超連結
...
Hsieh 發表於 2011-11-9 22:26

感謝hsieh大的回覆～
小弟沒注意到那是函數的用法，結果一直在那鑽牛角尖，真是不好意思了～～

作者: mark15jill 時間: 2011-11-16 11:13

本帖最後由 mark15jill 於 2011-11-16 12:00 編輯

回復 1# syuan
TRY IT

[attach]8556[/attach]

原始碼

Sub Macro1()
'
' Macro1 Macro
'
' 快速鍵: Ctrl+Shift+Y
'

For XX = 1 To 65535
If Range("A" & XX).Value <> "" Then

      RQ = Range("A" & XX).Value & Range("B" & XX).Value
      Range("C" & XX).Select
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=RQ & ".xlsx", _
         TextToDisplay:=RQ
End If
Next XX
End Sub

作者: syuan 時間: 2012-2-6 23:06

回復 syuan
TRY IT

原始碼

Sub Macro1()
'
' Macro1 Macro
'
' 快速鍵: Ctrl+Shift ...
mark15jill 發表於 2011-11-16 11:13

感謝mark大～～

實在很抱歉，到現在才回覆！

目前小弟正在試著悟這段程式，待有問題再來請問您！

真的謝謝您～～

作者: PJChen 時間: 2012-2-7 10:55

回復 6# mark15jill
真是好用的連結,又上了寶貴的一課!

作者: syuan 時間: 2012-11-6 10:05

回復 4# Hsieh
Hsieh您好

小弟依您所po的語法測試後，的確能連結並開啟所點選的檔案。

不過小弟將這個excel檔移到別處並加入修改後的路徑時，它就無法開啟所連結的檔案了。

也就是說這段語法似乎只能將excel檔與連結的檔放在同目錄下。

小弟寫的語法如下，請參考。

Dim A as range
Dim B
Dim C

Worksheets("sheet1").Activate
For Each A In Worksheets("sheet1").UsedRange
If InStr(A, "JIS ") And A.Value = "" Then
B = "C:\11"
C = Dir(B, ".PDF")
A.Hyperlinks.Add A, C & A
End If

不過小弟發現如果將...... and A.value="" Then改為......and A.value <>""Then 時

那麼儲存格的字串會變成一大串的路徑，例如：C:\11\aa.pdf

但這時點它時，卻是可以開啟超連結的檔案.........=="

小弟一直百思不解，不知到底是哪裡出問題。

還望各位先進不吝指教，以上～謝謝。

作者: mark15jill 時間: 2012-11-6 11:37

回復 9# syuan

B = "C:\11\"

作者: syuan 時間: 2012-11-6 12:11

回復 10# mark15jill

感謝mark15jill大的回覆

小弟的路徑裡確實少了一個反斜線，但小弟加了之後再run時，超連結的路徑依然沒改過來！

現在的語法如下：
Dim A As Range
Dim B
Dim C
Worksheets("sheet1").Activate
For Each A In Worksheets("sheet1").UsedRange
If InStr(A, "JIS ") And A.Value = "" Then
B = "C:\11\"
C = Dir(B, ".PDF")
A.Hyperlinks.Add A, C & A
End If
Next A

明明路徑是"C:\11\"，但滑鼠游標指到儲存格而秀出的小提示卻是c:\temp\....... (小弟將這個excel檔移至c:\temp內）

好奇怪，不知哪裡出了問題？

還望各位先進不吝指導，謝謝。

作者: mark15jill 時間: 2012-11-6 14:39

回復 mark15jill

感謝mark15jill大的回覆

小弟的路徑裡確實少了一個反斜線，但小弟加了之後再 ...
syuan 發表於 2012-11-6 12:11

明明路徑是"C:\11\"，但滑鼠游標指到儲存格而秀出的小提示卻是c:\temp\....... (小弟將這個excel檔移至c:\temp內）

你檔案在 temp 還是 11底下..

作者: mark15jill 時間: 2012-11-6 15:10

本帖最後由 mark15jill 於 2012-11-6 15:13 編輯

以下程式分兩部份
1.列出 c:\11\ 副檔名為 " .tif "的檔案
2.依照所列出名稱 +以連結

Private Sub CommandButton1_Click()
With 工作表1
u = 1
k = 1
'此區域列表
Dim mFile As String
mFile = Dir("C:\11\*.tif")
Do While mFile <> ""
Cells(u, 1) = mFile
mFile = Dir()
u = u + 1
'此區域超連結
With Worksheets(1)
.Hyperlinks.Add .Cells(k, 1), "c:\11\" & Cells(k, 1)
End With
k = k + 1
Loop
End With
End Sub

複製代碼

作者: syuan 時間: 2012-11-6 17:07

回復 12# mark15jill

你檔案在 temp 還是 11底下..

回覆mark15jill大，

小弟想要被連結的檔案放在C:\11底下，而excel檔則是放在C:\temp底下

因為小弟想測試如果EXCEL檔放置在別目錄下時，那超連結是否仍然可以連結到檔案。

但目前以小弟寫的那一段語法看來，路徑並沒辦法改過來。

作者: mark15jill 時間: 2012-11-6 17:13

本帖最後由 mark15jill 於 2012-11-6 17:15 編輯

回復 mark15jill

你檔案在 temp 還是 11底下..

回覆mark15jill大，

小弟想要被連結的檔案 ...
syuan 發表於 2012-11-6 17:07

Excel 檔案 (來源) 和被連結檔案(目的)
不用放在一起也可以
只要路徑改掉即可..

原始設定
PDF檔案在 C:\11\
Excel檔案在 C:\temp

基本上除非程式碼有將PDF 和 Excel 歸類同資料夾底下
不然的話 Excel檔案所在不用理會 ( 路徑指向須正確)
若 PDF 檔案原本從 C:\11\ 改成 C:\99\
只須將指向C:\11\ 改成 C:\99\

就好比
小學生上學
學校是固定地點
但小學生可能因搬家而導致地址不同

作者: syuan 時間: 2012-11-6 22:25

本帖最後由 syuan 於 2012-11-6 22:26 編輯

回復 15# mark15jill

感謝mark15jill大的回覆

小弟不死心又再試了一下這段語法，結果發現該段語法仍無法導正路徑。

明明語法寫著B="C:\11:\"，但將EXCEL檔搬至別的目錄下後，在EXCEL裡並將滑鼠指到儲存格上的超連結字串所秀出的小提示，
卻秀出C:\JIS1111-1999.PDF的提示，也就是說語法裡的B="C:\11\"是無效的。
今天一整天都在試這段語法，已經想不出到底是哪裡出問題.....

小弟附上這段語法的EXCEL檔，望各位先進不吝教導。
謝謝各位

檔案如下：
[attach]13058[/attach]

作者: Hsieh 時間: 2012-11-6 22:46

回復 16# syuan
若B的字串目錄存在則，C = Dir(B & A)將會傳回與A相同字串否則傳回空字串

Sub test()
Dim A As Range
Dim B
Dim C
Worksheets("sheet1").Activate
For Each A In Worksheets("sheet1").UsedRange
If InStr(A.Value, "JIS") > 0 And A.Value <> "" Then
B = "C:\11\"
A.Hyperlinks.Add anchor:=A, Address:=B & A
End If
Next A
End Sub

複製代碼

作者: syuan 時間: 2012-11-7 23:43

回復 17# Hsieh

感謝Hsieh大

小弟試了您的這段語法，發現儲存格會變成一整串的路徑加檔名。

只是小弟想要的是儲存格字串不變，而超連結到語法裡所指的目錄下，

如excel裡儲存格的字為JISXXXX-1999.PDF，而超連結後，儲存格的字串仍然沒變。

而使用者點擊儲存格後，即能開啟PDF檔。

請問小弟的那段語法裡，該修改為什麼呢？

以上，謝謝。

作者: Hsieh 時間: 2012-11-8 12:09

回復 18# syuan

A.Hyperlinks.Add anchor:=A, Address:=B & A, TextToDisplay:= A

作者: syuan 時間: 2012-12-4 00:29

回復 19# Hsieh

回HSIEH大

小弟有加了這句語法"A.Hyperlinks.Add anchor:=A, Address:=B & A, TextToDisplay:=A＂
發現還是無法讓VBA去連結路徑，路徑C:\11\依然被程式忽略過去。

小弟的程式碼如下：

Dim A As Range
Dim B
Dim C

Worksheets("sheet1").Activate
For Each A In Worksheets("sheet1").UsedRange
If InStr(A.Value, "JIS ") > 0 And A.Value <> "" Then
B = "C:\11\"
A.Hyperlinks.Add anchor:=A, Address:=B & A, TextToDisplay:=A
End If
Next A

而如果將這句裡的 If InStr(A.Value, "JIS ") > 0 And A.Value ＝ "" Then改為 If InStr(A.Value, "JIS ") > 0 And A.Value ＜＞ "" Then

那麼執行後，就會出現一個對話框，寫著＂執行階段錯誤'5'：　程序呼叫或引數不正確＂

而這句 If InStr(A.Value, "JIS ") > 0 And A.Value ＜＞ "" Then就會被程式反黃起來。

小弟目前已經卡關了，已經不知道該怎麼除錯。

還望各位大德協助一下，指點一下小弟看看是哪裡出問題。

感謝各位。

作者: GBKEE 時間: 2012-12-4 08:30

回復 20# syuan
沒有錯誤啊!

Sub Ex()
Dim A As Range
With Worksheets("Sheet1")
.[b5] = "JIS" '測試用
For Each A In .UsedRange
If InStr(A.Value, "JIS") > 0 And A.Value <> "" Then
'If InStr(A.Value, "JIS ") > 0 And A.Value <> "" Then '這是你的程式碼
MsgBox A.Address
End If
Next
End With
End Sub

複製代碼

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)