时间戳

首页 > 解决方案 > Print path of input file

excel - Print path of input file

问题描述

I have developed a macro that imports to my main workbook some data from other workbooks, which are stored in different folders, and keeps the link instead of doing the simplye copy-paste xlvalues. Thereby an extract of the code

With Master.Sheets("Sheet1")
     .Activate
     .Range("F4").Select
     .Paste Link:=True
End With

In this way, the cells of the workbook "Master" display the link, for example:

"'C:\Users\Documents\[Input.xlsx]Sheet1'!E29"

Is there a macro code which would allow me to extract from the abovementioned link only the path to the relevant workbook (i.e. 'C:\Users\Documents\[Input.xlsx]) so that I can print it in a summary report to display the path of the inputs?

标签： excelvba

解决方案

Please, try the next piece of code:

Sub extractPathFromLink()
  Dim x As String, path As String
  x = "'C:\Users\Documents\[Input.xlsx]Sheet1'!E29"
  path = Split(Split(x, "'")(1), "]")(0) & "]"
  MsgBox path
End Sub

Edited:

The next version returns the real workbook path:

Sub extractPath()
  Dim x As String, path As String
  x = "'C:\Users\Documents\[Input.xlsx]Sheet1'!E29"
  path = Replace(Split(Split(x, "'")(1), "]")(0), "[", "")
  MsgBox path
End Sub

推荐阅读