用用户名替换VBA中的文件路径 [英] Replace file path in VBA with user name

问题描述

我有一个写有VBA的Excel文件，该文件从计算机上的文件中提取信息.Excel文件位于网络文件夹中，我也希望网络上的其他用户也可以使用它.但是，我已经在VBA上对文件路径进行了硬编码，因此，每当另一个用户打开它时，它都会查找不可用的文件.

I have an Excel file with VBA written on it that draws information from a file on my computer. The Excel file is on a network folder and I would like for other users on the network to use it as well. However, I have hardcoded the file path on the VBA and, as such, whenever another user opens it, it looks for a file that is not available.

这是我要更改的路径:

C:\Users\User1\Documents\The Market in\DATA FOR REPORTS.xlsx

路径上的唯一区别是用户名:User1，user2等.

The only difference on the paths would be the user's name: User1, user2, etc.

如何编写VBA代码，以使其用Windows用户名将其替换为文件路径中的用户名?

How can I write the VBA code in order for it to replace the username in the file path with the Windows user name opening it?

我尝试使用通配符，也尝试使用ENVIRON("username")，但未成功.

I have tried to use wild card and also tried to use ENVIRON("username") but have not been successful.

我要替换的代码如下:

Private Sub Workbook_Open()

Application.Visible = False
WelcomeForm.Show
Workbooks.Open ("C:\Users\User1\Documents\The Market in\DATA FOR REPORTS.xlsx")

End Sub

这是我使用ENVIRON所做的:

This is what I did using ENVIRON:

Private Sub Workbook_Open()

Dim username As String

username = Environ("username")

Application.Visible = False
WelcomeForm.Show
Workbooks.Open ("C:\Users\&username&\Documents\The Market in\DATA FOR REPORTS.xlsx")

End Sub

非常感谢您

推荐答案

尝试如下操作:

Private Sub Workbook_Open()
   Application.Visible = False
   WelcomeForm.Show
   Workbooks.Open ("C:\Users\" & Environ("UserName") & "\Documents\The Market in\DATA FOR REPORTS.xlsx")
End Sub

这篇关于用用户名替换VBA中的文件路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！