VBA code to open file using wildcards

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,500
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
 
Sort by date Sort by votes
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 :(
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
VBasic2008 said:
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.
Click to expand...
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
 
Upvote 0
Solution
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"
 
Upvote 0
hrayani said:
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"
Click to expand...
Try strFileName = "C:\OneDrive\Internal Sheets - Excel Files\Lomotex\" & Range("B3").Value & "*" & ".xlsm".
hrayani said:
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"
Click to expand...
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
VBasic2008 said:
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
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Open 2 worksheets; closing first spreadsheet
Replies
8
Views
110
keycalinc
K
S
  • Solved
Can't open Excel XLSM file due to recent VBA Reference failing
2
Replies
16
Views
453
SixSigmaGuy
S
M
  • Question
Share excel file on Sharepoint using VBA
Replies
1
Views
107
mariyarashkova
M
H
  • Question
VBA code Required For Deleting A Folder then Copying Files From Another Folder
Replies
1
Views
121
hrayani
H
H
  • Question
Add a criteria to VBA Code
Replies
6
Views
74
hrayani
H

Forum statistics

Threads
1,214,819
Messages
6,121,741
Members
449,050
Latest member
excelknuckles

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