User did not follow file naming convention... thus macro crashes

[brncao]

I told the user to name the files as This_Report_07-01-2015. Actually this is how they've been naming it so it wasn't my idea. Now the person is inserting Spaces instead of Underscores. It crashed my macro because it was expecting an underscore. Is there a way to deal with this? No, I'm not going to rename all the files.

 

[Mike LH]

Hi,

You can use the REPLACE function in your code to change the spaces into underscores like this

Dim Fnam As String
Fnam = Replace("This Report 07-1-2015", " ", "_")

so after replace evaluates the filename will look like this "This_Report_07-1-2015"

 

[brncao]

But how does that work? Wouldn't you get a File Not Found error in the first place? Before you can replace something, it needs to read what the "incorrect" file name is before any corrective actions can be taken. But that's a catch 22, because if the file name is incorrect, then you'll get a file not found error, and no corrective actions will be taken. Right now I have f = "\This_Report_"

 

[Smitty]

How 'bout telling use what your code is supposed to do? There's almost always a workaround for idiots who can't follow arcane rules that can almost never be reliably (EDIT: or realistically) enforced.

 

[Rick Rothstein]

But how does that work? Wouldn't you get a File Not Found error in the first place? Before you can replace something, it needs to read what the "incorrect" file name is before any corrective actions can be taken. But that's a catch 22, because if the file name is incorrect, then you'll get a file not found error, and no corrective actions will be taken. Right now I have f = "\This_Report_"

The problem is that you did not post any of your code, so we do not know how it is attempting to you use the filename nor do we know how the filename is even getting into your running program... I can think of several ways your program could be coded and still "crash you macro"... unless you show us your code, all we can do is guess.

 

[SpillerBD]

This is one of the reason I don't like using set file names or folders. I use the

Application.FileDialog(msoFileDialogFilePicker)

to select the desired file(s) to run my macros on.

 

[brncao]

Objective: Name the ranges in each Excel file. These excel files have different dates on them. A loop is created to loop through the files, name the ranges, and save & close them.

A simple Sub will pass the following arguments (starting and ending dates) to Auto_Define_Name

Option Explicit
Public Sub Auto_Define_Name(myStartingDate As Date, myEndingDate As Date)
Dim p As String, f As String
Dim d As Integer, totalDays As Integer
Dim startingYear As Integer, startingMonth As Integer, startingDay As Integer
Dim yyyymm As String, mmddyy As String
Dim rowNum As Integer
    p = "C:\XXXXX\XXXX\XXXXXXXXXXXXX\XXX " 'Path
    f = "\This_Report_" 'File name (partial)
    totalDays = DateDiff("d", myStartingDate, myEndingDate)
    startingYear = Year(myStartingDate)
    startingMonth = Month(myStartingDate)
    startingDay = Day(myStartingDate)
    
    For d = 0 To totalDays
        yyyymm = Format(DateSerial(startingYear, startingMonth, startingDay + d), "yyyy-mm")
        mmddyy = Format(DateSerial(startingYear, startingMonth, startingDay + d), "mm-dd-yy")
        
        Workbooks.Open Filename:=p & yyyymm & f & mmddyy & ".xlsx" 'This will return an error if the user puts in spaces because of f
        'The rest of the code... Going through a loop that increments the date by a day, etc.
     Next d
End Sub

 

[Smitty]

Is it possible to just loop through all of the files in the folder, or do you have files that shouldn't be touched?

 

[brncao]

Is it possible to just loop through all of the files in the folder, or do you have files that shouldn't be touched?

The latter unfortunately.

Jesus, now there's leading spaces in the file names

 

[netuser]

Objective: Name the ranges in each Excel file. These excel files have different dates on them. A loop is created to loop through the files, name the ranges, and save & close them.

A simple Sub will pass the following arguments (starting and ending dates) to Auto_Define_Name

Option Explicit
Public Sub Auto_Define_Name(myStartingDate As Date, myEndingDate As Date)
Dim p As String, f As String
Dim d As Integer, totalDays As Integer
Dim startingYear As Integer, startingMonth As Integer, startingDay As Integer
Dim yyyymm As String, mmddyy As String
Dim rowNum As Integer
    p = "C:\XXXXX\XXXX\XXXXXXXXXXXXX\XXX " 'Path
    f = "\This_Report_" 'File name (partial)
    totalDays = DateDiff("d", myStartingDate, myEndingDate)
    startingYear = Year(myStartingDate)
    startingMonth = Month(myStartingDate)
    startingDay = Day(myStartingDate)
    
    For d = 0 To totalDays
        yyyymm = Format(DateSerial(startingYear, startingMonth, startingDay + d), "yyyy-mm")
        mmddyy = Format(DateSerial(startingYear, startingMonth, startingDay + d), "mm-dd-yy")
        
        Workbooks.Open Filename:=p & yyyymm & f & mmddyy & ".xlsx" 'This will return an error if the user puts in spaces because of f
        'The rest of the code... Going through a loop that increments the date by a day, etc.
     Next d
End Sub

Is there any part of your file name that will always be same ? If yes then it is very easy to do just use the following to get the file name and put in variable

Dim file As String
Dim File_Name As String

file = Dir$(path & "*" & PartialName & "*" & ".xlsx")  'replace Path and partial name with yours


'For multiple files you have to loop :

    File_Name = Dir(file, vbNormal)
     
    While File_Name <> ""
         'your code
    Wend