Using VBA to open files in a folder-CodePudding

I would like to open the file automatically after clicking the button, the folder is the same and the file name is the same every month. I wanted it to always select the most recent file from teog msc, e.g. if not, it would say that the file is too old. However, I get an error. here is the code I wanted to make However, he gets the error that "We cannot find the file. Has it been moved 1004." here

Set data_wb = Workbooks.Open (MyFile, UpdateLinks: = 0)

Even though the file is in this folder

ThisMonth = Format(Date, "mmmm")
MyFolder = "C:\Users\G2121290\Documents\PriceQ" & ThisMonth & "\"
MyFile = Dir(MyFolder & "\FinalPrice*.xlsx")
Do Until MyFile = ""
    Set data_wb = Workbooks.Open(file_name, UpdateLinks:=0)
    MyFile = Dir
Loop

And here is the code I have.

file_name = selectFilePK
If file_name = "" Then Exit Sub

'Set data file
Set data_wb = Workbooks.Open(file_name, UpdateLinks:=0)
Private Function selectFilePK()
    Dim fd As Office.FileDialog

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .InitialFileName = ActiveWorkbook.Path
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel", "*.xlsm"

        If .Show = True Then selectFilePK = .SelectedItems(1)
        Application.ScreenUpdating = False
        Application.AskToUpdateLinks = False

CodePudding user response：

in your initial code:

ThisMonth = Format(Date, "mmmm")
MyFolder = "C:\Users\G2121290\Documents\PriceQ" & ThisMonth & "\"
MyFile = Dir(MyFolder & "\FinalPrice*.xlsx")
Do Until MyFile = ""
    Set data_wb = Workbooks.Open(file_name, UpdateLinks:=0)
    MyFile = Dir
Loop

in the Open statement you are using file_name instead of MyFile which you previously defined

CodePudding user response：

Add a line that helps your debugging, like this:

file_name = selectFilePK
Debug.Print file_name
If file_name = "" Then Exit Sub

Press Ctrl G to show the debug output before running. The value there is the file that is expected to be opened. If that is wrong in some way, adjust selectFilePK accordingly.