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
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,036
Office Version
  1. 2016
Platform
  1. Windows
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
       With Workbooks.Open(strFileName)
          .Close SaveChanges:=False
                End With
              
    End If

End Sub

Point Number 1 solved i.e. close the file immediately after opening it

Now only the wild card part remains.. unsolved :(
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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.
I've abandoned the idea of checking the file extension. So it will just check the left part of the filename and it is case-insensitive now i.e. A=a.

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 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
    
    ' 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 StrComp(Left(fsoFile.Name, Len(FilePattern)), _
                       FilePattern, vbTextCompare) = 0 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 "Path 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
 
Solution

hrayani

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

Same issue... neither opening the file nor giving any msgbox

Can you please look at this code and amend this part if possible...

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
       With Workbooks.Open(strFileName)
          .Close SaveChanges:=False
                End With              
    End If
End Sub

I am not an expert like you but I do have a feeling that this portion will do the trick... But I do not know how to make it work
I guess its a bit inserting "*" here and there

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

VBasic2008

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

Same issue... neither opening the file nor giving any msgbox

Can you please look at this code and amend this part if possible...

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
       With Workbooks.Open(strFileName)
          .Close SaveChanges:=False
                End With            
    End If
End Sub

I am not an expert like you but I do have a feeling that this portion will do the trick... But I do not know how to make it work
I guess its a bit inserting "*" here and there

VBA Code:
    strFileName = "C:\OneDrive\Internal Sheets - Excel Files\Lomotex\" & Range("B3").Value & ".xlsm"
Try strFileName = "C:\OneDrive\Internal Sheets - Excel Files\Lomotex\" & Range("B3").Value & "*" & ".xlsm".
No Dear....

Same issue... neither opening the file nor giving any msgbox

Can you please look at this code and amend this part if possible...

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
       With Workbooks.Open(strFileName)
          .Close SaveChanges:=False
                End With             
    End If
End Sub

I am not an expert like you but I do have a feeling that this portion will do the trick... But I do not know how to make it work
I guess its a bit inserting "*" here and there

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

hrayani

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

ADVERTISEMENT

its giving this error.. It is not reading it as a wildcard... instead a full file name I guess... But if thats the case then it should show the msgbox file not exist.
Oh... I am scratching my head over this


1604146645097.png
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
Sub pull_data()
   
    Const FolderPath As String = _
      "C:\OneDrive\Internal Sheets - Excel Files\Lomotex\"

    Dim PathPattern As String
    PathPattern = FolderPath & Range("B3").Value & "*" & ".xlsm"
   
    Dim FileName As String
    FileName = Dir(PathPattern)

    If FileName = "" Then
        MsgBox "File Does Not Exist"
    Else
        Application.ScreenUpdating = False
        With Workbooks.Open(FolderPath & FileName)
            .Close SaveChanges:=False
        End With
        Application.ScreenUpdating = True
    End If

End Sub
 
Last edited:

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,036
Office Version
  1. 2016
Platform
  1. Windows
I've abandoned the idea of checking the file extension. So it will just check the left part of the filename and it is case-insensitive now i.e. A=a.

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 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
   
    ' 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 StrComp(Left(fsoFile.Name, Len(FilePattern)), _
                       FilePattern, vbTextCompare) = 0 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 "Path 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

My Bad....

Sorry its opening the file and the reason for not pulling the data is the formula in my internal sheet which is causing the error

Your code is working absolutely perfect

Sorry for the trouble
 

Watch MrExcel Video

Forum statistics

Threads
1,129,793
Messages
5,638,359
Members
417,023
Latest member
Zimbo38

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