VBA code to open file using wildcards

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,036
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 is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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.
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,036
Office Version
  1. 2016
Platform
  1. Windows
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.
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....
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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:

hrayani

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

ADVERTISEMENT

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
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 ???
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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
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
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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 ???
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.
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,036
Office Version
  1. 2016
Platform
  1. Windows
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
Do all files contain xls in their extension?
file extension will always be xlsm
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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
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:

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,036
Office Version
  1. 2016
Platform
  1. Windows
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,129,480
Messages
5,636,582
Members
416,927
Latest member
BNM8V6

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
Top