VBA Excel FileDialog返回selecteditem作为对象 [英] VBA Excel FileDialog return selecteditem as object

问题描述

我正在VBA中使用Excel的功能来搜索文件，并要求用户选择未找到的文件.

I am using a function in VBA for Excel to search for a file and ask the user to select of not found.

当我使用 application.filedialog filepicker 选择文件时， .selecteditem(1)作为字符串返回，但我理想情况下需要它作为对象返回.

When I use the application.filedialog filepicker to select the file the .selecteditem(1) is returning as a string but I ideally need it returning as an object.

有没有一种方法可以转换它，或者从一开始我就走错了路?

Is there a way to convert this or am I going the wrong way about it from the start?

Public myDir As String
Public newFilePath As String
Public FileSys As Object
Public myFolder

    Function LoadFileName(FileStart As String, FileType As String)
    newFilePath = ActiveWorkbook.Path
    myDir = newFilePath & "\Daily reports"
    ChDrive (Left(ActiveWorkbook.Path, 2))
    ChDir myDir
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    Set myFolder = FileSys.GetFolder(myDir)        


    On Error GoTo FileNotFound

    Dim dteFile As Date
    Dim oFS As Object
    Dim objFile As Object
    Dim FileName As Object
    Dim strFileToOpen As Office.FileDialog

    dteFile = DateSerial(1900, 1, 1)

    Set FileSys = CreateObject("Scripting.FileSysetmObject")

    For Each objFile In myFolder.Files
        If FileSys.GetFile(objFile).DateCreated >= dteFile And UCase(Left(objFile.Name, 3)) = FileStart Then
        dteFile = FileSys.GetFile(objFile).DateCreated
        Set FileName = objFile
        End If
    Next

FileFound:
    fileConfirm = MsgBox("Is " & FileName.Name & " the report you wish to use?", vbYesNoCancel + vbQuestion)
    If fileConfirm = vbCancel Then
    End
    ElseIf fileConfirm = vbNo Then
    GoTo SelectFile
    ElseIf fileConfirm = vbYes Then
    Set LoadFileName = FileName
    Exit Function
    End If

FileNotFound:
    MsgBox "Unable to find most recent report." & vbCrLf & _
            "Please select the file you wish to use.", vbInformation + vbOKOnly
SelectFile:
    Set strFileToOpen = Application.FileDialog(msoFileDialogFilePicker)
    With strFileToOpen
    .AllowMultiSelect = False
    .Title = "Pleas select report to use."
    .Filters.Clear
    .Filters.Add "Excel", "*." & FileType & "*"
    End With

    If strFileToOpen.Show = -1 Then
    FileName = strFileToOpen.SelectedItems(1)
    GoTo FileFound
    Else
    forceBreak = MsgBox("No file has been selected. Would you like to try again?", vbExclamation + vbYesNo)
        If forceBreak = vbNo Then
        MsgBox "The CQUIN daily patient list cannot be generated without both EPIC and CHEQS reports." & _
                "Please ensure these have been run and saved in the correct locations.", vbCritical
        End
        Else
        GoTo SelectFile
        End If

    End If


    End Function

推荐答案

我最终设法自己找到了解决方案.对于那些想要的人，这就是我所缺少的:

I've managed to find the solution on my own in the end. For those who want it, this is what I was missing:

我只需要使用 .GetFile ...

I just needed to use .GetFile...

    If strFileToOpen.Show = -1 Then
    Set FileName = FileSys.GetFile(strFileToOpen.SelectedItems(1))
    GoTo FileFound
    Else

这篇关于VBA Excel FileDialog返回selecteditem作为对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！