VBA目录引用 [英] VBA Directory Referencing
问题描述
Path = Application.GetOpenFilename
现在路径固定在'S:\SN \SN\2015-S [2015SNL.xlsx] 2015'
Sub Formulation()
/ pre>
Application.ScreenUpdating = False
表格(Data)激活
范围(A1)。FormulaR1C1 = _
= IF(ISBLANK 'S:\SN\SN\2015-S\ [2015SNL.xlsx] 2015'!RC),_
- ,'S:\SN\SN\2015 -S \ [2015SNL.xlsx] 2015'!RC)
Application.ScreenUpdating = True
End Sub
它也引用了特定工作表,您可以看到 ... 2015年!!
任何想法?感谢您的高级帮助
解决方案要将变量添加到VA中的字符串中,您需要关闭引号,跟随并符号:
&
。
像这样:
范围(A1)FormulaR1C1 == IF(ISBLANK('& Path&2015'!RC), - ,& Path&2015!RC)
确保在引号,和号和变量名之间添加一个空格。
I'm trying to reference data from another workbook and I found that (correct me if I'm wrong) inserting the worksheet formulas directly into the cells (see code below) is the only way to do this without actually opening the workbook I'm referring to. Now my dilemma is I'm trying to make the directory portion of this code variable such that the user can do something like:
Path = Application.GetOpenFilename
Right now the path is fixed at 'S:\SN\SN\2015-S[2015SNL.xlsx]2015'
Sub Formulation() Application.ScreenUpdating = False Sheets("Data").Activate Range("A1").FormulaR1C1 = _ "=IF(ISBLANK('S:\SN\SN\2015-S\[2015SNL.xlsx]2015'!RC), _ ""-"",'S:\SN\SN\2015-S\[2015SNL.xlsx]2015'!RC)" Application.ScreenUpdating = True End Sub
It is also referencing to a specific sheet as you can see the ...2015'!
Any ideas? Thank you in advanced for your assistance
解决方案To add in variables into a string in VA, you need to close the quotes, and follow with and ampersand:
&
.Like so:
Range("A1").FormulaR1C1 = "=IF(ISBLANK('" & Path & "2015'!RC),""-"",'" & Path & "2015'!RC)"
Make sure to add a space between the quotes, the ampersand and the variable name.
这篇关于VBA目录引用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!