如何在路径中使用变量编写VBA [英] how to write a vba with a variable within the path

问题描述

这是我在该网站上的第二篇文章，对VBA来说我还是一个新手.

This is my second post on this site and an I am relatively new to VBA.

我今天的问题是

如何将单元格值添加到路径字符串"以指定要保存工作簿的文件夹.

How can I add a cell value to the Path String to designate the folder where I would like the workbook to be saved.

Dim Path As String
Dim FileName1 As String
Dim FileName2 As String

Path = "D:\folder1\folder2\Projects\The FILES\theFILES\"FileName1"\

FileName1 = Range("B6")
FileName2 = Range("A1")

ActiveWorkbook.SaveAs Filename:=Path & FileName2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

提前谢谢！

推荐答案

已回答的问题

Path = "D:\folder1\folder2\Projects\The FILES\theFILES\" & FileName1 & "\" 

另请参阅 其他反馈 和 更正后的代码 .

Also see additional feedback and corrected code for other feedback.

详细解释

将值分配给字符串变量后，最常用的方式是这样的:

When you have assign values to a string variable, the most commen way of doing so, is like this:

string = "This is my string value."

但是，您可能经常在代码中看到一个很长的字符串，例如下面的代码这样的语法，以使所有文本都可以在开发屏幕上显示而无需滚动:

However, you may often see in code with a fairly long string, syntax such as in the following code, to allow all the text to fit on the development screen without scrolling:

string = "This is my really, really, really long string value. I am making this " _
    & "as long as I can, while also having something to write."

如果您取消了_，并将所有内容移到一行，它将显示为:

If you eliminated the _, and moved everything to one line, it would read like so:

string = "This is my really, really, really long string value. I am making this " & "as long as I can, while also having something to write."

请注意，任何分配给变量的字符串都可以通过以下方式分解:

Note that any string being assigned to a variable can be broken up in such a manner:

string = "This is" & " my " & "string value."

' Returns the same result as:
string = "This is my string value."

此外，如果我有一个字符串变量str_val = " my "，则可以使用替换将上述示例编写为:

Furthermore, if I have a string variable, str_val = " my ", I could then use substitution to write the above sample as:

string = "This is" & str_val & "string value."

---

其他反馈

当前，您的代码顺序(请参见下面的 原始代码 )是

Currently, the order of your code (see original code below) is:

1. 定义变量
2. 将值分配给路径
3. 将值分配给FileName1
4. 将值分配给FileName2
5. 保存文件

不幸的是，遵循此顺序表示在步骤2)，FileName1的值是空字符串""，因为尚未为其分配值.

Unfortunately, this following this order means that at step 2), the value of FileName1 is a null string, "", as it hasn't been assigned a value yet.

因此，您应遵循的顺序是:

Therfore, the order you should be following is:

1. 定义变量
2. 将值分配给FileName1
3. 将值分配给FileName2
4. 将值分配给路径
5. 保存文件

---

字符串中的其他变量

@Davesexcel暂时发布了一个答案(然后更改)，该答案假定您字符串中的folder1和folder2也是变量.我附上了 替代代码 ，以了解是否确实如此.

There was an answer temporarily posted by @Davesexcel (then changed) which assumed that the folder1 and folder2 in your string were also variables. I attached alternative code for if that really is the case.

原始代码

Dim Path As String
Dim FileName1 As String
Dim FileName2 As String

Path = "D:\folder1\folder2\Projects\The FILES\theFILES\"FileName1"\

FileName1 = Range("B6")
FileName2 = Range("A1")

ActiveWorkbook.SaveAs Filename:=Path & FileName2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

---

更正的代码

Dim Path As String
Dim FileName1 As String
Dim FileName2 As String

FileName1 = Range("B6")
FileName2 = Range("A1")

Path = "D:\folder1\folder2\Projects\The FILES\theFILES\" & FileName1 & "\"    

ActiveWorkbook.SaveAs Filename:=Path & FileName2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

---

备用代码

Dim Path As String
Dim FileName1 As String
Dim FileName2 As String

FileName1 = Range("B6")
FileName2 = Range("A1")

Path = "D:\" & folder1 & "\" & folder2 & "\Projects\The FILES\theFILES\" & FileName1 & "\"    

ActiveWorkbook.SaveAs Filename:=Path & FileName2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

这篇关于如何在路径中使用变量编写VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！