VBA-Excel Import Issue re: file name

Question

A brief description of what I'm trying to do:

• Import data from multiple .csv files into a single workbook with multiple sheets.

Description of the problem I'm encountering:

• The suffixes of the original .csv data files change daily (the database automatically adds the date the report was run as a suffix as integers [e.g. today's date would be a suffix of 03182019]).

• I've created the below macro to locate the requisite file and then offer an InputBox in order to manually enter the suffix (report date as integers). This code works perfectly and largely meets my needs, but I would like to eliminate the need to manually enter the suffix since the macro will be importing 15 separate workbooks. As a result, I would have to enter the suffix 15 times - once for each of the original .csv files.

  Dim otherWB As Workbook, ThisWS As Worksheet
  Dim sFileName As String
  Dim sBase As String
  Dim sExt As String
  Dim sSuffix As String
  
  Sheets("AJP EGC").Select
  Set ThisWS = ActiveSheet
  
  sBase = "/Users/plum/Downloads/AJP EGC "
  sExt = ".csv"
  sSuffix = InputBox("Enter suffix for filename")
  
  sFileName = sBase & sSuffix & sExt
  Workbooks.Open FileName:=sFileName
  
  Set otherWB = ActiveWorkbook
  
  Application.ScreenUpdating = False
  
  ActiveSheet.Range("A1").CurrentRegion.Copy 
  Destination:=ThisWS.Range("A1")
  
  otherWB.Close False
  ThisWS.Activate
  
  Set ThisWS = Nothing
  Set otherWB = Nothing
  
  Application.ScreenUpdating = True
  

As noted above, this macro works fine but I have to enter the suffix manually. I've tried variations of this code in order to eliminate the need to manually enter the suffix since the original .csv files are always downloaded to the same location on my computer. Here's the new code I tried:

    Dim otherWB As Workbook, ThisWS As Worksheet
    Dim sFileName As String
    Dim sBase As String
    Dim sExt As String

    Sheets("AJP EGC").Select
    Set ThisWS = ActiveSheet

    sBase = "/Users/plum/Downloads/AJP EGC "
    sExt = ".csv"

    sFileName = Dir(sBase & "*" & sExt)
    Workbooks.Open FileName:=sFileName

    Set otherWB = ActiveWorkbook

    Application.ScreenUpdating = False

    ActiveSheet.Range("A1").CurrentRegion.Copy 
    Destination:=ThisWS.Range("A1")

    otherWB.Close False
    ThisWS.Activate

    Set ThisWS = Nothing
    Set otherWB = Nothing

    Application.ScreenUpdating = True

When I run this code, I get the following Run-time error:

This image of the Run-time error shows that the correct suffix has been selected and I've verified that this is the exact name of the correct file to be imported; however, the error indicates it is not able to locate the file on my computer. When I select 'Debug' in the error box, it takes me to the following line:

Workbooks.Open FileName:=sFileName 

When I hover over FileName:=sFileName after clicking 'Debug', the correct file name again shows, though it does not include the directory location...just the file name. The files have not changed location, and as I said, when I run the first code I provided that prompts the user to manually enter the suffix, it finds the files without issue.

I have also tried to change the sSuffix = InputBox in the original code to sSuffix = "*" which returns the same run-time error related to the FileName:=sFileName snippet. Again, the correct file name is shown in the run-time error message.

I'm not sure what I'm missing here and feel like it's got to be something painfully obvious. Would appreciate any insight you could offer. I'm a relative noob to VBA and have exhausted Google and this site in an effort to figure out the issue.

Answer 1

The Dir() function returns the name of a file and also its extension type.

To fix the problem, try:

sFileName = Dir(sBase & "*" & sExt)
sFileName = sBase & sFileName