获取Excel VBA的文件位置和名称 [英] Getting File Location and Name for Excel VBA

问题描述

我正在创建一个VBA程序，它将一个列从一个文件复制到另一个文件.

I am creating a VBA program that will copy one column from one file to another.

当前代码有效，但是我希望将其更改为出现提示的位置，并要求用户提供文件位置和名称/扩展名.该输入将作为Workbooks.Open函数的文件位置导入，然后从那里输入.

The current code works, but I wish to change it to where a prompt will come up and ask the user for the file location and name / extension. That input will be imported as the file location for the Workbooks.Open function and go from there.

如何创建一个提示，要求用户输入所需excel文件的文件位置和名称，并将其输入到Workbooks.Open函数中?

How do I create a prompt to ask for the user to input the file location and name for the desired excel file, and have it input in the Workbooks.Open function?

代码:

Sub Macro1()

Dim wb1 As Workbook
Dim wb2 As Workbook

MsgBox "Now converting data from Incident Data to Specific Data "

'Set it to be the file location, name, and extension of the Call Data CSV
Set wb1 = Workbooks.Open("Z:\xxxx\Call Data - Copy.csv")

'Set it to be the file location of the Working File
Set wb2 = Workbooks.Open("Z:\xxxx\Working File.xlsx")

wb1.Worksheets(1).Columns("E").Copy wb2.Worksheets(1).Columns("A")
wb1.Worksheets(1).Columns("I").Copy wb2.Worksheets(1).Columns("Q")
wb1.Worksheets(1).Columns("AE").Copy wb2.Worksheets(1).Columns("R")
wb1.Worksheets(1).Columns("BD").Copy wb2.Worksheets(1).Columns("F")

wb2.Close SaveCahnges:=True
wb1.Close SaveChanges:=True

End Sub

推荐答案

我将与FileDialog一起选择输入文件:

I would go with FileDialog to select an input file:

Dim fDialog As FileDialog, result As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

fDialog.AllowMultiSelect = False
fDialog.title = "Select a file"
fDialog.InitialFileName = "C:\"
fDialog.Filters.Clear
fDialog.Filters.Add "Excel files", "*.xlsx"

'Show the dialog. -1 means a file has been successfully selected
If fDialog.Show = -1 Then
   Debug.Print fDialog.SelectedItems(1)
End If

要保存，您可以参考以下

For saving you can refer to this post

要在工作簿中使用它，只需执行以下操作即可打开

To use it in Workbooks.Open you just do something like the following:

Dim fname As String
If fDialog.Show = -1 Then
   fname=fDialog.SelectedItems(1)
Else 
   MsgBox("Filename selection error")
   Exit Sub
End If

Set wb1 = Workbooks.Open(fname)

这篇关于获取Excel VBA的文件位置和名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！