File Open

[vmjan02]

I have this code as is all perfect, but the use is using the file ext some time as ".xlsm" or ".xls".
How should i modify the code so that if the file ext is .xlsm or .xls it will work.

any idea or suggestions.

'
=====================================================================================
    'Check if all the Raw files are avalaible in folder
'=====================================================================================
If Sheets("Reference").Range("a2").Value = "" And Sheets("Reference").Range("a3").Value = "" And Sheets("Reference").Range("a4").Value = "" Then
MsgBox "Dear User all the 4 Raw files are not avalaible in folder" & vbNewLine & Fpath & vbNewLine & "Download raw file & Run the scrip again"
End
End If
  
'=====================================================================================
    ' 1. Check Raw data in folder sheet Abandon calls Report
'=====================================================================================
    If Sheets("Reference").Range("c2").Value <> "Abandon calls Report" Then
    MsgBox "Abandon calls Report Raw file not avaliable" & vbNewLine & "Download raw file & Run the scrip again", vbCritical, "Raw File Required"
    End
    End If
  
    If Len(Dir(Fpath & "\" & "Abandon calls Report.xlsx")) = 0 Then '' ---- ''Here it  can be .xlsx or .xls
    MsgBox "Abandon calls Report Raw file Not avaliable in the folder" & vbNewLine & Fpath & vbNewLine & "Download raw file & Run the scrip again"
    End
    End If
    
'=====================================================================================
    ' 1. ImportDaily Abandon calls Raw data
'=====================================================================================
        
    If Sheets("Reference").Range("c2").Value = "Abandon calls Report" Then
    
    Sheets("Abandon calls Raw data").Visible = True
    
    Windows("RTMC Daily ReportV3.xlsm").Activate
    ActiveWorkbook.Sheets("Abandon calls Raw data").Activate
    Cells.Select
    Selection.ClearContents
    Application.CutCopyMode = False
        
    Set wbsource = Workbooks.Open(Fpath & "\" & "Abandon calls Report.xlsx") '' ---- ''Here it  can be .xlsx or .xls
            
    Windows("Abandon calls Report.xlsx").Activate '' ---- ''Here it  can be .xlsx or .xls
        Cells.Select
        Selection.Copy
       Windows("RTMC Daily ReportV3.xlsm").Activate
        ActiveWorkbook.Sheets("Abandon calls Raw data").Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False
    wbsource.Close
    End If

 

[dmt32]

Hi,
try including a wildcard

If Len(Dir(Fpath & "\" & "Abandon calls Report.xls*")) = 0 Then

Dave

 

[vmjan02]

Hi Dave,
it is giving the error as file not found

Set wbsource = Workbooks.Open(Fpath & "\" & "Abandon calls Report.xls*")

'' Here wildcard don't work

 

[dmt32]

Hi,
sorry, only looked at Dir function part of your code

Try replacing that section with following

Dim fname
 fname = Dir(Fpath & "\" & "Abandon calls Report.xls*")
  If Len(fname) = 0 Then
    MsgBox "Abandon calls Report Raw file Not avaliable in the folder" & vbNewLine & _
    Fpath & vbNewLine & "Download raw file & Run the scrip again"
    Exit Sub
  End If

and update the workbooks.Open part with this

Set wbsource = Workbooks.Open(Fpath & "\" & fname)

Dave

 

[vmjan02]

Dim fname

as string