iT邦幫忙

0

excel自動更新超連結

第n週報表內的B2=A2+'[第(n-1)週報表.xlsx]Sheet1'!$B$2
B2內的數值是否有方式能夠自動更新?
檔案名稱格式是固定的,每週會複製前一周的檔案改檔名第(n-1)週報表.xlsx後輸入A2數字,
B2的內容是否能夠自動算出,不用再自己修改連結"第(n-1)週報表.xlsx"

能接受用VBA嗎?
另外,如果n=1呢?該不會有"第0週報表.xlsx"吧?
第三,你的公式會出現`A2+'[第(n-1)週報表.xlsx]Sheet1'!$B$2`是因為`第(n-1)週報表.xlsx`為開啟的狀態,如果關閉的話`第(n-1)週報表.xlsx`會變成甚麼?
感謝回覆^^

1) 可以接受VBA
2) 不會有"第0週報表.xlsx"
3) 關閉的話會變成=A2+'C:\Users\Assistant\Desktop\report\[第6週週報表.xlsx]Sheet1'!$B$2

1 個回答

0
paicheng0111
iT邦高手 1 級 ‧ 2018-06-19 14:39:21
最佳解答
  1. 在EXCEL中,用ALT+F11開啟VBE。
  2. 在VBE中,插入/模組,建立一個新模組。
  3. 把下列程式碼,複製貼上該模組。
  4. 關閉VBE,回到EXCEL
  5. 每次要執行時,在EXCEL中按下ALT+F8,選renewRef並執行即可。
Sub renewRef()
    Dim thisWbk As String, n As Variant
    Dim strPath As String, oldWbk As String
    
    thisWbk = ThisWorkbook.Name
    strPath = ThisWorkbook.Path
    
    With CreateObject("vbscript.regexp")
      .Global = True
      .IgnoreCase = True
      .MultiLine = True
      .Pattern = "第(\d*)週週報表\.xlsx"
      If .test(thisWbk) Then n = .Execute(thisWbk)(0).submatches(0)
    End With
        
    If n > 1 Then
        oldWbk = Replace(thisWbk, n, n - 1)
        If Dir(strPath & "\" & oldWbk) <> "" Then
          ThisWorkbook.Worksheets(1).Range("B2").Formula = "=A2+'" & strPath & "\[" & oldWbk & "]Sheet1'!$B$2"
        Else
          ThisWorkbook.Worksheets(1).Range("B2").Value = "找不到檔案:" & oldWbk
        End If
    End If
End Sub

非常感謝幫忙!!!/images/emoticon/emoticon41.gif

還能夠更自動。
例如檔案存檔時,更新B2儲存格的公式。
有需要的話再説。

我要發表回答

立即登入回答