0 votes
in VBA by Beginner (58 points)
edited by

hi, 

actually i  got  the  code  and   add some  lines   to  what  i  need    as  you   see the  code   i  add  some  message   when  i  search   the  files   so   in the   begining   if    the  inputbox   is empty   the   it  shows   message  tell me   you  don't  write   the  file name   and  if  is not    found   in  the  specefic  folder   it  shows   message    the  file  is not  existed   and   if   the   file   is

existed   so  show  message   two  choice    i  open  the   file   or  not   but  before  it   show message  contain  two  choice    i  would  give  me  message  show   data   about  file   like   the  name   and   the  type   and  size

i  hope   somebody help

 

Sub LoopThroughFiles1()
 
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer
Set oFSO = CreateObject("Scripting.FileSystemObject")
 
Set oFolder = oFSO.GetFolder("C:\Users\ALHAGAG\Desktop")
file = InputBox("in which file do you wish open  data ? specify file name ", "file name")
If file = "" Then
MsgBox "you didn't write a file name !"
Exit Sub
End If
If file <> oFolder Then
MsgBox "the  file  is not  existed!"
Exit Sub
End If

If file = oFolder Then
answer = MsgBox("are you sure want to add the file name?", vbYesNo + vbQuestion, "add file  name")
End If
If answer = vbYes Then

("11\oFSO.Open ("C:\Users\ALHAGAG\Desktop     
End If
 

 
End Sub

   

1 Answer

+1 vote
by Beginner (56 points)
selected by
 
Best answer

 you can try like

Sub LoopThroughFiles1()
 
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer
Set oFSO = CreateObject("Scripting.FileSystemObject")
 
Set oFolder = oFSO.GetFolder("C:\temp\sample")
file = InputBox("in which file do you wish open  data ? specify file name ", "file name")
If file = "" Then
MsgBox "you didn't write a file name !"
Exit Sub
End If
Set f = oFSO.getfile(oFolder & "\" & file)
If LCase(file) <> LCase(f.Name) Then
MsgBox "the  file  is not  existed!"
Exit Sub
End If


answer = MsgBox("are you sure want to add the file name?" & vbNewLine & f.Size & " bytes" & vbNewLine & f.Type, vbYesNo + vbQuestion, "add file  name")

If answer = vbYes Then
Workbooks.Open f.Path
End If
 

 
End Sub

 

by Beginner (58 points)
thanks westconn but   unfortinatly   it  gives  me  run time  error 53    file  not  found   and  highlight  in  this  line

Set f = oFSO.getfile(oFolder & "\" & file)

indeed  i'm sure  about  my directory  of   file  i  changed  more  than  directory of  file   but  it  gives  me the  same  error
by Beginner (56 points)

i tested the code with a valid file before posting

to fix the error message

On Error Resume Next
Set f = oFSO.getfile(oFolder & "\" & file)
If LCase(file) <> LCase(f.Name) Then
    MsgBox "the  file  is not  existed!"
    Exit Sub
End If

but the error indicates the file name you input does not exist, some typo or incorrect path

file extention must be input as part of the filename, else it would require a loop to test every file in the folder to see if the name and any extention match the input

if you want to only input the name of the file with no extention, try like below

Sub LoopThroughFiles1()
 
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer
Set oFSO = CreateObject("Scripting.FileSystemObject")
 
Set oFolder = oFSO.GetFolder("C:\temp\sample")
file = InputBox("in which file do you wish open  data ? specify file name ", "file name")
If file = "" Then
    MsgBox "you didn't write a file name !"
    Exit Sub
End If
For Each x In oFolder.Files
    If LCase(x.Name) Like LCase(file & ".*") Then Set f = x: Exit For
Next
If IsEmpty(f) Then
    MsgBox "the  file  is not  existed!"
    Exit Sub
End If


answer = MsgBox("are you sure want to add the file name?" & vbNewLine & f.Size & " bytes" & vbNewLine & f.Type, vbYesNo + vbQuestion, "add file  name")

If answer = vbYes Then
    Workbooks.Open f.Path
End If

End Sub

you can limit the files returned to only be excel files, by changing to

    If LCase(x.Name) Like LCase(file & ".xl*") Then Set f = x: Exit For

 

by Beginner (58 points)
thanks  you're  right  about  exention in the  main code    and  the  a new code  also  works  so well  but  it  misses  something  first of  all i would  when open  the  files  not  only exel   i would all of type  files  image ,videos ..etc  and    i want  when  i  choose   yes   before  open  the  file  shows  me  detailes  in messagebox  size,type  name  directory  

thanks  advance
by Beginner (56 points)

use shellexecute API to open any type of file with the default program for that file type

 

the file size and type are already shown in the message box, other properties could also be added there

by Beginner (58 points)

actually  i  have  no  idea   how   use  shellexecute  and  where is  exactly  adjusting  line  code   as for  the file size and type are not  shown  all of  properties in the message box  i'm sure  about  this   it  just  gives me    the size

by Beginner (56 points)

as an alternative to shell execute, you can use a wscript object

If answer = vbYes Then
'    Workbooks.Open f.Path
 CreateObject("WScript.Shell").Run f.path
End If

 

answer = MsgBox("are you sure want to add the file name?" & vbNewLine & f.Path & vbNewLine & f.Size & " bytes" & vbNewLine & f.Type, vbYesNo + vbQuestion, "add file  name")

to add more detail to message box, change above line, now shows full path to file found

by Beginner (58 points)
well done  westconn   thanks   for  your assistance   and  your  time    everything work  perfectly

  take care

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...