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

標題: 如何利用VBA來達到 "連結" 效果? [打印本頁]

作者: RCRG 時間: 2016-1-11 13:51 標題: 如何利用VBA來達到 "連結" 效果?

問題
如何利用VBA來達到 "連結" 效果?

詳細問題內容
使用檔的工作表a 和修改檔的工作表A，內容是完全一模一樣，
因為是利用='C:\Documents and Settings\桌面\利用VBA更新連結\修改資料夾\[修改檔.xls]工作表A'!A1
的公式來連結兩個不同路徑的活頁簿工作表內容，
不知有沒辦法改利用VBA來達到這種效果，(VBA裡面請幫我註明修改路徑的地方)，
我想要打開活頁簿就會自動更新修改檔的連結內容而傳到使用檔；
另外如果沒有內容，可以不要顯示0而是維持空白嗎?

另外如果像是工作表B & b，有限制範圍的讀取來源，VBA又該如何寫呢?

以上問題，請大大指教幫解了！謝謝！

下載說明檔
[attach]23085[/attach]

作者: RCRG 時間: 2016-1-13 12:49

問題
如何利用VBA來達到 "連結" 效果?

詳細問題內容
使用檔的工作表a 和修改檔的工作表A，內容是完全 ...
RCRG 發表於 2016-1-11 13:51

簡單來講就是，是否能利用VBA來複製 "另一個活頁簿的工作表" 內容，而且是每開啟活頁簿就執行一次複製工作表的動作。

作者: RCRG 時間: 2016-1-15 12:48

回復 1# RCRG

嗚嗚...! 難道只能利用工作表a'A1=工作表A'!A1 的公式手法來連結兩工作表的內容嗎? 還是有甚麼地方是我敘述不清的，求VBA高手解惑！謝謝！QQ

作者: stillfish00 時間: 2016-1-15 14:26

本帖最後由 stillfish00 於 2016-1-15 14:32 編輯

回復 3# RCRG
如果只是要連線其他EXCEL內的Table
可用資料>(取得外部資料)從其他來源>從Microsoft Query>
Excel files
建立該Table的連線

https://support.office.com/zh-TW ... 3-9c38-4c62f252da2e
建議你多看看OFFICE的說明

但是這跟你2#說的複製貼上是兩回事

作者: RCRG 時間: 2016-1-15 17:38

回復 4# stillfish00

我的用意只是要能 "透過活頁簿A去修改活頁簿B"，因為活頁簿B是寫給使用者在用的，所以他們可能會複製去使用而且放在任何路徑，不過沒關係，我只要在活頁簿B寫入VBA(或者像目前用連結更新的方式)，就能隨時透過活頁簿A間接達到修改活頁簿B的需求；
也因為活頁簿A和活頁簿B兩者內容會一模一樣，所以我才會用到 "複製" 字眼，不太會形容與表達，歹勢！XD
我只是在想，除了用更新連結方式，是否能透過VBA，或者有更聰明的辦法呢? 因為感覺更新連結資料量會比較大，畢竟可能是幾千列的內容。

作者: RCRG 時間: 2016-1-23 16:07

回復 5# RCRG

Microsoft Query資料連結的方式自己弄了半天還是不會用，都會出現 "此資料來源中並未存在可使用的表格" ，所以這方面就放棄了...XD
硬是給他用工作表a'A1=工作表A'!A1 的公式來連結兩個活頁簿的資料，實測結果就是開檔慢、存檔也慢，每次時間就是3、40秒起跳；
試著自己在"使用檔"錄巨集，但是"修改檔"一關閉檔案或移動路徑，巨集就完全沒用了！...求助無門了....QQ

Sub 巨集1()
'
' 巨集1 巨集
'

'
Windows("修改檔.xls").Activate
Cells.Select
Selection.Copy
Windows("使用檔.xls").Activate
Cells.Select
ActiveSheet.Paste
End Sub

作者: ML089 時間: 2016-1-23 23:50

本帖最後由 ML089 於 2016-1-23 23:51 編輯

回復 6# RCRG

'VBA程式放在使用檔.XLS中 ThisWorkbook
Private Sub Workbook_Open()
xF = "'C:\Documents and Settings\桌面\利用VBA更新連結\修改資料夾\[修改檔.xls]工作表A'!R1C1:R999C7"
Workbooks("使用檔.xls").Worksheets("工作表a").Range("A1:G999").FormulaArray = "=if(" & xF & "="""",""""," & xF & ")"
End Sub

複製代碼

作者: RCRG 時間: 2016-1-24 13:54

回復 7# ML089

謝謝ML089大大的解答，我要的形式就是這樣，等等再測試看透過您的VBA和我本來的直接公式連結，程式跑起來會不會還是很花時間；
對了！所以這是開檔複製另一個活頁簿的"數值"吧，因為字體(儲存格)顏色、字體大小、格式、甚至註解好像都無法複製！

作者: RCRG 時間: 2016-1-24 18:50

回復 7# ML089

請教一下M大，我把您的範圍"A1:G999"改成其他範圍(如："A1:BB500" 或 "U1:CC300"..等)，好像就會出現錯誤，
自己研究了一下好像是上面這個R1C1:R999C7也要跟著修改沒錯吧，可是我不懂R1C1:R999C7是什麼意思，不知能否說明一下；
或者除了R1C1:R999C7，還有甚麼地方也需一起修改呢?

作者: ML089 時間: 2016-1-24 19:35

回復 9# RCRG

R1C1:R999C7

R1:第1列
C1:第1欄
等於 A1

R999:第999列
C7:第7欄
等於 G999

U1:CC300 = R1C21:R300C81
COLUMN(U1)=21
COLUMN(CC300)=81

作者: ML089 時間: 2016-1-24 19:38

回復 8# RCRG

這樣才能不開 "修改檔" ，"使用檔"又能同步更新

作者: RCRG 時間: 2016-1-25 12:22

回復 10# ML089

為什麼我範圍自己改成如下，VBA還是會跳出問題視窗呢

'VBA程式放在使用檔.XLS中 ThisWorkbook
Private Sub Workbook_Open()
xF = "'C:\Documents and Settings\桌面\利用VBA更新連結\修改資料夾\[修改檔.xls]工作表A'!R1C1:R500C81"
Workbooks("使用檔.xls").Worksheets("工作表a").Range("A1:CC500").FormulaArray = "=if(" & xF & "="""",""""," & xF & ")"
End Sub

作者: ML089 時間: 2016-1-25 13:57

回復 12# RCRG

我測試應該沒有問題

先傳檔案上來看看

作者: 准提部林 時間: 2016-1-25 15:26

複製內容含格式及註解, 沒什麼方便法, 老實用複製貼上吧!!

Private Sub Workbook_Open()
Dim MyB As Workbook, xB As Workbook, xFile$
Set MyB = ThisWorkbook
xFile = ThisWorkbook.Path & "\修改資料夾\修改檔.xls" '路徑檔名自行修改
Application.ScreenUpdating = False
Set xB = Workbooks.Open(xFile, ReadOnly:=True)
xB.Sheets("工作表A").[A1:H500].Copy MyB.Sheets("工作表a").[A1]
xB.Sheets("工作表B").[AA1:AB20].Copy MyB.Sheets("工作表b").[AA1]
xB.Close 0
End Sub

來源檔手動開啟有多快, 這程式就一樣的快, 反之亦然~~

作者: RCRG 時間: 2016-1-28 13:50

回復 ML089

為什麼我範圍自己改成如下，VBA還是會跳出問題視窗呢

'VBA程式放在使用檔.XLS中 ...
RCRG 發表於 2016-1-25 12:22

回復 13# ML089

我大概找到問題所在了，好像是要把產生的陣列公式整個移除，才會正常；
也就是說使用檔裡，不能有跟上一次不同連結的陣列公式，有的話需全部移除，讓他從空白頁中再跑新的連結出來，才不致發生異常。
以上是我使用M大版的心得，也非常謝謝您第一時間為我解答。

作者: RCRG 時間: 2016-1-28 14:00

回復 14# 准提部林

所以准大的VBA是套用複製貼上的方法囉?

那我想了解詳細的VBA內容是 1. 複製來源直接貼上覆蓋目標
還是 2. 複製來源，清除目標舊內容，再貼上目標

是哪種模式呢? 因為如果是第1個模式，那目標本來有VBA按鈕，是不是就無法覆蓋VBA按鈕了
因為准大的vba如果來源有vba按鈕好像也會一併複製，所以不知道會不會每開檔一次就複製貼上越來越多按鈕過去

作者: 准提部林 時間: 2016-1-28 16:47

回復 16# RCRG

按鈕有人會放在資料區嗎？

可先將來源工作表設為保護，貼好後再解除保護（按鈕須設為－鎖定）
With xB.Sheets("工作表A")
   .Protect
   .[A1:H500].Copy MyB.Sheets("工作表a").[A1]
   .UnProtect
End With

作者: RCRG 時間: 2016-1-28 18:29

回復 17# 准提部林

如果來源檔( 修改檔)與目標檔(使用檔)都被保護著，那麼VBA我自己改成如下，但用起來感覺有點怪怪的，好像有時會複製成功，有時會沒複製到，是那兒有問題嗎?

Private Sub Workbook_Open()
Dim MyB As Workbook, xB As Workbook, xFile$
Set MyB = ThisWorkbook
xFile = ThisWorkbook.Path & "\修改資料夾\修改檔.xls" '路徑檔名自行修改
Application.ScreenUpdating = False
Set xB = Workbooks.Open(xFile, ReadOnly:=True)

ActiveSheet.Unprotect "123" '先取消保護

xB.Sheets("工作表A").[A1:H500].Copy MyB.Sheets("工作表a").[A1]
xB.Sheets("工作表B").[AA1:AB20].Copy MyB.Sheets("工作表b").[AA1]
xB.Close 0

ActiveSheet.Protect "123" '重新設定保護
End Sub

作者: 准提部林 時間: 2016-1-28 20:30

回復 18# RCRG

１．先去弄清楚　ActiveSheet　是什麼意思？　　〔當前工作表〕，它不一定代表是來源表或目標表
２．複製時，來源表應是保護，目標表是未保護，才能正常做〔貼上〕，
　　可先手動去做一次〔複製．貼上〕試試就知道
　

作者: RCRG 時間: 2016-1-29 10:56

回復 19# 准提部林

原來如此，"不一定是目標檔或來原檔" 這句提醒了我;

那可以改幫我寫成貼上目標檔前，先解開目標檔的保護，再貼上目標檔，最後再恢復目標檔的保護嗎?

作者: 准提部林 時間: 2016-1-29 14:28

回復 20# RCRG

Set xB = Workbooks.Open(xFile, ReadOnly:=True)
xB.Sheets("工作表A").Protect "123"
With MyB.Sheets("工作表a")
　　.Unprotect "123"
　　xB.Sheets("工作表A").[A1:H500].Copy .[A1]
　　.Protect "123"
End With
　
xB.Sheets("工作表B").Protect "123"
With MyB.Sheets("工作表b")
　　.Unprotect "123"
　　xB.Sheets("工作表B").[AA1:AB20].Copy .[AA1]
　　.Protect "123"
End With
xB.Close 0

既然已知語法，為何不自己去試作？
這VBA就是要自己動手去試作，從錯誤中去修去改，才能了解其機制，
只用問來的直接去貼用，永遠學不了！！！

作者: RCRG 時間: 2016-2-5 18:51

回復 21# 准提部林

謝謝准大指教，有時間的話的確自己測試遠比貼上收穫的多，也很羨慕你們能用專業替他人解答，這是我們新手努力的目標，再次謝謝解題。

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