在Excel VBA中使用用户输入打开文件 [英] Open a file with user Input in Excel VBA

问题描述

我目前正在使用VBA编写程序，该程序会从文件夹C：Reports中打开特定的excel文件。我需要能够从该文件夹中的用户输入动态打开文件，例如；

I am currently writing a Programm using VBA, which opens particular excel files from the folder C:Reports. I Need to be able to open the file dynamically from user input in this folder such as;

1）用户将看到一个对话框，并看到第一个问题什么是文件名，他/她必须在不看到文件文件夹的情况下键入文件名。他键入的文件名必须在C：Reports中，否则应打印找不到文件

1)User gets an Dialog box and sees the first question "What is the Name of the file" where he/she has to type the Filename without seeing the folder of the file. The file Name he typed has to be in C:Reports, else it should print "File not Found"

2）输入文件名后，他又遇到了一个有关ID号，是要打开的所需文件中包含的列中的值。如果文件名与文件内的ID匹配，则应打开文件，否则应打印文件名和ID不匹配

2)After entering file Name, he gets another question about the ID number, which is a value in a column in the desired file to open includes. If the file Name matches with the ID inside of the file then the file should be opened, else it should print "file Name and ID does not match"

有人解决方案？

推荐答案

选中此子项。这应该为您工作

Check this sub. This should work for you

Sub OpenFileIfDataExist()
On Error GoTo HarunErrHandler
Dim strWorkBook As Excel.Workbook
Dim SearRange As Range
Dim FSO As Object
Dim strFileName, strFilePath As String
Dim strID As String
Dim lnCheckID As Long

    strFileName = InputBox("Enter your file name.", "File Name")
    strFilePath = "C:\Reports\" & strFileName & ".xlsx"
    Set FSO = CreateObject("scripting.filesystemobject")


    If FSO.FileExists(strFilePath) = True Then
        Set strWorkBook = GetObject(strFilePath)
        Set SearRange = strWorkBook.Sheets(1).Range("A1:A1000")

           strID = InputBox("Enter ID.", "ID Input")
           lnCheckID = Application.WorksheetFunction.Match(strID, SearRange, 0)

             If lnCheckID > 0 Then
                Workbooks.Open (strFilePath)
             End If

    Else
        MsgBox "File Name does not match"
    End If

Exit Sub
HarunErrHandler:
MsgBox "File Name and ID does not match.", vbInformation, "Nothing Found"
End Sub

这篇关于在Excel VBA中使用用户输入打开文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！