VBA code to open file using wildcards

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,494
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends.


I need a VBA code to open file keeping in view following points

1) Open a file > file name stored in cell B3 and then close it very next second without saving changes
2) Using wildcard.. Like value in cell B3 is 534 but the file name may be 534 or 534 - Barcodes or 534 - Barcodes - Carton Sizes
3) Destination will always be "C:\OneDrive\Internal Sheets - Excel Files\Lomotex\"
4) Msg box to show file does not exists if there is no file available
5) Current file to stay on top. Like the user should not know another file has been open & closed to pull values

Reason > I am using indirect function and the problem is that it does not work on closed workbook

Regards,

Humayun
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Do you have a worksheet name that goes with cell `B3` and maybe the same or another that should be recalculated to pull the values? Could the file name be File_534 or is the value always at the beginning.
 
Upvote 0
VBasic2008 said:
Do you have a worksheet name that goes with cell `B3` and maybe the same or another that should be recalculated to pull the values? Could the file name be File_534 or is the value always at the beginning.
Click to expand...
Hi, Thanks for the reply..

534 is all what cell b3 has... and it will keep on changing like 547 or 298 etc. And the files stored at the destination will always start from what cell b3 has in there..

cell b3 value will always be at the begining of file names....
 
Upvote 0
Update External References (Silently)

VBA Code: 
Option Explicit

Sub pullData()
    
    ' Define constants.
    Const FolderPath As String = _
      "C:\OneDrive\Internal Sheets - Excel Files\Lomotex"
    Const TrailingPattern As String = "*.xls*"
    Const FileCell As String = "B3"
    
    ' Define worksheet.
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    ' Define File Pattern.
    Dim FilePattern As String
    FilePattern = ws.Range(FileCell).Value & TrailingPattern
    
    ' Iterate (loop through) files in folder.
    Dim fsoFile
    With CreateObject("Scripting.FileSystemObject")
        On Error GoTo clearError
        For Each fsoFile In .getFolder(FolderPath).Files
            If fsoFile.Name Like FilePattern Then
                Application.ScreenUpdating = False
                With Workbooks.Open(fsoFile.Path)
                    .Close SaveChanges:=False
                End With
                Application.ScreenUpdating = True
                Exit For
            End If
        Next fsoFile
    End With
    
    ' ha, ha, ha: nothing happened.
    Exit Sub

clearError:
    ' ha, ha, ha: something happened.
    On Error GoTo 0
    Application.ScreenUpdating = True
    MsgBox "File not found.", vbCritical, "Fail"

End Sub
 
Last edited:
Upvote 0
VBasic2008 said:
Update External References (Silently)

VBA Code: 
Option Explicit

Sub pullData()
    Const FolderPath As String = _
      "C:\OneDrive\Internal Sheets - Excel Files\Lomotex"
    Const TrailingPattern As String = "*.xls*"
    Const FileCell As String = "B3"
 
    ' Define worksheet.
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
 
    ' Define File Pattern.
    Dim FilePattern As String
    FilePattern = ws.Range(FileCell).Value & TrailingPattern
 
    ' Iterate (loop through) files in folder.
    Dim fsoFile
    With CreateObject("Scripting.FileSystemObject")
        On Error GoTo clearError
        For Each fsoFile In .getFolder(FolderPath).Files
            If fsoFile.Name Like FilePattern Then
                Application.ScreenUpdating = False
                With Workbooks.Open(fsoFile.Path)
                    .Close SaveChanges:=False
                End With
                Application.ScreenUpdating = True
                Exit For
            End If
        Next fsoFile
    End With
 
    ' ha, ha, ha: nothing happened.
    Exit Sub

clearError:
    ' ha, ha, ha: something happened.
    On Error GoTo 0
    Application.ScreenUpdating = True
    MsgBox "File not found.", vbCritical, "Fail"

End Sub
Click to expand...
Hi,

I tried your code but its not working with the wildcard portion I guess

For example

1) If cell B3 value is 534 and the file to open is also 534 then it opens the file
2) If cell B3 value is 534 and the file to open is 534 - Barcodes then it does not open file

Also it does not give any msg if the file does not exists... Any idea ???
 
Upvote 0
VBasic2008 said:
Update External References (Silently)

VBA Code: 
Option Explicit

Sub pullData()
   
    ' Define constants.
    Const FolderPath As String = _
      "C:\OneDrive\Internal Sheets - Excel Files\Lomotex"
    Const TrailingPattern As String = "*.xls*"
    Const FileCell As String = "B3"
   
    ' Define worksheet.
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
   
    ' Define File Pattern.
    Dim FilePattern As String
    FilePattern = ws.Range(FileCell).Value & TrailingPattern
   
    ' Iterate (loop through) files in folder.
    Dim fsoFile
    With CreateObject("Scripting.FileSystemObject")
        On Error GoTo clearError
        For Each fsoFile In .getFolder(FolderPath).Files
            If fsoFile.Name Like FilePattern Then
                Application.ScreenUpdating = False
                With Workbooks.Open(fsoFile.Path)
                    .Close SaveChanges:=False
                End With
                Application.ScreenUpdating = True
                Exit For
            End If
        Next fsoFile
    End With
   
    ' ha, ha, ha: nothing happened.
    Exit Sub

clearError:
    ' ha, ha, ha: something happened.
    On Error GoTo 0
    Application.ScreenUpdating = True
    MsgBox "File not found.", vbCritical, "Fail"

End Sub
Click to expand...
Here is some additional error handling including the scenario when the file was not found in the folder which I forgot about. Sorry.

VBA Code: 
Option Explicit

Sub pullData()
    
    ' Initialize error handling.
    Const ProcName As String = "pullData"
    On Error GoTo clearError
    
    ' Define constants.
    Const FolderPath As String = _
      "C:\OneDrive\Internal Sheets - Excel Files\Lomotex"
    Const TrailingPattern As String = "*.xls*"
    Const FileCell As String = "B3"
    
    ' Define worksheet.
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    ' Define File Pattern.
    Dim FilePattern As String
    FilePattern = ws.Range(FileCell).Value & TrailingPattern
    
    ' Iterate (loop through) files in folder.
    Dim fsoFile
    Dim found As Boolean
    With CreateObject("Scripting.FileSystemObject")
        On Error GoTo PathNotFound
        For Each fsoFile In .getFolder(FolderPath).Files
            On Error GoTo clearError
            If fsoFile.Name Like FilePattern Then
                Application.ScreenUpdating = False
                With Workbooks.Open(fsoFile.Path)
                    .Close SaveChanges:=False
                End With
                Application.ScreenUpdating = True
                found = True
                Exit For
            End If
        Next fsoFile
    End With
    If found = False Then
        GoTo FileNotFound
    End If
   ' ha, ha, ha: nothing happened.
    GoTo ProcExit

FileNotFound:
    ' ha, ha, ha: something happened.
    MsgBox "File not found.", vbCritical, "Fail"
    GoTo ProcExit

PathNotFound:
    ' ha, ha, ha: something happened.
    On Error GoTo 0
    Application.ScreenUpdating = True
    MsgBox "File not found.", vbCritical, "Fail"
    GoTo ProcExit

clearError:
    Debug.Print "'" & ProcName & "': " & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "        " & Err.Description
    On Error GoTo 0
    GoTo ProcExit

ProcExit:

End Sub
 
Upvote 0
hrayani said:
Hi,

I tried your code but its not working with the wildcard portion I guess

For example

1) If cell B3 value is 534 and the file to open is also 534 then it opens the file
2) If cell B3 value is 534 and the file to open is 534 - Barcodes then it does not open file

Also it does not give any msg if the file does not exists... Any idea ???
Click to expand...
Do all files contain xls in their extension? It should pick the first file 534 - BarcodesANYTHING.xlsANYTHING. It is also case-sensitive i.e. A<>a.
 
Upvote 0
No dear still not working....

IF the file name matches exactly that of cell B3 then it does open otherwise it does not open and not giving any msg
VBasic2008 said:
Do all files contain xls in their extension?
Click to expand...
file extension will always be xlsm
 
Upvote 0
hrayani said:
No dear still not working....

IF the file name matches exactly that of cell B3 then it does open otherwise it does not open and not giving any msg

file extension will always be xlsm
Click to expand...
That is covered with xls*, but you can change it to xlsm. I'll look into the case-sensitivity issue which is most likely causing problems.
Your could cover this by using Option Compare Text right after Option Explicit, i.e. before the Sub.
 
Last edited:
Upvote 0
This is the code which is working a bit of what is required apart from two things

1) The wildcard parameter
2) Close the file after it is just open

VBA Code: 
Sub pull_data()

Dim strFileName As String
Dim strFileExists As String


    strFileName = "C:\OneDrive\Internal Sheets - Excel Files\Lomotex\" & Range("B3").Value & ".xlsm"
    strFileExists = Dir(strFileName)

   If strFileExists = "" Then
        MsgBox "File Does Not Exist"
    Else
       Application.ScreenUpdating = False
       Workbooks.Open strFileName
             
    End If

End Sub

I guess this line of the code needs to be amended for the wildcard to operate
strFileName = "C:\OneDrive\Internal Sheets - Excel Files\Lomotex\" & Range("B3").Value & ".xlsm"
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Can't open Excel XLSM file due to recent VBA Reference failing
2
Replies
16
Views
440
SixSigmaGuy
S
H
  • Question
VBA code Required For Deleting A Folder then Copying Files From Another Folder
Replies
1
Views
118
hrayani
H
S
  • Question
Vba code to open all excel files along with excel name and date modified and data inside the excel
Replies
0
Views
53
suyogpat
S
P
  • Solved
VBA to open file using Cell for the path
Replies
4
Views
545
Peter Davison
P
Joe4
  • Solved
Old VBA Code No Longer Working
Replies
3
Views
493
Micron
M

Forum statistics

Threads
1,214,636
Messages
6,120,669
Members
448,977
Latest member
moonlight6

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top