VBA Code Error 5

[Glasgowsmile]

When running this macro to upload a file and pull in data everything works great during the debug until I F8 after the tmpVal code

Not sure exactly what the problem might be but it looks like something in the esName is causing a problem?

Currently this report is only pulling data for xlsx files but I need it to be able to pull xlsx or xls.

'esName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx; *.xls", Title:="Please select a file")
esName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx; *.xls), OpenFiles", Title:="Please select a file")

tmpStart = InStr(esName, ".xlsx")    tmpVal = Mid(esName, 1, tmpStart - 1)
    New_esName = tmpVal + "_data.xlsx"

 

[jjasmith4]

If you pick an XLS file, InStr(esName, ".xlsx") is 0, so tmpStart - 1 is -1, and the Mid function doesn't like that.

 

[Glasgowsmile]

I see, what do I need to change to correct it?

 

[Glasgowsmile]

I changed the InStr(esName, ".xls") instead of xlsx and it appears I can now pull both types of files without any issues.