Hi,
I'm so confused to show message based on this line
MsgBox "File not found for selection= " & FileRootafter open file !
the file is lready existed and will open it , so the correct procedure should not show message!
what's wrong guys?!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
problem show message "File not found" after open file
0
Answers
0
Speed, sorry but I'm away from my PC for several hours now (and can't check macros on my phone).
In my previous Answer to you, I asked you to ensure that the line:
Dim i As Longis at the top of the module behind your UserForm (i.e. it is the very first line, before any codes). That way its value is kept between the main procedure and the function (and iterative calls of that function).
I suspect that is the cause of your problem - please check that this is the case.
REVISION #1, 04 June 2025 -
The problem arises since the Function code on the previous Answer open files in all of folders branches in directory (now corrected) omitted the lines in bold below in the code extract:
' check again for a pdf of that name
Fname = Dir(fldFolder & "\" & fRoot & ".pdf")
If Fname <> "" Then
' try to open pdf
ActiveWorkbook.FollowHyperlink fldFolder & "\" & Fname
' increment the "found" counter
i = i + 1
End IfHope this corrects the problem- if so, please remember to mark this Answer as Selected.
Discussion
sorry about this mistake.
I remeber that was existed when run in previous post and this will show the same problem even if it's existed at the top of the module.
I remeber that was existed when run in previous post and this will show the same problem even if it's existed at the top of the module.
speed (rep: 46) Jun 3, '25 at 9:44 am
Okay Speed, I think I know what you mean. Will try to look at your file in a few hours.
John_Ru (rep: 6722) Jun 3, '25 at 10:10 am
Speed
I took your file and:
1. modified the UserForm code so that FolderPath pointed to a folder on my PC and
2. changed cell C4 to include the 'root' of a known .xlsm file within a subfolder of FolderPath.
From the UF ListBox, I picked the line associated with C4- I got your message box (for the selection) then another stating "Launched 1 files" (and the .xlsm file was opened). I didn't get the mesage you got.
Are you sure your selected file (under Invoice in the ListBox) really exists as .xls* or .pdf in your FolderPath?
Remember that if you pick ACCCB4 then the code looks for ACCCB4.xls* and ACCCB4.pdf but will NOT find ACCCB4oh.xlsx say (but if you changed C4 text to read ACCCB4* then that * wildcard would get the code to find it)
I took your file and:
1. modified the UserForm code so that FolderPath pointed to a folder on my PC and
2. changed cell C4 to include the 'root' of a known .xlsm file within a subfolder of FolderPath.
From the UF ListBox, I picked the line associated with C4- I got your message box (for the selection) then another stating "Launched 1 files" (and the .xlsm file was opened). I didn't get the mesage you got.
Are you sure your selected file (under Invoice in the ListBox) really exists as .xls* or .pdf in your FolderPath?
Remember that if you pick ACCCB4 then the code looks for ACCCB4.xls* and ACCCB4.pdf but will NOT find ACCCB4oh.xlsx say (but if you changed C4 text to read ACCCB4* then that * wildcard would get the code to find it)
John_Ru (rep: 6722) Jun 3, '25 at 3:13 pm
Are you sure your selected file (under Invoice in the ListBox) really exists as .xls* or .pdf in your FolderPath?
yes , if it's not existed then I suppose the message show without open the file.
From the UF ListBox, I picked the line associated with C4- I got your message box (for the selection) then another stating "Launched 1 files" (and the .xlsm file was opened). I didn't get the mesage you got.
disappointed for me,sorry I said that .
speed (rep: 46) Jun 3, '25 at 4:58 pm
Sorry but your comments aren't clear to me.
Your file works well for me (when I adjust it as stated previously).
The messages are wholly dependent on the value of i (not the files actually opened- which it should count) - if a match is found but somehow i reverts to 0 then you'll get the "File not found..." message.
Please try this- use File Explorer to view your chosen folder, pick an Excel file then right click / Rename. Use Ctrl+C to copy the filename (without extension) but cancel. Paste that into a cell in column C of your worksheet and select that in the ListBox Hopefully it finds (and opens) the file.
Your file works well for me (when I adjust it as stated previously).
The messages are wholly dependent on the value of i (not the files actually opened- which it should count) - if a match is found but somehow i reverts to 0 then you'll get the "File not found..." message.
Please try this- use File Explorer to view your chosen folder, pick an Excel file then right click / Rename. Use Ctrl+C to copy the filename (without extension) but cancel. Paste that into a cell in column C of your worksheet and select that in the ListBox Hopefully it finds (and opens) the file.
John_Ru (rep: 6722) Jun 3, '25 at 6:04 pm
Use Ctrl+C to copy the filename (without extension) but cancel. Paste that into a cell in column C of your worksheet ...
I followed your steps , but unfortunately doesn't solve the problem!
speed (rep: 46) Jun 4, '25 at 4:06 am
Speed. I can't explain why ut doesn't work for you.
I'm busy again today but suggest you comment out the MsgBox lines in the main code but add them to the If blocks within the Function (if you really need to know when the files are opened anyway) .
I'm busy again today but suggest you comment out the MsgBox lines in the main code but add them to the If blocks within the Function (if you really need to know when the files are opened anyway) .
John_Ru (rep: 6722) Jun 4, '25 at 4:23 am
I notice that happens with just pdf files ,as to xls files works correctly without show message after open file.
speed (rep: 46) Jun 4, '25 at 4:24 am
Well spotted! My code in the Functon missed the line in bold (to increment the counter):
I'll revise my earlier Answer (and file) later today
If Fname <> "" Then
i =i + 1
' try to open pdf
ActiveWorkbook.FollowHyperlink fldFolder & "\" & Fname
End If
I'll revise my earlier Answer (and file) later today
John_Ru (rep: 6722) Jun 4, '25 at 6:40 am
See the Revision#1 to my Answer above.
Also I corrected the Answer to your previous Question - see REVISION #3, 04 June 2025 to that
Also I corrected the Answer to your previous Question - see REVISION #3, 04 June 2025 to that
John_Ru (rep: 6722) Jun 4, '25 at 4:21 pm
Did the revision work for you, Speed?
John_Ru (rep: 6722) Jun 5, '25 at 1:20 pm
unfortunately doesn't work
I'm not sure what exactly happen !
somtimes show the message for excel,pdf files , not only pdf files!
I'm not sure what exactly happen !
somtimes show the message for excel,pdf files , not only pdf files!
speed (rep: 46) Jun 5, '25 at 3:33 pm
Sorry Speed, I can't explain. It works well for me. Again I suggest you comment out the MsgBox which is wrong for you (I was following what your original code seemed to want to do).
John_Ru (rep: 6722) Jun 5, '25 at 5:57 pm